This section covers BigQuery partition elimination behavior when a partitioned table is joined to a driving table with a join condition on a non-partitioning field (NPF) 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
partition elimination can occur when filtering on a non-partitioned field when BigQuery can determine based on per-file minimum and maximum statistics collected for each field that the filter is satisfied only for particular partitions
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_2` t1
join `BIGQUERY_SAMPLES.DATES` dts
on
dts.CAL_DAYOFWEEK - 1 = t1.ID
where
dts.CAL_DT = '2018-01-29'
;