Optimizing SQL Queries

When to use

• A query is slow, times out, or is expensive (bytes/credits/slots scanned).
• A dashboard or model run regressed after data grew.
• You see full-table scans, large shuffles, disk spills, or exploding row counts.
• Do NOT use for query correctness bugs — this skill assumes results are correct.

Workflow

- [ ] Read the actual query/EXPLAIN plan (not guesses)
- [ ] Find the dominant cost: scan, join, aggregation, or sort/spill
- [ ] Reduce data read (predicates, partition/cluster pruning, column pruning)
- [ ] Fix join strategy (order, keys, broadcast vs shuffle, skew)
- [ ] Re-measure and confirm the plan changed

1. Get the plan. Never optimize blind:
   • Postgres: EXPLAIN (ANALYZE, BUFFERS) <query>
   • Snowflake: Query Profile UI, or SYSTEM$EXPLAIN_PLAN_JSON
   • BigQuery: execution details / --dry_run for bytes billed
   • Spark/Databricks: df.explain("formatted") or the SQL plan tab
2. Identify the dominant operator by time/rows/bytes. Optimize that first; ignore cheap nodes.
3. Reduce data scanned before anything else — it usually dominates cost.
4. Fix the join only after the scan is minimal.
5. Re-run the plan and verify the change (row estimates, join type, pruning).

Patterns

Enable partition/cluster pruning — push filters on the partition/cluster key so the engine skips files. On BigQuery/Snowflake this is the single biggest lever.

-- Good: filter on the partitioning column with a literal/range so pruning applies
SELECT user_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01'  -- prunes partitions
GROUP BY user_id;

Filter before joining, not after. Reduce each side to the needed rows/columns first so the join processes less data.

Prefer explicit column lists over SELECT * in columnar warehouses — reading fewer columns reads fewer bytes.

Choose the right join for the sizes. A small dimension joined to a large fact should broadcast (map-side) rather than shuffle. In Spark, let Adaptive Query Execution pick, or hint /*+ BROADCAST(dim) */.

Aggregate/pre-filter in a CTE to shrink data before an expensive window or join, rather than computing over the full table.

Common pitfalls

• Wrapping the partition key in a function (WHERE DATE(ts) = ...) disables pruning — filter on a raw range instead.
• Implicit type casts on join keys (string vs int) force full scans and block index/pruning use — align types.
• SELECT DISTINCT to hide a fan-out join — fix the join grain instead; it is cheaper and correct.
• OR across columns often prevents index/pruning use — rewrite as UNION ALL or IN.
• Optimizing a cheap node — always target the dominant operator in the plan.
• Chasing indexes on columnar warehouses — Snowflake/BigQuery have no row-store indexes; use clustering/partitioning and result caching instead.

References

• Engine-specific tuning notes