1
# Query Plan Reader
2
3
## 1. Get a plan with real numbers
4
5
An estimate-only plan tells you what the planner believes, not what happened. Ask for
6
timings and buffers:
7
8
```sql
9
-- Postgres
10
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;
11
12
-- MySQL 8+
13
EXPLAIN ANALYZE <query>;
14
15
-- SQLite
16
EXPLAIN QUERY PLAN <query>;
17
```
18
19
`ANALYZE` runs the query. On a write statement, wrap it in a transaction and roll back.
20
21
## 2. Find where the time actually goes
22
23
Read from the innermost node outward. For each node, note **actual time**, **actual
24
rows**, and **loops**. Two traps:
25
26
- **Postgres reports per-loop time.** A node showing 2ms with `loops=5000` cost 10
27
  seconds, not 2ms. Multiply before deciding anything.
28
- **Cost is not time.** Cost is an arbitrary unit for comparing plans. Never report it
29
  as a duration.
30
31
The bottleneck is the node with the largest actual total time that is not simply the
32
sum of its children. That is the one to fix. Everything else is noise.
33
34
## 3. Compare estimated rows to actual rows
35
36
This single ratio explains most bad plans.
37
38
| Estimate vs actual | What it means | What to do |
39
|---|---|---|
40
| Within about 10x | Planner is informed | Trust the plan shape; fix the node |
41
| Estimate far too low | Stale or missing statistics | `ANALYZE <table>` and re-check |
42
| Estimate far too high | Correlated predicates the planner treats as independent | Extended statistics, or rewrite |
43
44
A planner that thinks a node returns 1 row will happily choose a nested loop that runs
45
a million times. Fix the estimate before fixing anything else. Very often the whole
46
problem is stale statistics and no index is needed at all.
47
48
## 4. Identify the real problem
49
50
Work through in order:
51
52
1. **Sequential scan on a large table with a selective predicate.** Missing or unusable
53
   index. Check the predicate is sargable: a function on the column, a leading
54
   wildcard, or an implicit type cast all prevent index use.
55
2. **Nested loop with high loop count.** Usually a bad row estimate upstream. Fix the
56
   estimate first.
57
3. **Sort or hash spilling to disk.** Look for "external merge" or "Disk". Either
58
   reduce the rows before sorting, or raise the working memory setting.
59
4. **Index scan that still reads most of the table.** The index is not selective enough
60
   to be worth it. A different column order, or none at all.
61
5. **Filter removing most rows after fetching them.** The predicate ran after the scan.
62
   Get it into the index.
63
64
## 5. Propose the smallest fix
65
66
In order of preference:
67
68
1. **Update statistics.** Free, instant, and often sufficient.
69
2. **Rewrite the query.** Make a predicate sargable, remove a needless DISTINCT, push a
70
   filter into a subquery. No schema change, no ongoing cost.
71
3. **Add one index.** Column order matters: equality columns first, then the range
72
   column, then anything you want covered. One well-ordered composite index usually
73
   beats three single-column ones.
74
4. **Change the schema.** Last resort. Say what it costs.
75
76
Never propose more than one index at a time. Add it, re-measure, then decide whether
77
another is still needed. Every index slows writes and takes space, forever.
78
79
## 6. Verify
80
81
Re-run `EXPLAIN ANALYZE`. Confirm three things:
82
83
- The new index is actually used. If it is not, say so, and remove it.
84
- Actual time improved, and by how much.
85
- No other node got worse.
86
87
Report the before and after as two numbers with the same units, and name what changed.
88
If the fix did not work, say that plainly rather than reaching for another index.
89