optimize-sql-query

name	optimize-sql-query
description	Diagnoses slow SQL via EXPLAIN plans and recommends fixes — indexes, query rewrites, partition pruning, and join reordering — with measured before/after.
when_to_use	When a SQL query is slow or expensive and the user wants it faster — analyze the execution plan, find the bottleneck (seq scan, bad join order, missing index, spilled sort), and propose index or rewrite fixes with verified improvement.

When to Use

A specific query is slow or costly and the user wants it faster. You have (or can get) the SQL text and a way to run EXPLAIN against a representative dataset.

Do NOT use this skill when:

• Authoring a new analytical query from scratch → use the SQL-authoring skill instead (this is perf, not authoring).
• The "slowness" is connection/pool/lock contention, not the plan → that is an ops problem, not a query problem.
• Row counts are tiny (< ~10k) on every table — a seq scan is fine; chasing a plan here is wasted effort.

Steps

1. Get the real plan, not the estimate. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) (Postgres) or the engine's analyze form. EXPLAIN alone gives the optimizer's guess; ANALYZE runs it and gives actual rows/time. Always compare estimated rows vs actual rows per node — a 100x gap means stale statistics (run ANALYZE <table>) and every downstream join choice is suspect.

2. Find the cost driver, top of the plan downward. Look for, in priority order:

   • Seq Scan on a large table under a selective filter → missing index on the filter/join column.
   • Nested Loop where inner side runs N times with high N → join-order or missing-index blowup; the planner expected few outer rows but got many (see step 1's row gap).
   • Sort / Hash that spills to disk → look for external merge Disk: NkB or Batches: >1. The sort/hash exceeded work_mem. Fix by indexing to avoid the sort, reducing rows before the sort, or (last resort) raising work_mem for that session.
   • Partition scan with no pruning → all partitions scanned. The predicate isn't on the partition key, or wraps it in a function/cast that defeats pruning.

3. Index recommendations — be specific about column order.

   • Composite index column order = equality columns first, then the range/sort column, then included columns. WHERE a = ? AND b > ? wants (a, b), never (b, a).
   • A query filtering a and c cannot use (a, b, c) efficiently for c — b is a gap. Either reorder or make a separate index.
   • Covering index (INCLUDE in Postgres, or trailing columns) turns an Index Scan + heap fetch into an Index-Only Scan — propose it when the query selects only a few columns beyond the predicate.
   • Partial index (WHERE status = 'active') when the query always filters on a low-cardinality flag — smaller, hotter, faster.

4. Rewrite when an index won't help.

   • Make predicates sargable: WHERE date_col >= '2024-01-01' not WHERE date(date_col) = ...; col LIKE 'foo%' (indexable) not LIKE '%foo' (not). Functions/casts on the indexed column kill index use — move the transform to the literal side.
   • Predicate pushdown: filter inside the subquery/CTE, not after the join.
   • In Postgres, a CTE may materialize and block predicate pushdown — add MATERIALIZED/NOT MATERIALIZED deliberately, or inline it.
   • Replace a correlated self-join with a window function (ROW_NUMBER, LAG, running SUM) — usually one pass instead of N.
   • Drop a redundant DISTINCT when a join key already guarantees uniqueness, or when EXISTS expresses the intent without deduplicating a large set.
   • Prefer EXISTS over IN (subquery) when the subquery can short-circuit; prefer a join over IN when you need columns from both sides.

5. Warehouse / columnar engines need different levers. On columnar warehouses, indexes barely exist; instead:

   • Align the filter with the partition/cluster key so the engine prunes files/micro-partitions — check the plan's scanned-partitions/bytes-scanned, not just time.
   • Watch broadcast vs shuffle joins: broadcast a small dimension table, shuffle when both sides are large. A shuffle of two huge tables on a skewed key is the classic blowup.
   • Reduce columns scanned (columnar pays per column) — SELECT * is expensive here in a way it isn't on row stores.

6. Measure before/after on representative data and prove equivalence.

   • Capture baseline timing (median of a few warm runs, not one cold run) and the plan.
   • Apply the fix, re-run EXPLAIN ANALYZE, capture the new timing/plan.
   • Confirm the rewrite is logically equivalent: same row count and a checksum/ordered-diff of results on a sample, especially after touching DISTINCT, join type, or NULL-handling. A faster query that returns different rows is a bug, not an optimization.

Common Errors

• Tuning to the cold cache. First run reads from disk; second run is cached and 10x faster regardless of your change. Warm up, then take the median.
• Stale statistics misread as a bad query. Huge estimated-vs-actual row gap → run ANALYZE first and re-plan before adding indexes; the "fix" may just be fresh stats.
• An index that helps one query slows every write. Each index is maintained on every INSERT/UPDATE/DELETE. On write-heavy tables, weigh read gain against write cost; don't add a fourth near-duplicate index when reordering an existing one covers both.
• Function/cast silently disabling the index you just added. WHERE lower(email) = ? won't use an index on email — needs an expression index on lower(email) or a sargable rewrite.
• Plan flips with data volume. A plan that's optimal on today's 10k rows can go quadratic at 10M. Validate on production-scale row counts, or at least reason about how the chosen join (nested loop vs hash) scales.
• OR across columns defeating indexes. WHERE a = ? OR b = ? often can't use a single composite index — a UNION ALL of two indexed lookups is frequently faster.
• Raising work_mem/session knobs as the "fix." That hides the spill instead of removing it; it's a per-connection footgun under concurrency. Prefer reducing rows or indexing the sort away; bump memory only as a last, documented resort.

Verify

The optimization is done only when ALL hold:

• New plan no longer shows the original cost driver (seq scan gone / nested loop replaced by hash / sort no longer spilling / partitions pruned).
• Measured median runtime (or bytes-scanned on warehouses) improved on representative data — show the before/after numbers, not "should be faster."
• Result set is provably identical: same row count + matching checksum/ordered sample vs the original query.
• Write/maintenance cost of any new index was considered and noted for write-heavy tables.
• Estimated rows now track actual rows within ~1 order of magnitude (no stale-stats landmine left behind).