BigQuery SQL Optimization
Performance and efficiency guidelines for BigQuery SQL queries. Includes rules for column pruning, predicate pushdown, join optimization, and materialization strategies.
SQL Optimization Rules
[!TIP] Always include a "Summary of Optimizations" section listing only the optimizations applied.
Always Apply (Automatic)
- Column Pruning: Remove unnecessary columns from all query stages.
- Common Subexpression Reuse: Factor out identical expressions to avoid redundant computation.
- Predicate Pushdown: Apply
WHEREfilters as early as possible. - Early Aggregation: Perform
GROUP BYbefore joins when possible. - Intermediate Materialization: Choose
VIEWvsTABLEfor intermediate nodes based on efficiency.
Intermediate Node Strategy
VIEW: Small datasets or simple transformations.TABLE: Large datasets, expensive computations, or nodes reused multiple times.
Always Rewrite (Mandatory)
WHERE <col> IN (SELECT ...): Replace WithWHERE EXISTS (SELECT 1 FROM ...)WHERE (SELECT COUNT(*) ...) > 0: Replace WithWHERE EXISTS (SELECT 1 FROM ...)
Propose with Confirmation (Conditional)
UNION→UNION ALL: Faster (skips deduplication), but permits duplicate rows.COUNT(DISTINCT)→APPROX_COUNT_DISTINCT: Faster and lower memory, but approximate.
