Skip to content

Reading EXPLAIN (ANALYZE, BUFFERS): scans, joins, estimates vs actuals

Read a plan from the bottom: the lowest node whose estimate misses its actual rows is where the planner went wrong.

Predict first

Before you read: predict

ExamHall's check-in summary joins Hall B's rooms to their check-ins. Under a Nested Loop, EXPLAIN ANALYZE prints two child lines: Seq Scan on rooms (actual rows=12 loops=1) and Bitmap Heap Scan on checkins (actual rows=30 loops=12).

How many check-in rows did the Bitmap Heap Scan pass up to the Nested Loop, in all?

Pick one

The 30-second version

EXPLAIN prints the plan tree without running it; cost is in planner units, not milliseconds. EXPLAIN ANALYZE runs the statement and adds actual rows and loops per node. On a node that runs in loops, actual rows is per loop: multiply by loops. Find the lowest node whose estimate misses tenfold: stale statistics or correlated columns.

Read a plan from the bottom: the lowest node whose estimate misses its actual rows is where the planner went wrong.

How it works

Step 1 of 13

The tree. EXPLAIN prints the plan the planner chose, without running it. Rows flow upward: scans feed joins, which feed the top node.
In the kitchen
  • the round plan, drawn up at head office from last census's counts → the plan from statistics (steps 1, 7)

Analogy: A national census

Head office plans each district's rounds from the last census's counts. Enumerators then walk the rounds and log what they actually found.

In the census In the system
the round plan, drawn up at head office from last census's counts the plan from statistics (steps 1, 7)
the plan scored in effort points, not hours cost units (step 2)
the plan is paper until someone walks it; a round that hands out forms really hands them out EXPLAIN vs EXPLAIN ANALYZE (step 3)
"30 households per block, 12 blocks" in the log rows per loop × loops (step 6)
the first street where the count left the plan the lowest misestimated node (step 8)
an estate built since the last census, missing from the plan until a recount stale statistics (step 9)
"flats" and "no garden" planned as independent, though no flat has a garden correlated columns (step 10)
forms already in the enumerator's bag, or fetched from the district office shared hit, shared read (step 11)

In real terms: before tuning a slow query, put each node's estimated rows next to its actual rows × loops, and start at the bottom.

Breaks down when: you expect the plan to adapt. An enumerator who meets an unplanned estate changes route on the spot; a running plan keeps its nested loop for every row.

Contrasting case: a node that ran once. Its actual rows is already the total; loops only matter where a parent reruns a child (step 5).

Examples, run for real

4.1 ExamHall: the results page after an import

EXH-1270: the import loaded ECON-201's answers (2,400 candidates × 60 questions) into a table last analyzed before the import. MR !1470 runs ANALYZE answers at the end of every import.

4.1 results page for ECON-201 (exam 201), after the bulk import of 144,000 answers
  plain EXPLAIN (not run): top node Aggregate, cost 12.65 planner units, est rows=1; no actual rows
  EXPLAIN (ANALYZE, BUFFERS), statistics from before the import:
    Aggregate · est rows=1 · actual rows=2400 loops=1 · buffers hit=433032 read=0
      -> Sort · est rows=1 · actual rows=144000 loops=1 · buffers hit=433032 read=0
        -> Nested Loop · est rows=1 · actual rows=144000 loops=1 · buffers hit=433032 read=0
          -> Index Scan using answers_exam on answers · est rows=1 · actual rows=144000 loops=1 · buffers hit=1032 read=0
          -> Index Scan using candidates_pkey on candidates · est rows=1 · actual rows=1 loops=144000 · buffers hit=432000 read=0
  after ANALYZE answers:
    Aggregate · est rows=60000 · actual rows=2400 loops=1 · buffers hit=1357 read=0
      -> Hash Join · est rows=144000 · actual rows=144000 loops=1 · buffers hit=1357 read=0
        -> Index Scan using answers_exam on answers · est rows=144000 · actual rows=144000 loops=1 · buffers hit=1032 read=0
        -> Hash · est rows=60000 · actual rows=60000 loops=1 · buffers hit=325 read=0
          -> Seq Scan on candidates · est rows=60000 · actual rows=60000 loops=1 · buffers hit=325 read=0

Plain EXPLAIN's tiny cost says nothing about speed (step 2). The lowest misestimate is the answers scan: 1 estimated, 144,000 actual (steps 8–9). Planned for one row, the Nested Loop reran its candidate lookup 144,000 times: 432,000 buffer accesses (steps 6, 11). After ANALYZE, a Hash Join needs 1,357. The Aggregate's group estimate stays high, but no node sits on it.

4.2 Ticketing: one join, three join nodes

Each plan switches the other join methods off for itself only (SET LOCAL), to show what each node does with loops.

4.2 ticketing: price tiers of one venue's 60 events (4 tiers each), one join, three join nodes
  planner's choice:
    Hash Join · est rows=240 · actual rows=240 loops=1
      -> Seq Scan on tiers · est rows=12000 · actual rows=12000 loops=1
      -> Hash · est rows=60 · actual rows=60 loops=1
        -> Seq Scan on events · est rows=60 · actual rows=60 loops=1
  nested loop and hash join off:
    Merge Join · est rows=240 · actual rows=240 loops=1
      -> Index Scan using tiers_event on tiers · est rows=12000 · actual rows=11829 loops=1
      -> Sort · est rows=60 · actual rows=60 loops=1
        -> Seq Scan on events · est rows=60 · actual rows=60 loops=1
  hash join and merge join off:
    Nested Loop · est rows=240 · actual rows=240 loops=1
      -> Seq Scan on events · est rows=60 · actual rows=60 loops=1
      -> Index Scan using tiers_event on tiers · est rows=4 · actual rows=4 loops=60

