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 WHERE filters as early as possible.
• Early Aggregation: Perform GROUP BY before joins when possible.
• Intermediate Materialization: Choose VIEW vs TABLE for 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 With `WHERE EXISTS (SELECT 1 FROM

...)`

• **WHERE (SELECT COUNT(*) ...) > 0**: Replace With `WHERE EXISTS (SELECT 1

FROM ...)`

Propose with Confirmation (Conditional)

• UNION → UNION ALL: Faster (skips deduplication), but permits

duplicate rows.

• COUNT(DISTINCT) → APPROXCOUNTDISTINCT: Faster and lower memory,

but approximate.