| name | bigquery-sql |
| metadata | {"version":"v1"} |
| description | Provides BigQuery SQL query optimization techniques, execution best practices, and performance tuning rules for high-efficiency querying. Use when optimizing BigQuery SQL queries, reducing query costs, or designing performant SQL transformations. |
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.
