How to read EXPLAIN plans like a pro
Simple terms
Read a plan top-down to understand it. It runs bottom-up though, the leaves feed their parents. Then do the one thing that finds most problems: compare estimated rows against actual rows, node by node. Where those two numbers pull apart sharply is where the planner was working from a wrong picture of your data. That spot is usually upstream of whatever node looks slow. Fix the estimate and the plan very often fixes itself.
You might be asked
Walk me through how you read an EXPLAIN plan. Given a slow query in production, how do you find the problem in the plan and prove your fix worked?
How you’d answer it
Read the plan top-down to see what runs, the root is the final step. But it executes bottom-up: leaf scans feed their parents.
For every node, put the planner's guess next to reality: `cost=.. rows=est` against `actual rows=N loops=M` from EXPLAIN ANALYZE. The gap is where the planner was wrong, and that is where the problem usually lives.
Watch the arithmetic. `rows x loops` is the true row count, the one people misread.
Add `BUFFERS` and you get real I/O instead of estimates: `shared hit/read`, and `temp read/written`, which is a work_mem spill announcing itself.
Then hunt the usual villains. A Seq Scan that should use an index. A misestimate that blew up a join. A sort or bitmap that spilled. Heap Fetches on an index-only scan.
Fix one thing, re-run EXPLAIN (ANALYZE, BUFFERS), and show the node that changed.
What the docs say
An EXPLAIN plan reads top-down but runs bottom-up. The node at the top is the final step that produces your rows, while the actual work starts at the leaf scans and flows upward, each node feeding its parent. Getting that direction straight is half of reading a plan.
Every node shows the planner's estimate, a start-up and total cost in arbitrary units (a sequential page fetch is 1.0 by convention), plus an estimated row count and width. Costs are cumulative: a node's cost already includes its children's. Run EXPLAIN ANALYZE and you also get the measured side, actual time, rows, and loops.
The single most useful habit is comparing estimated rows against actual rows on each node. A large gap is where the planner guessed wrong, and that is usually where a bad plan is born, a misestimate that picked the wrong join, say. Two traps catch people here, and the manual calls out both. First, when a node runs inside a loop, its "actual rows" is the average per loop, so the real total is rows times loops. Second, wasted work hides in the "Rows Removed by Filter" line, a node reading far more rows than it keeps.
Add BUFFERS to see real I/O: shared hit versus read tells you what came from cache versus disk, and any temp read/written (or a "Sort Method: external merge Disk: NkB") means something spilled past work_mem. From there you are hunting the usual villains, a sequential scan that should have used an index, a sort or bitmap that spilled, heap fetches on a supposedly index-only scan. Fix the cause, then re-run EXPLAIN (ANALYZE, BUFFERS) and point at the specific node that changed. That before-and-after on the node is your proof, not a wall-clock stopwatch.
References
- 1. PostgreSQL docs, Using EXPLAIN
- 2. PostgreSQL docs, EXPLAIN command (options incl. GENERIC_PLAN, SETTINGS, WAL)
- 3. Source: src/backend/commands/explain.c (ExplainNode, show_sort_info, show_tidbitmap_info) @ REL_17_10.
- 4. PostgreSQL docs, Planner cost constants & Statistics Used by the Planner
What the code does
Lens 2 (navigated, PG 17.10, REL_17_10). Every line you read in a plan is printed by ONE recursive walker: ExplainNode() in src/backend/commands/explain.c (entry at line 1367). It is called once per plan node, prints that node's line, then recurses into its children, which is exactly why the text is a top-down tree. The numbers you compare come from two different places in the code, and knowing this is the whole game:
- The ESTIMATES (`cost=. rows=. width=.`) are printed from the Plan struct the planner produced (costsize.c filled them).
- The ACTUALS come from `planstate->instrument`, the runtime instrumentation the executor accumulated. For example `Heap Fetches` for an Index-Only Scan is literally `ExplainPropertyFloat("Heap Fetches", NULL, planstate->instrument->ntuples2,..)` (explain.c ~line 1993), a counter the executor bumps every time the visibility map said a page was NOT all-visible and it had to go to the heap.
- `Sort Method: external merge Disk: NkB` is printed by show_sort_info() (line 2945): it calls tuplesort_get_stats() and prints the method name + space type + space used. "external merge" vs "quicksort" is a fact reported by the sort machinery, not a guess.
- `Heap Blocks: exact=N lossy=M` is printed by show_tidbitmap_info() (line 3592) straight from `planstate->exact_pages` / `planstate->lossy_pages`. When the per-page bitmap can't fit exact tuple IDs in work_mem it degrades a page to "lossy" (just the page number), and the executor then re-checks the index condition against every tuple on that page, which is why a lossy scan also prints `Rows Removed by Index Recheck` (explain.c ~line 2004). Plain-language takeaway: the plan is not narrative, each line is a direct print of either a planner estimate or an executor counter, so 'estimate vs actual' is built into the code path.Proof from a real run
All captured on pgi17 = PostgreSQL 17.10, table `events` (500,000 rows), literal output.
VILLAIN 1, Seq Scan that should use an index (account_id = 4242):
BEFORE (no index):
Gather (cost=1000.00..12709.07 rows=99 width=120) (actual rows=108 loops=1)
Buffers: shared hit=9095
-> Parallel Seq Scan on events (actual rows=36 loops=3)
Filter: (account_id = 4242)
Rows Removed by Filter: 166631
Execution Time: 17.774 ms
AFTER `CREATE INDEX idx_events_account ON events(account_id)`:
Bitmap Heap Scan on events (cost=5.19..371.44 rows=99 width=120) (actual rows=108 loops=1)
Recheck Cond: (account_id = 4242)
Heap Blocks: exact=107
Buffers: shared hit=107 read=3
-> Bitmap Index Scan on idx_events_account (actual rows=108 loops=1)
Execution Time: 0.374 ms
Read it: 9095 buffers + 166,631 rows thrown away -> 110 buffers; 17.774 ms -> 0.374 ms (~47x). (Honest note: with 108 matching rows the planner chose a Bitmap Heap Scan, not a plain Index Scan, both are index-driven; for a unique lookup you'd see Index Scan.)
VILLAIN 2, sort spilled past work_mem (ORDER BY payload):
work_mem='64kB':
Buffers: shared hit=9169, temp read=21770 written=28838
-> Sort Sort Method: external merge Disk: 17200kB
work_mem='256MB':
Buffers: shared hit=9169 <- no temp lines at all
-> Sort Sort Method: quicksort Memory: 30734kB
The `temp read/written` numbers and `external merge Disk` are the spill signal, that, not wall-clock, is the reliable tell.
VILLAIN 3, Heap Fetches on an index-only scan (visibility map):
After churn the planner AVOIDED an index-only scan (cost of heap visits too high) and used a Bitmap Heap Scan: `Buffers: shared hit=8530`, 27.313 ms. After `VACUUM (ANALYZE) events` set the visibility map:
Index Only Scan using idx_events_acct_only on events (actual rows=19800 loops=1)
Heap Fetches: 0
Buffers: shared hit=37
Execution Time: 0.829 ms
8530 buffers -> 37 buffers, Heap Fetches: 0. The visibility map is what makes an index-only scan actually index-only.
VILLAIN 4, lossy bitmap + index recheck (work_mem='64kB', bitmap forced):
Bitmap Heap Scan on events (actual rows=60087 loops=1)
Recheck Cond: ((account_id >= 1) AND (account_id <= 600))
Rows Removed by Index Recheck: 406332
Heap Blocks: exact=578 lossy=8791
Same query at work_mem='64MB': `Heap Blocks: exact=9369` (no lossy, no recheck). The 406,332 rechecked rows are pure overhead caused by too little work_mem for the bitmap.Using it under pressure
A situation where it matters
Common real-world scenario (tested): "a reporting query got slow this week." You run `EXPLAIN (ANALYZE, BUFFERS) SELECT a.plan, count(*) FROM events e JOIN accounts a ON a.id=e.account_id WHERE e.status='error' AND a.plan='team' GROUP BY a.plan;` and get (literal):
Hash Join (cost=109.34..12179.87 rows=1442 width=4) (actual rows=1162 loops=3)
-> Parallel Seq Scan on events e (actual rows=3331 loops=3)
Filter: (status = 'error'::text)
Rows Removed by Filter: 163336
Execution Time: 24.112 ms
Now you can read it: the join estimate (rows=1442) is close to actual (1162x... = 3486 total), so the JOIN is fine; the cost is the Parallel Seq Scan throwing away 163,336x3 'ok' rows to find the few 'error' rows. The fix isn't the join, it's a partial index `CREATE INDEX ON events(account_id) WHERE status='error'`. Now you understand WHY: the plan told you exactly which node burns the time (Rows Removed by Filter), not which node looks scary.
The call you would make
Designing for a latency target (say p95 < 5 ms on a point lookup at high QPS): you design the plan you WANT and prove the planner produces it. Measured here: the same `account_id=4242` lookup is 17.774 ms as a parallel seq scan vs 0.374 ms once an index exists, so for a 5 ms budget the seq-scan plan is disqualified on its own. Go further with covering/index-only scans: the `account_id < 200` aggregate dropped from 8530 buffers to 37 buffers (0.829 ms) as an Index Only Scan once the visibility map was set. Design rules that fall out of the numbers:
- 1
Size
work_memso the hot sorts/bitmaps stay in memory, at 64kB the sort wrote 28,838 temp blocks, at 256MB it wrote zero; - 2
Keep heavily-read tables well-vacuumed so index-only scans stay heap-fetch-free;
- 3
Use `EXPLAIN (
GENERIC_PLAN)` (PG16+) in CI to verify a parameterized query still plans the index path before you ship.
When it goes wrong
Production triage with EXPLAIN, in order:
1. `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)`, SETTINGS surfaces any non-default GUC that shaped the plan. Captured proof: with a session `SET work_mem='64kB'`, the plan footer printed `Settings: work_mem = '64kB'`, instantly explaining the spill.
2. Find the node where estimate != actual by the largest factor, that is the planner's blind spot (stale stats, correlated columns, a bad cast).
3.
Look for the fingerprints: `Rows Removed by Filter` (missing/!partial index), `temp read/written` + `external merge Disk` (work_mem), `Heap Blocks: ... lossy=N` + `Rows Removed by Index Recheck` (work_mem for the bitmap), `Heap Fetches: N` on an index-only scan (vacuum/visibility map), huge `loops=` on a Nested Loop inner (misestimate).
4.
For a query you can't run live, `EXPLAIN (GENERIC_PLAN)` plans it without executing; `auto_explain` (preloaded here) captures slow plans from real traffic.
5. Prove the fix with a second `EXPLAIN (ANALYZE, BUFFERS)` showing the node changed and buffers/temp dropped.
What people get wrong
The trap: candidates read `actual rows=36` on an inner node and think 36 rows were processed. Debunked with a real capture, the parallel seq scan node showed `(actual rows=36 loops=3)`; the TRUE row count is rows x loops = 108 (and the Gather above it confirms `actual rows=108`).
Second trap: 'a higher cost number always means slower.' Cost is an unitless planner ESTIMATE; the only ground truth is `actual time` / buffers from ANALYZE. Third trap: 'index-only scan never touches the heap.' The capture proved otherwise, `Heap Fetches: 0` only AFTER VACUUM; before the visibility map was set the planner wouldn't even pick the index-only scan.
Follow-ups they'll push on
Q. What's the difference between cost and actual time? A: cost is the planner's unitless estimate used to choose between plans; actual time is measured wall-clock from ANALYZE. You compare estimates across candidate plans, but you trust actuals for the truth.
Q. Why is `loops` important? A: a node's `actual rows`/`actual time` are PER-loop averages; total work = value x loops. A Nested Loop with loops=100000 and 'cheap' inner per-loop is often the real cost.
Q. What does `Rows Removed by Index Recheck` mean? A: the bitmap went lossy (couldn't hold exact TIDs in work_mem), so the executor stored only page numbers and re-applied the condition to every tuple on those pages, overhead you remove by raising work_mem or tightening the scan.
Q. BUFFERS shows shared hit vs read, which is the problem? A: `read` = blocks fetched from the OS/disk (cache miss); `hit` = served from shared_buffers. High `read` on a repeated query points at cache pressure or a plan touching too many pages.
Version notes
Captured on PG 17.10; the reading method is identical across PG 14-18 with these differences: EXPLAIN (SETTINGS) is PG12+; the WAL option is PG13+; EXPLAIN (GENERIC_PLAN) is PG16+ (the F2 capture used it); SERIALIZE and per-node memory in EXPLAIN are PG17+. On PG18, BUFFERS is enabled by default when you write `EXPLAIN ANALYZE` (you no longer have to add BUFFERS explicitly), and index-only-scan accounting is unchanged. Parallel plan shape (Gather + Parallel Seq Scan) and the Heap Fetches / lossy-bitmap accounting behave the same on all five.
How this was verified
This concept is free end to end. The manual section is grounded in the official PostgreSQL documentation, the source walk was navigated in postgres/postgres at a version-pinned tag, and the evidence is labeled as raw output or a captured run summary. Where the text distinguishes mechanism (from docs/source) from consequence (measured), that boundary is kept explicit.
Connected
Where this concept connects
How this concept links across the library, the interview questions that test it, its plain-English glossary definition, and the guided pathways it belongs to. Open the full map to explore further.