bigquery-sql

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.