SQL Explain Plan

A diagnostic-first read of an EXPLAIN ANALYZE output. Find the expensive node, identify the cause class, fix one thing. Optimized for Postgres but the patterns generalize to MySQL, SQLite, and DuckDB.

When to use

• A query is slow and the user has run (or can run) EXPLAIN ANALYZE
• They suspect a missing index but want confirmation
• A previously-fast query got slow after data growth or a schema change

Skip for: schema design (different skill); write-heavy bottlenecks (locking diagnostics); slow application code where the query is fine (profile the app, not the query).

Workflow

1. If the user pasted EXPLAIN (no ANALYZE), ask for EXPLAIN ANALYZE BUFFERS instead. Plain EXPLAIN shows the planner's estimate; EXPLAIN ANALYZE shows what actually happened. BUFFERS reveals cache hit/miss. Without the real numbers, half the diagnostics are guesswork. (Postgres syntax: EXPLAIN (ANALYZE, BUFFERS) SELECT ...)

2. Find the most expensive node. Read the plan tree from inner-most outward. Look at (actual time=X..Y rows=N loops=M). The expensive one is the node where:

   • actual time is the highest fraction of total query time
   • OR actual rows >> estimated rows (planner mis-predicted)
   • OR Rows Removed by Filter is high (scanning then discarding)

3. Identify the cause class. One of seven:

   Pattern in plan	Cause class	Typical fix
   Seq Scan over a large table with a WHERE filter	Missing index on the filter column	Add btree index
   Index Scan returning >>1% of table	Index doesn't help; full scan is faster	Drop the index OR add a covering index
   Nested Loop with high outer-loop count	Forced row-by-row join	Rewrite as hash/merge join; raise work_mem
   Rows Removed by Filter >> actual rows	Index condition too loose	Add a partial index OR a multicolumn index
   Sort with external merge Disk	Sort spilled to disk	Increase work_mem; OR add an index that pre-sorts
   Hash Join with high Memory Usage	Hash table builds bloated	Reduce table cardinality with an earlier filter; OR work_mem
   Estimated rows is way off from actual rows	Stale stats	ANALYZE <table>; OR raise default_statistics_target

4. Test the fix in isolation. Add the proposed index in a transaction, re-run EXPLAIN ANALYZE, see the new plan. ROLLBACK if it didn't help (or if the index size is unreasonable). Don't deploy "this should help" indexes — verify.

5. Mind the index cost. Each new index slows writes proportionally. Adding a 4-column compound index to a hot-write table can double INSERT latency. The fix needs to be worth the cost.

Output shape

**Plan summary**: <one sentence — what the query is doing>

**Bottleneck**: <node + actual time + cause class>

**Fix recommendation**
- <specific DDL or query rewrite>
- Expected impact: <estimate>
- Cost: <write-amplification or storage trade-off>

**Verify with**
- `EXPLAIN (ANALYZE, BUFFERS) <query>` after fix
- Look for: <specific signal that confirms the fix worked>

Definition of done

• Bottleneck node identified by name (not "the query is slow")
• Cause class named (one of the seven, or a clear new category)
• Fix proposed AND its cost
• A test that verifies the fix worked (before/after EXPLAIN comparison)

Gotchas

1. pg_stat_statements is your friend. If the user wonders WHICH queries to optimize, point them at pg_stat_statements for top-N-slowest aggregates. Optimizing a query that runs once a day matters less than one that runs 10K times an hour.

2. SELECT * hides cost. A query that pulls only the columns it needs can sometimes be index-only-scanned; SELECT * forces a heap fetch. Don't blindly add indexes when the query rewrite is the fix.

3. LIMIT 1 doesn't always speed things up. If the LIMIT comes AFTER a sort that requires the full set, you still paid for the sort. Look at where LIMIT is in the plan tree.

4. work_mem is per-operation, not per-query. A query with 5 sorts + 3 hashes can allocate 5 * work_mem total. Raising it has real RAM implications.

5. Statistics rot under bulk inserts. After a big load, ANALYZE the affected tables before benchmarking. Stale stats cause wildly wrong plans.

6. A Seq Scan is not always wrong. If the table is small (a few thousand rows), seq scan beats index lookup. The planner knows this; don't force an index where it isn't earning its keep.

7. Don't share EXPLAIN output containing real data values in public chat. Plans often quote actual filter values. Redact PII before pasting.

8. The fix is rarely "rewrite the whole query." 90% of slow queries are fixed with one index, one ANALYZE, or one small WHERE-clause adjustment. If your recommendation is a full rewrite, double-check that a smaller fix wouldn't suffice.