4.1. Partition Elimination
This section describes partition elimination opportunities in BigQuery.
The advantage of partition elimination in a given query is that
BigQuery spends virtually no time handling data in partitions that can
be eliminated. Without partition elimination, BigQuery must, at a
minimum, spend time and resources reading data from every row.
For official Google documentation regarding partitioned tables, see
links below.
Below is a list of strategies which BigQuery does or could use to
support partition elimination.
-
filter partitioned table
-
filter comparison with partitioning field
-
filter comparison with raw partitioning field
-
filter comparison with partitioning field
expression
-
filter comparison with non-partitioning field
-
filter comparison with raw non-partitioning
field
-
filter comparison with non-partitioning field
expression
-
join subset of driving table to partitioned table
-
join comparison with partitioning field
-
join comparison with raw partitioning field
-
join comparison with partitioning field
expression
-
the answer is YES if BigQuery
determines there are enough partitions and the
amount of data from the driving table needed
for the join condition is small enough to push
to the partitioned table read step; otherwise,
the answer is NO
-
join comparison with non-partitioning field
-
join comparison with raw non-partitioning
field
- join
comparison with raw field -
SOMETIMES
- join
comparison with field
expression
- SOMETIMES
-
the answer is YES if
BigQuery determines there are enough
partitions, the amount of data from the
driving table needed for the join
condition is small enough to push to
the partitioned table read step, and
BigQuery can eliminate partitions based
on the partition minimum and maximum
statistics for the given field;
otherwise, the answer is NO
-
join comparison with non-partitioning field
expression
Some general observations:
-
partition elimination appears to behave the same for
ingestion-time and field partitioned tables; however, not
necessarily tested in all cases