EXPLAIN & EXPLAIN ANALYZE — Reading Query Plans in Practice
Query planning is the database's guess about the fastest way to run a query. EXPLAIN prints that guess as a tree of nodes. EXPLAIN ANALYZE actually runs the query and prints the guess next to what really happened — row by row, cost by cost, node by node. Reading the gap between those two columns, in the right order, is the single most useful skill in database performance work.
Key Components
rows=(estimated) vs. actual rows- The planner's guess at how many rows a node will produce, next to what it really produced. Comparing these two numbers, at every node, is the core diagnostic move in this entry.
loops- How many times a node executed — greater than 1 whenever the node sits on the inner side of a nested loop. When
loopsis above 1, theactual timeandrowsshown are averages per execution, not totals. - Rows Removed by Filter
- Rows this node read (or, for a join, row pairs it considered) and then threw away because they failed a condition. It is a direct measure of wasted work, and it appears on index scans just as readily as on sequential scans.
Index Condvs.Filter- Where a condition gets applied.
Index Condis checked during the B-tree traversal, so a non-matching row is never visited.Filteris checked after the row is already fetched from the heap, so a non-matching row is fully paid for and then discarded. - Startup cost & total cost
- Startup cost is the estimated work before the first row can come out; total cost assumes the node runs to completion. Costs are cumulative upward — a parent node's total cost already includes the cost of every child beneath it.
Concrete Example
One scan node from a real plan, field by field:
Seq Scan on transactions (cost=0.00..470.00 rows=7000 width=244)
(actual time=0.030..1.995 rows=7000 loops=1)cost=0.00..470.00— startup cost (nothing to do before the first row) and total cost (the cost if the node runs to completion). This is a leaf scan with no children, so the full470.00is this node's own work; on a parent node, part of that number would belong to the children beneath it.rows=7000— the estimate, assuming the node runs to completion.width=244is the estimated average row size in bytes.actual time=0.030..1.995— time to the first row, then time to the last row.rows=7000 loops=1— actual output. This node ran once, so no adjustment is needed.
Now the same fields on a node that sits inside a nested loop, where loops is not 1:
Index Scan on transactions (actual time=0.012..0.014 rows=3 loops=50000)0.014 ms and 3 rows look harmless. They are not totals. The actual time and rows shown are averages per execution, and have to be multiplied by loops to get the true total. Do the arithmetic on the page rather than trusting the first impression:
0.014 ms × 50,000 loops ≈ 700 ms
3 rows × 50,000 loops = 150,000 rows"700 ms, 150,000 rows" is a diagnosis you can act on. "It looks fast" is not — and this exact gap is how a 40-minute query hides inside its own plan.
Visual Model
Picture a plan as a small org chart where a rumor started at the bottom. Each node passes a row count up to its parent, and sometimes that rumor is badly wrong because a filter turned out far less selective than the planner guessed. Your job is not to listen at the top — it is to walk down the chart until you find the node where the rumor first went wrong. Two rules make the walk correct: a node's own cost is what is left after you subtract its children's cost, because totals are cumulative upward; and inside anything that repeats, the "actual" numbers are a per-execution average, so you must multiply by loops before comparing them to anything else.
Step through a real plan below, applying both rules in the order that finds the cause rather than its echo.
Loading…
Deeper — Edge Cases & Gotchas
Run ANALYZE on a write, and the write happens
"Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual." — PostgreSQL Documentation
EXPLAIN ANALYZE directly on an UPDATE or DELETE to "just check the plan."
EXPLAIN ANALYZE UPDATE transactions SET status = 'FAILED' WHERE txn_id = 42;Why it breaks: EXPLAIN only discards the row output a SELECT would have returned. The update itself commits normally. Row 42 is now really set to 'FAILED'. Make this reflex before running ANALYZE on any write:
BEGIN;
EXPLAIN ANALYZE UPDATE transactions SET status = 'FAILED' WHERE txn_id = 42;
ROLLBACK;Index Cond versus Filter — what Rows Removed by Filter actually measures
A row counted by Rows Removed by Filter is one the engine already visited the heap for: it paid the page access, read the tuple, checked visibility, and then dropped it. It is not a statement about access strategy, only about wasted work.
| Applied where | Cost of a non-matching row | |
|---|---|---|
Index Cond | during the B-tree traversal | never visited — the index entry is skipped |
Filter | after the row is retrieved | fully paid — heap fetch performed, then discarded |
The fix is more specific than "add an index" — an index often already exists. The goal is to move the predicate from Filter into Index Cond:
idx(merchant_id) → Index Cond: merchant_id Filter: status ← 8734 wasted
idx(merchant_id, status) → Index Cond: merchant_id AND status ← 0 wastedA Filter line on an index scan with a large Rows Removed count is really the question "can that predicate become part of the index?" — the question composite index design exists to answer.
Scan node vocabulary
- Seq Scan — reads every page of the table. Not automatically bad: fine for a small table or when a large fraction of rows is needed. It is a warning sign when paired with a large
Rows Removed by Filter. - Index Scan — descends the B-tree, then fetches each matching row from the heap. Two structures touched per row; good for high selectivity.
- Index Only Scan — answered entirely from the index, no heap access, provided the visibility map says the page is all-visible.
Heap Fetches: 0is healthy; a large number means the visibility map is stale and the "index only" scan is secretly visiting the heap anyway. - Bitmap Heap Scan with a Bitmap Index Scan beneath — collects matching row locations, sorts them into physical page order, then reads the heap sequentially. Chosen when there are too many rows for individual random fetches but too few for a full scan.
Recheck Condappears because a large bitmap can go lossy (whole pages instead of exact rows), forcing a re-check of the condition.
Physical warning signs
| Signal | Meaning |
|---|---|
Rows Removed by Filter: 3000000 | the scan read millions of rows and threw them away — a missing or unusable index |
Batches: 16 under a Hash | the hash table spilled to disk — work_mem too small, or the row estimate was wrong |
Sort Method: external merge Disk: 24MB | the sort spilled to disk instead of staying in memory |
Heap Fetches: 480000 on an Index Only Scan | VACUUM is behind on the visibility map |
Seq Scan on the inner side of a nested loop | an emergency signal from join algorithms |
loops= in the tens of thousands | an N+1 shape inside the engine |
Options worth knowing
Run as EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) SELECT ...;
| Option | Default | Adds |
|---|---|---|
ANALYZE | off | actually runs the statement and shows real run times |
BUFFERS | off, implied by ANALYZE | shared/local/temp blocks hit, read, dirtied, written |
VERBOSE | off | per-node output columns, schema-qualified names |
SETTINGS | off | planner-relevant settings that differ from the built-in defaults |
TIMING | on | per-node timing — set off when clock overhead distorts a fast query |
GENERIC_PLAN | off | plans a parameterised query without values; cannot combine with ANALYZE |
FORMAT | text | JSON / YAML / XML for tooling and visualisers |
SETTINGS is underused and very effective in an unfamiliar environment — it often explains a surprising plan immediately. GENERIC_PLAN closes the loop on the custom-versus-generic plan question from query planning: it shows what the prepared-statement form of a query gets, without needing real parameter values.
Why BUFFERS beats timing when comparing two plans
Timings depend on cache warmth, background load, and machine noise. Buffer counts do not — they are a deterministic count of pages touched, made observable through the buffer pool.
Buffers: shared hit=200000 read=812 → shared hit=1500 read=40That is a real collapse in work, whatever the clock said on any single run. This matters most on a small, fully-cached practice dataset: everything fits in memory either way, so a bad plan and a good plan can take almost the same wall-clock time while the buffer counts still separate cleanly. EXPLAIN (ANALYZE, BUFFERS) is a safer default than plain EXPLAIN ANALYZE for exactly this reason.
Planning time versus execution time
Planning time covers turning the parsed query into an optimized plan; it does not include parsing or rewriting. Execution time covers running that plan, including executor start-up/shutdown and any triggers fired — but not parsing, rewriting, or planning. Two consequences: if planning time rivals execution time, the query is trivial and a prepared statement will help; and because execution time includes trigger time, a mysteriously slow INSERT with a trivial-looking plan is usually a trigger doing invisible work.
Practical habits
- Run it twice. The first run pays cold-cache costs; the second reflects steady state. Compare
shared readbetween the two runs. - Make
EXPLAIN (ANALYZE, BUFFERS)the default incantation, not plainEXPLAIN ANALYZE. - Wrap any write in
BEGIN; ... ROLLBACK;before addingANALYZE. - Read bottom-up for what happened — rows flow up from the scans. Read top-down for what it cost — costs accumulate down from the root.
- Predict the scan and join type before running the query. Being wrong is where the learning is.
- Use
FORMAT JSONwhen a plan is too large to read by eye, so a visualiser can render it.
See Also
Query Planning & Optimization — How the Database Picks a Plan The planner that produces every estimate this entry teaches you to check — EXPLAIN is that process made visible. Join Algorithms — Nested Loop, Hash Join & Merge Join The join type printed on a plan node is a consequence of the row estimate, not the cause of a slow query — the worked example proves it. SQL Fundamentals — Joins, Aggregations, Subqueries & CTEs Every node in a plan traces back to a clause in the query — this entry supplies that clause-level vocabulary. B-tree Indexes — Structure & How Range/Equality Queries Use Them The structure an Index Scan actually walks, and what anIndex Cond line is checking against.
Composite Indexes — Column Order & the Leftmost-Prefix Rule
The fix for the worked example's Filter-vs-Index-Cond gap: moving a predicate from Filter into Index Cond.
Covering Indexes & Index-Only Scans
Heap Fetches: 0 is this entry working as intended; a large count means the visibility map is stale.
Buffer Pool / Page Cache — How a DB Manages Memory
The BUFFERS option reports shared hit/read counts straight out of this cache.
How Data Is Stored on Disk — Heap Files, Pages & Slots
What a heap fetch actually costs — the page-and-slot structure an Index Scan visits after the B-tree traversal.
Sources consulted
- PostgreSQL Documentation — 14.1. Using EXPLAIN — cited in source research
- PostgreSQL Documentation — EXPLAIN (SQL command reference) — cited in source research
- PostgreSQL Documentation — 52.5. Planner/Optimizer — cited in source research
- PostgreSQL Documentation — 20.4. Resource Consumption (work_mem) — cited in source research
Test Yourself
A node reads (actual time=0.012..0.014 rows=3 loops=50000). What did this node actually do in total?
A Hash Join node reads cost=0.00..15000.00. What does 15000 represent?
If you replace idx(merchant_id) with a composite idx(merchant_id, status), what should change in the Index Scan node's output?