This section covers BigQuery partition elimination behavior when a partitioned table is joined to a driving table with a join condition on the partitioning field (PF) and a driving table field expression.
General observations:
driving table partition elimination is part of a more general “read-filter” optimization where values from a small amount of spool can be pushed into a filter in the read step of a large table
there must be only a single join condition between tables
previously, the join condition had to be on raw fields, not more complicated expressions; but now it seems that the join condition can involve more complicated expressions
driving table partition elimination appears to occur only if there are enough partitions; e.g., 7 is too few; 28 is sufficient
driving table partition elimination appears to occur only if there are enough rows per partition; e.g., 1 million is too few; 5 million is sufficient
driving table partition elimination does not appear to be sensitive to the number of fields selected
Partition elimination was evident in the query plan for the query below.
select
t1.ID
from `BIGQUERY_SAMPLES.FLDPART28_R140M_F100_TABLE_1` t1
join `BIGQUERY_SAMPLES.DATES` dts
on
date_add(
dts.CAL_DT
,interval 1 day
) = t1.CREATE_DT
where
dts.CAL_YEAR = 2018
and dts.CAL_WEEK = 4
and dts.CAL_DAYOFWEEK = 1
;