SQL Query Optimizer

Make queries fast by understanding the plan, not by guessing. The costly mistake this skill prevents is the shotgun index: adding indexes on instinct, which slows every write and bloats storage while the actual cost driver - a non-sargable predicate, a mis-ordered composite, an N+1 in the ORM - survives untouched.

Operating procedure

Diagnosis strictly precedes any fix, because the right fix depends on which cost driver the plan reveals.

Step 1: gather inputs

Collect (label guesses as guesses):

1. The query text and, if ORM-generated, the ORM code that emits it.
2. EXPLAIN ANALYZE output (or EXPLAIN if production-unsafe to execute; note that estimates only).
3. Approximate row counts of the tables involved, and existing indexes (\d table or equivalent).
4. Current latency, target latency, and how often the query runs. A 200 ms query at 500 QPS matters more than a 5 s nightly report.
5. Write volume on the tables - this prices any new index.

Step 2: read the plan for cost drivers