Reading EXPLAIN ANALYZE
EXPLAIN ANALYZE prints a plan tree annotated with estimate vs actual. The gap between estimated rows and actual rows is the signal that tells you the planner was lied to.
A report query times out at 30 s in production. A junior rewrites the SQL three times; nothing helps. You run EXPLAIN ANALYZE once and the answer is sitting on line three: a node says rows=1 estimated, rows=2,300,000 actual. The planner thought it would touch one row and built the whole join around that lie. Nobody had read the plan. Reading it is a five-minute skill that prevents five-hour incidents.
What the output actually is
EXPLAIN ANALYZE <query> runs your query and prints the plan tree that produced it, one node per line, indented to show nesting. The databases track’s execution-plans chapter derives why the planner builds a tree of scans and joins; here you only learn to read it like a runbook (a step-by-step operational reference). Each node line carries two cost groups:
- Estimates (from planning):
cost=0.00..431.00(startup..total in arbitrary cost units) androws=1000— what the planner guessed before running. - Actuals (from execution):
actual time=0.012..4.318(ms) androws=998 loops=1— what really happened.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.status = 'shipped';A real (trimmed) result:
Hash Join (cost=33.50..812.40 rows=1200 width=20)
(actual time=1.2..58.7 rows=1180 loops=1)
Hash Cond: (li.order_id = o.id)
Buffers: shared hit=512 read=140
-> Seq Scan on line_items li
(cost=0..512 rows=20000 width=12)
(actual time=0.01..21.3 rows=20000 loops=1)
-> Hash (cost=20..20 rows=300 width=12)
(actual time=1.1..1.1 rows=305 loops=1)
-> Index Scan on orders o
(cost=0.29..20 rows=300 width=12)
(actual time=0.03..0.8 rows=305 loops=1)
Index Cond: (status = 'shipped')
Planning Time: 0.4 ms
Execution Time: 59.4 msRead it inside-out, watch the gap
The tree executes from the deepest, innermost nodes outward — leaves first, root last. Read it the same way. For each node ask one question: does rows= estimated match rows= actual?
- Estimated 300, actual 305 → the planner is well calibrated. Trust this subtree.
- Estimated 1, actual 2,300,000 → the planner was misestimating. Every decision above this node (which join algorithm, which join order) was made on a wrong row count, so the whole plan above it is suspect. This is the single highest-value signal in the output, and lesson 03 is entirely about fixing it.
A 10× gap is a yellow flag; a 1000× gap is the bug. The fix is almost never “rewrite the query” — it’s refresh statistics, add an index the planner can trust, or correct correlated-column estimates.
Three numbers people misread
Loops — actual time is per-loop, not total. On the inner side of a nested loop you’ll see actual time=0.05..0.06 rows=1 loops=50000. That node ran 50,000 times. Total time at that node is roughly per-loop time × loops ≈ 0.06 ms × 50000 = 3 seconds, not 0.06 ms. The most common rookie misread is “this node is fast” while it is secretly the entire runtime. Likewise rows on a looped node is the per-loop average; multiply by loops for the real total.
Cost vs time. cost is unitless and only meaningful for comparing candidate plans inside the planner (lesson 02). It is not milliseconds and not comparable across machines. For diagnosing a real query, ignore cost and read actual time and rows.
BUFFERS — where the I/O went. BUFFERS adds shared hit=N (8 KB pages served from the buffer cache) and read=N (pages fetched from disk/OS). A node with shared read=180000 is I/O-bound: it pulled 180k pages (~1.4 GB) off disk. That, not CPU, is your slowness — the fix is a more selective index or scanning less data. BUFFERS is the difference between guessing “maybe it’s I/O” and proving it.
▸Why this works
Why does EXPLAIN ANALYZE actually execute the query (and so can be dangerous on writes)? Because the “ANALYZE” half means “instrument and run”, not “plan only”. Running EXPLAIN ANALYZE on an UPDATE or DELETE performs the write. To inspect a mutating statement safely, wrap it: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. The instrumentation itself also adds overhead (a clock read per node), so Execution Time here is slightly inflated versus the bare query — use it for comparison, not as an SLA number. FORMAT TEXT (the default) is for reading by eye; FORMAT JSON when a tool will parse it.
Order how a senior reads an EXPLAIN ANALYZE plan to find the problem:
- 1 Run EXPLAIN (ANALYZE, BUFFERS) on the slow query
- 2 Find the innermost (deepest-indented) nodes — they run first
- 3 At each node compare estimated rows to actual rows
- 4 Locate the node with the largest estimate-vs-actual gap
- 5 Multiply per-loop time by loops to get that node's true cost
- 6 Check BUFFERS to see whether the cost is disk reads or cache hits
- 7 Decide the fix: stats refresh, index, or rewrite — by what the numbers showed
A nested-loop inner node shows: actual time=0.04..0.05 rows=1 loops=80000. How much wall time does this node really cost?
A Seq Scan node reads rows=1 estimated but rows=2,300,000 actual. What does this tell you first?
- 01What is the single most useful number to read in EXPLAIN ANALYZE output and why?
- 02Why is 'actual time=0.05 rows=1' on a looped node misleading, and how do you get the real cost?
- 03What does BUFFERS add, and what does shared hit vs read tell you?
EXPLAIN ANALYZE runs the query and prints the chosen plan tree, one indented node per line, each carrying the planner’s estimate (cost, rows) beside the executor’s actual (time, rows, loops). Read it inside-out — deepest nodes run first — and at every node compare estimated to actual rows: the largest gap is where the planner was misled and where the real fix belongs (statistics, an index, correlated-column estimates — lessons 02 and 03), not a query rewrite. Watch two traps: on looped nodes actual time and rows are per loop, so multiply by loops for the true cost; and cost is a unitless planning yardstick, not milliseconds. Add BUFFERS to turn “feels like I/O” into proof — shared hit is cache, read is disk. Now when you see a slow query, your first move is EXPLAIN (ANALYZE, BUFFERS) — not a rewrite, not a new index — and the first number you read is the estimated-vs-actual row gap at each node.
Practice
Start at the top. Tasks go easiest → hardest: recall a fact, apply it to a case, then a senior-level stretch. Open one, attempt it, then reveal.
Something unclear?
Ask a question about this lesson. Questions are anonymous and go straight to the author to make the lesson better.
Apply this
Put this lesson to work on a real build.