Only the Nested Loop reruns a child: 4 rows per loop, 60 loops (steps 5–6). The Hash Join reads each input once. The Merge Join stops reading tiers when the sorted events run out, so its actual rows falls short of the estimate without any misestimate.

Picture it

a man in a green jacket and blue trousers, seen from behind, pulls a pale file box from a wooden archive shelf of teal and white boxes, binders and rolled plans. A pale strip runs along the aisle floor, and a trail of black footprints leads up to him from the lower right. The right third of the canvas is empty.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A refunds table held 15,000 refunds from 100 merchants when it was last analyzed. An import then adds merchant 900's 12,000 refunds; autovacuum is off for the table. Plain EXPLAIN SELECT sum(cents) FROM refunds WHERE merchant_id = 900 estimates 1 row.

Which single statement brings the estimate close to the import?

Pick one

Question 2 of 5

A chat service will purge old messages with DELETE FROM messages WHERE age_days > 90. Before release, the team wants that statement's actual row counts from a staging copy that must keep every message.

What do they run?

Pick one

Question 3 of 5

A reviews report is slow. Its plan, top to bottom: Aggregate (cost=4633.68 rows=1) (actual rows=1 loops=1); under it Hash Join (cost=4631.17 rows=1000) (actual rows=149588 loops=1), whose children are Seq Scan on reviews (cost=3082 rows=200000) (actual rows=200000 loops=1) and Hash (cost=1021 rows=250) (actual rows=37397 loops=1); under the Hash, Seq Scan on products (cost=1021 rows=250) (actual rows=37397 loops=1).

At which node did the planner first go wrong?

Pick one

Question 4 of 5

A tickets table holds 24,000 tickets. Plain EXPLAIN estimates WHERE venue = 'venue-9' at 600 rows and WHERE city = 'city-9' at 600 rows, and each query returns 600. WHERE venue = 'venue-9' AND city = 'city-9' is estimated at 15 rows and returns 600.

Which is the bug?

Pick one

Question 5 of 5

An analyst will compare two rewrites of a plays report by their EXPLAIN (ANALYZE, BUFFERS) output. They will run at different times, and either run may start with a cold cache.

Which number shows which rewrite touches fewer pages?

Pick one

Go deeper

Pro tips
  1. Turn node timing off when you only need counts. EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) still gives rows, loops and buffers, without per-node clock reads. When: comparing two plans (step 12). Verify (RAN, every script here): each plan printed in this lesson was produced with TIMING OFF and repeated identically across 100 runs.
  2. After any bulk import, analyze before the first read. End the import job with ANALYZE <table>; autovacuum may take a while to notice. When: loads, purges and restores (step 9).
  3. On PostgreSQL 18, expect two changes. EXPLAIN ANALYZE includes BUFFERS automatically, and row counts per loop are printed with fractions. Status: DOC (PostgreSQL 18 release notes); every run here is on 17, which prints whole rows per loop.
Source
  • PostgreSQL 17, 14.1 Using EXPLAIN: https://www.postgresql.org/docs/17/using-explain.html · fetched 2026-09-27. §14.1.1: costs "are measured in arbitrary units determined by the planner's cost parameters", conventionally with seq_page_cost at 1.0 (step 2); a nested loop "will run its second, or 'inner' child once for each row obtained from the outer child"; a hash join enters one table's rows "into an in-memory hash table"; merge join "requires its input data to be sorted on the join keys"; the bitmap child "visits an index to find the locations of rows" (steps 4–5). §14.1.2: ANALYZE "actually executes the query"; "the actual time and rows values shown are averages per-execution" (step 6); the BEGIN; EXPLAIN ANALYZE UPDATE …; ROLLBACK pattern (step 3). Valid as of 2026-09-27.
  • PostgreSQL 17, EXPLAIN: https://www.postgresql.org/docs/17/sql-explain.html · fetched 2026-09-27. A hit "means that a read was avoided because the block was found already in cache"; "The number of blocks shown for an upper-level node includes those used by all its child nodes"; BUFFERS "defaults to FALSE" in 17 (step 11). The statement "is actually executed when the ANALYZE option is used" (step 3). TIMING off is useful "when only actual row counts, and not exact times, are needed" (tip 1). Valid as of 2026-09-27.
  • PostgreSQL 17, 14.2 Statistics Used by the Planner: https://www.postgresql.org/docs/17/planner-stats.html · fetched 2026-09-27. §14.2.1: pg_statistic entries "are updated by the ANALYZE and VACUUM ANALYZE commands, and are always approximate even when freshly updated" (steps 7, 9). §14.2.2.1: "The planner normally assumes that multiple conditions are independent of each other"; without a functional dependency it will "assume that the conditions are independent, resulting in underestimating the result size" (step 10). Valid as of 2026-09-27.
  • PostgreSQL 17, pg_buffercache: https://www.postgresql.org/docs/17/pgbuffercache.html · fetched 2026-09-27. pg_buffercache_evict() "is intended for developer testing only". Valid as of 2026-09-27.
  • PostgreSQL 18 release notes: https://www.postgresql.org/docs/18/release-18.html · fetched 2026-09-27. "Automatically include BUFFERS output in EXPLAIN ANALYZE"; "Modify EXPLAIN to output fractional row counts" (tip 3). Not run here. Valid as of 2026-09-27.

Related lessons

Checked against the tools and docs as of 2026-09-27.