krishp091205/gemini-config/bigquery-sql

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)` → `APPROX_COUNT_DISTINCT`: Faster and lower memory,

but approximate.