optimizing-sql-querieslisted

# 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. ```sql -- Good: filter on the partition