Why EXPLAIN ANALYZE Matters
PostgreSQL's plain EXPLAIN shows you the planner's intended strategy along with estimated costs and row counts, but it never runs the query. EXPLAIN ANALYZE actually executes the statement and instruments every node in the plan tree, reporting real elapsed time, real row counts, and how many times each node was looped. The difference between the two is the difference between a chef's recipe and a chef's actual timed cooking log.
Cricket analogy: It is the difference between a pre-match team sheet predicting Virat Kohli will face 40 balls and the actual scorecard after the innings showing he faced 52 and was dismissed lbw; EXPLAIN is the prediction, EXPLAIN ANALYZE is the scorecard.
Reading the Plan Tree
Plan output is a tree of nodes, most deeply indented nodes executing first and feeding their output upward to parent nodes such as Nested Loop, Hash Join, or Sort. Each node line shows cost=startup_cost..total_cost, the estimated rows and average row width, and, under ANALYZE, actual time=startup..total in milliseconds, actual rows, and loops, where loops tells you how many times that node was re-executed, typically because it sits on the inner side of a nested loop.
Cricket analogy: A batting order is a tree of dependencies: the opening partnership's outcome feeds into the middle order's run rate targets, just as an inner index scan node's output feeds a nested loop join above it.
Cost Units and Estimates vs. Actual
Cost figures are not milliseconds; they are arbitrary units derived from planner parameters like seq_page_cost (default 1.0), random_page_cost (default 4.0), and cpu_tuple_cost, used purely to compare candidate plans against each other. The real diagnostic value comes from comparing the planner's estimated rows against the ANALYZE actual rows: a wide gap, for example estimated rows=10 but actual rows=50000, means table statistics are stale or the predicate is too complex for the planner's histogram to model, and it is often the root cause of a bad plan choice.
Cricket analogy: A pre-series pundit's prediction that a pitch will yield 15 wickets is like the planner's cost estimate, but if the actual Test match produces only 4 wickets on a flat Chennai deck, that gap signals the pundit misjudged conditions, just like stale statistics mislead the planner.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.customer_name, SUM(o.total_amount) AS total_spent
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.customer_name
ORDER BY total_spent DESC
LIMIT 10;
-- Sample output (truncated):
-- Limit (cost=4520.34..4520.36 rows=10 width=40) (actual time=38.221..38.225 rows=10 loops=1)
-- Buffers: shared hit=1204 read=312
-- -> Sort (cost=4520.34..4532.10 rows=4703 width=40) (actual time=38.219..38.221 rows=10 loops=1)
-- Sort Key: (sum(o.total_amount)) DESC
-- Sort Method: top-N heapsort Memory: 26kB
-- -> HashAggregate (cost=4310.11..4357.14 rows=4703 width=40) (actual time=35.902..37.512 rows=4689 loops=1)
-- Group Key: c.customer_name
-- -> Hash Join (cost=180.00..4100.55 rows=41912 width=32) (actual time=2.113..21.887 rows=42315 loops=1)
-- Hash Cond: (o.customer_id = c.customer_id)
-- -> Seq Scan on orders o (cost=0.00..3210.00 rows=41912 width=12) (actual time=0.015..12.004 rows=42315 loops=1)
-- Filter: (order_date >= '2026-01-01'::date)
-- -> Hash (cost=130.00..130.00 rows=4000 width=28) (actual time=2.062..2.063 rows=4000 loops=1)
-- -> Seq Scan on customers c (cost=0.00..130.00 rows=4000 width=28) (actual time=0.006..0.812 rows=4000 loops=1)Always include BUFFERS with ANALYZE: EXPLAIN (ANALYZE, BUFFERS). It reports shared hit (found in cache) versus read (fetched from disk or OS cache) block counts per node, which is often the real reason a query is slow even when row estimates look fine.
Spotting Problems in the Output
Common red flags include a Seq Scan on a large table where you expected an Index Scan, a Nested Loop whose inner node has loops in the thousands each doing meaningful work, a Sort or Hash node reporting a disk-based sort method instead of an in-memory quicksort or top-N heapsort, and any node whose actual rows wildly exceeds estimated rows, cascading misestimation upward through the tree. Reading bottom-up and looking for where actual time first balloons relative to the node beneath it usually pinpoints the true bottleneck rather than the node with the largest raw number.
Cricket analogy: A fielding captain spots trouble when a part-time bowler concedes 60 runs in 4 overs instead of the expected 25, the way a query tuner spots trouble when a Seq Scan runs far longer than an Index Scan should have.
EXPLAIN ANALYZE actually executes the query, including INSERT, UPDATE, DELETE, and any side-effecting functions. Wrap it in a transaction and roll back if you are testing a data-modifying statement: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;. Never run it directly against production write statements without this safeguard.
- EXPLAIN shows the planned strategy with estimates only; EXPLAIN ANALYZE actually runs the query and reports real timings and row counts.
- Plan trees execute bottom-up: deeply indented leaf nodes run first and feed parent nodes like joins, sorts, and aggregates.
- Cost numbers are arbitrary planner units for comparing candidate plans, not milliseconds; actual time is the real wall-clock measurement.
- A large gap between estimated rows and actual rows signals stale statistics or an unmodeled predicate and often explains a bad plan choice.
- loops shows how many times a node re-executed, critical for spotting expensive inner nodes of nested loops.
- Always add BUFFERS to see shared hit versus read block counts, which often reveals the real I/O bottleneck.
- Wrap EXPLAIN ANALYZE of write statements in a transaction with ROLLBACK to avoid actually modifying data.
Practice what you learned
1. What is the key difference between EXPLAIN and EXPLAIN ANALYZE?
2. In an EXPLAIN ANALYZE plan node, what does 'loops=500' most likely indicate?
3. Why should EXPLAIN ANALYZE of an UPDATE statement be wrapped in a transaction with ROLLBACK?
4. A plan shows estimated rows=20 but actual rows=90000 on a Seq Scan node. What does this most strongly suggest?
5. What additional information does adding BUFFERS to EXPLAIN ANALYZE provide?
Was this page helpful?
You May Also Like
The Query Planner and Cost Estimation
Understand how PostgreSQL's planner generates candidate execution plans and estimates their cost so it can pick the cheapest one.
Join Algorithms in PostgreSQL
Understand the three physical join strategies PostgreSQL can choose — nested loop, hash join, and merge join — and when each one wins.
Window Functions Explained
Learn how PostgreSQL's window functions compute values across a set of related rows without collapsing them, enabling rankings, running totals, and moving averages.