EXPLAIN — reading query plans, cost estimation: Exercises

Intuition How to use this page

Each problem below is labelled by cognitive level — from just recognising what a plan line says (L1), up to synthesising a full tuning decision (L5). Try each one with the Solution hidden, then reveal. Everything you need was built in the parent EXPLAIN topic note; where a formula reappears we restate it so you never chase a symbol.

The four planner constants we lean on throughout (Postgres defaults):

$c_{seq} = 1.0$ — cost of reading one page in order (the reference unit).
$c_{rand} = 4.0$ — cost of reading one page at a random location (disk head must jump).
$c_{tuple} = 0.01$ — cost of processing (emitting) one row.
$c_{op} = 0.0025$ — cost of evaluating one filter/operator on one row.

Symbols: $P$ = pages in the table, $T$ = total rows, $S$ = selected rows (rows that survive a predicate), $s$ = selectivity (the fraction $S / T$ , a number between $0$ and $1$ ).

One more symbol we need before L2.2: == $n_{distinct}$ == = the number of distinct (different) values stored in a column. If user_id contains $15, 000$ different ids, then $n_{distinct} = 15, 000$ — even if the table has millions of rows, because the same id repeats many times. The planner reads this number straight from the statistics that ANALYZE collects into pg_statistic.

Figure — EXPLAIN — reading query plans, cost estimation

Figure 1 — alt-text / walkthrough: a dark-navy diagram in two horizontal rows of twelve blue-outlined page boxes. Top row = a Seq Scan: eleven blue left-to-right arrows chain across every page, one after another, labelled "walk ALL pages in order (cheap each, cost 1.0)". Bottom row = an Index Scan: a yellow "index" node in the centre sends three red arrows down to only three red-outlined page boxes at scattered positions (page 2, 6, 10), labelled "jump to a FEW pages randomly (4x costlier each)". The visual contrast — a continuous blue chain versus three isolated red jumps — is the entire trade-off: many cheap sequential reads against few costly random reads.

The figure above is the mental model for everything: a Seq Scan walks every page once, cheaply; an Index Scan jumps to a few pages, expensively each. Whichever total is smaller wins.

Level 1 — Recognition

Worked example L1.1 — Decode a plan line

You run EXPLAIN and Postgres prints:

Seq Scan on users  (cost=0.00..2550.00 rows=40000 width=52)

Name each of the four numbers: what is 0.00, what is 2550.00, what is rows=40000, what is width=52?

Recall Solution

0.00 = startup cost — abstract work before the first row can be emitted. A Seq Scan emits row 1 almost instantly, so this is $\approx 0$ .
2550.00 = total cost — abstract work to emit the last row.
rows=40000 = the planner's estimate of how many rows this node emits (not measured, predicted from statistics).
width=52 = estimated average bytes per row.

Nothing here is measured — EXPLAIN without ANALYZE never runs the query.

Worked example L1.2 — Which node runs first?

Sort  (cost=8000.00..8100.00 rows=40000 width=52)
  Sort Key: users.created_at
  ->  Seq Scan on users  (cost=0.00..2550.00 rows=40000 width=52)

Which of the two operators does its work first, and how can you tell from the indentation?

Recall Solution

The Seq Scan runs first. Execution flows bottom-up / inside-out: the more-indented node is a child that feeds its parent. The Seq Scan reads the 40 000 rows and hands them up to the Sort, which then orders them. The top-most node (Sort) is the last step — it produces the final output.

Level 2 — Application

Recall Solution

Plug in $P = 5000$ , $T = 300000$ : $C_{seq} = 5000 (1.0) + 300000 (0.01 + 0.0025) = 5000 + 300000 (0.0125) = 5000 + 3750 = 8750.$ Why each term? You must touch all $5000$ pages once (I/O term), emit each of the $300000$ rows (cpu_tuple_cost), and test the filter on each row (cpu_operator_cost). Postgres would print cost=0.00..8750.00.

Recall Solution

Recall $n_{distinct}$ (defined at the top of this page) = number of distinct values in the column, here $15, 000$ . $s = \frac{1}{n _{distinct}} = \frac{1}{15000} \approx 6.667 \times 1 0^{- 5} .$ $S = s \cdot T = \frac{300000}{15000} = 20 rows .$ Why $1/ n_{distinct}$ ? Equality selectivity assumes the values are uniformly distributed across the $n_{distinct}$ buckets, so a single value grabs one bucket's share, $1/ n_{distinct}$ . See the Selectivity & Cardinality Estimation note for the non-uniform case.

Level 3 — Analysis

Recall Solution

Where does this formula come from? Build it term by term:

Tree descent — why $lo g_{2} (T) \cdot c_{op}$ ? A B-tree finds a key by comparing at each level and stepping down; the number of levels grows like a logarithm of the number of stored entries, so we make a logarithmic number of comparisons. Each comparison is one operator evaluation, which is exactly what $c_{op}$ prices. That's why we use $c_{op}$ (not $c_{tuple}$ ) here: we're doing key comparisons, not emitting rows.
- Assumption flagged: writing the height as $lo g_{2} (T)$ pretends each index page holds just 2 entries (fanout 2). Real B-trees pack hundreds of entries per page, so the true height is $lo g_{f} (T)$ with fanout $f$ in the hundreds — i.e. only $3$ – $4$ levels for millions of rows. We keep $lo g_{2} (T)$ because it over-counts the descent, and the descent term is tiny either way, so the winner never changes. Just know the real height depends on fanout and entries-per-page, not on $lo g_{2}$ .
Why neglect the index-page I/O? Descending a B-tree touches only a few internal index pages, and the upper levels are tiny and almost always already cached in memory. A handful of cached page reads is negligible next to the $S$ random heap fetches below, so the simplified model drops it. (Postgres's real model adds a small index-I/O term; it never changes the winner in these examples.)
Random heap fetches — $S \cdot c_{rand}$ : for each of the $S$ matching keys we jump to wherever that row physically lives in the table (the heap), a random page hit priced at $c_{rand} = 4.0$ . This uses the page-4 simplifying assumption (one fetch per selected row); on a table clustered on user_id those $S$ rows would sit on consecutive pages and the fetches would be nearly sequential (cost $\approx c_{seq}$ each), making the index even cheaper — but with $S = 20$ the number is already trivially small.
Emit + filter each fetched row — $S (c_{tuple} + c_{op})$ : just like the Seq Scan formula, every fetched row must be emitted ( $c_{tuple}$ ) and re-checked against the predicate ( $c_{op}$ ). Including the $c_{op}$ term here keeps the model consistent with the Seq Scan cost in L2.1 — both charge one operator evaluation per row they process.

Now the numbers:

Tree descent: $lo g_{2} (300000) \approx 18.19$ , so $18.19 \times 0.0025 \approx 0.0455$ .
Random heap fetches: $S \cdot c_{rand} = 20 \times 4.0 = 80$ .
Emit + filter: $S (c_{tuple} + c_{op}) = 20 \times (0.01 + 0.0025) = 20 \times 0.0125 = 0.25$ . $C_{idx} \approx 0.0455 + 80 + 0.25 \approx 80.3.$ Compare: $80.3 ≪ 8750$ , so the planner picks the Index Scan. Why? Reading $20$ pages randomly (at $4 \times$ penalty each) is trivial next to reading all $5000$ pages. The B-tree descent term is negligible here — indexes are shallow. This is the whole point of the figure below.

Figure 2 — alt-text / walkthrough: a cost-vs-workload line chart on dark navy. The x-axis is labelled "S = selected rows" (0 to 4000 rows); the y-axis is labelled "estimated cost (abstract units)" (0 to 18000). A horizontal blue line sits flat at $8750$ — the Seq Scan cost, constant no matter how many rows match. A red line rises from near zero with slope $\approx c_{rand} + c_{tuple} = 4.01$ per selected row — the Index Scan cost. They cross at a yellow dashed vertical marker with a green dot at the break-even $S \approx 2182$ rows (≈0.7% of the table). Left of the marker (green "index WINS" region) the red line is below the blue; right of it (blue "Seq Scan WINS" region) the red line is above. Note: because our model charges one random fetch per row, this red line is an upper bound — a clustered heap would tilt it flatter and push break-even rightward.

Recall Solution

Set the two costs equal (dropping the $\approx 0.05$ descent term, and using the per-row cost $c_{rand} + c_{tuple}$ that dominates — the extra $c_{op}$ per row shifts the answer by under $0.1%$ ): $S \cdot c_{rand} + S \cdot c_{tuple} = 8750 ⟹ S (4.0 + 0.01) = 8750.$ $S = \frac{8750}{4.01} \approx 2182 rows .$ As a selectivity: $s^{⋆} = \frac{S}{T} = \frac{2182}{300000} \approx 0.00727 \approx 0.73%.$ Interpretation: if the predicate keeps fewer than ~0.7% of rows, the index wins; more than that, Seq Scan wins. This threshold assumes one random fetch per row; on a table clustered on the indexed column the fetches turn sequential, so the break-even shifts much higher (an index can pay off even at large $S$ ). This is why an index on a low-cardinality column (few distinct values → large $S$ ) is often useless on an unclustered table.

Level 4 — Synthesis

Worked example L4.1 — The stale-statistics disaster

EXPLAIN ANALYZE on events for WHERE status = 'archived' prints:

Index Scan using events_status_idx on events
  (cost=... rows=20) (actual ... rows=240000 loops=1)

(a) What does the huge gap between rows=20 (estimated) and actual rows=240000 tell you? (b) Compute the actual Index Scan cost implied by $S = 240000$ random fetches and compare it to $C_{seq} = 8750$ . (c) What single command likely fixes this?

Recall Solution

(a) A massive estimation error. The planner thought only $20$ rows matched (so an index looked great), but $240000$ actually match. This means the statistics are stale or wrong — probably 'archived' became a very common value after the stats were last gathered. This is the #1 cause of bad plans.

(b) Actual index cost with $S = 240000$ , using the same per-row formula as L3.1 (random fetch plus emit plus filter, i.e. $c_{rand} + c_{tuple} + c_{op}$ per row — we keep the $c_{op}$ term this time so the model stays consistent with the formula we built): $C_{idx} \approx 240000 (c_{rand} + c_{tuple} + c_{op}) = 240000 (4.0 + 0.01 + 0.0025) = 240000 \times 4.0125 = 963000.$ Assumption flagged: this again charges one random page fetch per row (the page-4 upper bound). On an unclustered events table that is realistic; if the table happened to be clustered on status, the fetches would be sequential and the index would be far less disastrous. We assume unclustered (the common case). Versus $C_{seq} = 8750$ . The chosen index plan is about $\frac{963000}{8750} \approx 110 \times worse$ than a plain Seq Scan would have been — a catastrophe caused purely by the wrong estimate.

(c) ANALYZE events; — refresh the statistics in pg_statistic so the planner sees the true frequency of 'archived' and switches to Seq Scan. See Database Statistics & ANALYZE.

Recall Solution

Plan B wins. With LIMIT 5 you stop after the first $5$ rows, so the optimizer estimates cost proportional to how quickly rows start flowing, not the full total.

Where does the LIMIT heuristic come from? (deriving it) A node's total cost is split into two parts: the startup cost — work that must finish before row 1 appears — and the remaining work, $(total - startup)$ , which is spread evenly across all rows output rows. So the marginal cost of producing one row after startup is $\frac{total - startup}{rows} .$ To emit only $k$ rows you pay the startup once (it cannot be split — the first row literally cannot appear until that work is done), then $k$ times the per-row marginal cost: $C_{limit k} \approx startup + \frac{k}{rows} (total - startup) .$ Why startup cannot be amortized: startup is setup that happens before any output exists (e.g. a Sort must read and order all input before it can hand over even the smallest row). There is no "partial startup" — asking for $5$ rows instead of all rows saves nothing on that fixed block. Only the per-row remainder shrinks with LIMIT.

Now evaluate both plans with $k = 5$ and $rows = 40000$ :

Plan A — its Sort has startup $9000$ , which LIMIT cannot discount: $C_{limit 5}^{A} \approx 9000 + \frac{5}{40000} (9100 - 9000) = 9000 + \frac{5}{40000} (100) = 9000 + 0.0125 \approx 9000.01.$
Plan B — the index emits pre-sorted rows immediately (startup $0.5$ ): $C_{limit 5}^{B} \approx 0.5 + \frac{5}{40000} (8800 - 0.5) = 0.5 + \frac{5}{40000} (8799.5) \approx 0.5 + 1.10 \approx 1.60.$

Comparison: $C_{limit 5}^{B} \approx 1.60 ≪ C_{limit 5}^{A} \approx 9000.01$ . Plan B is roughly $5600 \times$ cheaper for this LIMIT 5, so the optimizer picks Plan B.

Why startup is the deciding number: Plan A's total ( $9100$ ) is only slightly above Plan B's ( $8800$ ) — if you asked for all rows they'd be near-tied. But Plan A's huge fixed startup ( $9000$ ) is charged in full no matter how few rows you want, while Plan B starts emitting almost instantly. LIMIT rewards low startup. See LIMIT and Pagination.

Level 5 — Mastery

Worked example L5.1 — Full tuning decision on a join

You must speed up:

SELECT o.id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Facts: orders has $T_{o} = 2, 000, 000$ rows, $P_{o} = 40, 000$ pages; only $0.5%$ of orders are 'pending'. customers has $200, 000$ rows with a primary-key index on id. (a) Estimate rows surviving the status filter. (b) Given so few pending orders, which access method should feed the join on the orders side, and does that need an index? (c) Which join algorithm fits when one input is tiny and the other has an index on the join key, and why?

Recall Solution

(a) Filtered rows: $S = 0.005 \times 2, 000, 000 = 10, 000 pending orders.$

(b) $S = 10000$ out of $2, 000, 000$ is a selectivity of $0.5%$ — below typical break-even (~0.7% from L3.2), so an Index Scan on a status index (or a partial index on pending orders) beats the Seq Scan for the orders side. Without such an index, Postgres must Seq Scan all $40000$ pages ( $C_{seq} = 40000 + 2 \times 1 0^{6} \times 0.0125 = 40000 + 25000 = 65000$ ) just to find $10000$ rows — wasteful. So yes, an index on status helps here.

(c) A Nested Loop join with an index lookup on the inner side. Outer input = the $10000$ pending orders (tiny). For each one, probe the customers primary-key index on id — a logarithmically-cheap B-tree descent per row plus one random heap fetch: $\approx 10000 \times (lo g_{2} (200000) \times c_{op} + c_{rand}) \approx 10000 \times (17.6 \times 0.0025 + 4) \approx 10000 \times 4.044 \approx 40, 440.$ (Same page-4 assumption: one random fetch per probed customer, unclustered.) This beats a Hash Join that would have to hash all $200000$ customers, because the outer side is small and the inner side is index-covered. If the outer side were large (say millions of pending orders), a Hash Join would win instead — build one hash table, no per-row random probes.

Rule of thumb learned: small filtered outer + indexed inner ⇒ Nested Loop; large unindexed inputs ⇒ Hash Join.

Recall Solution

Selected rows $S = 0.92 \times 1 0^{7} = 9, 200, 000$ . Index Scan cost (dominated by random fetches, one per selected row): $C_{idx} \approx S \times c_{rand} + S \times c_{tuple} = 9.2 \times 1 0^{6} \times 4.0 + 9.2 \times 1 0^{6} \times 0.01 = 36, 800, 000 + 92, 000 = 36, 892, 000.$ Compare to $C_{seq} = 325, 000$ : $\frac{36 , 892 , 000}{325 , 000} \approx 114 \times worse for the index.$ Verdict: the planner is correct. Fetching $9.2$ million rows one random page at a time (each $4 \times$ costlier) is catastrophic versus streaming the whole table sequentially. The index is useless for this predicate because the predicate keeps almost everything — a low-selectivity filter. Caveat (clustering): if this table were physically clustered on is_deleted, the $9.2$ M fetches would be sequential rather than random, shrinking the index cost dramatically — but the default is unclustered, so the planner's Seq Scan choice stands. The Query Optimizer chose wisely.

Recall One-line takeaways

Seq Scan reads all $P$ pages cheaply ::: cost $= P c_{seq} + T (c_{tuple} + c_{op})$ , grows linearly with the table. Index Scan reads $S$ pages randomly ::: cost $\approx lo g_{2} T c_{op} + S (c_{rand} + c_{tuple} + c_{op})$ , wins only when $S$ is small. Why $S$ fetches is pessimistic ::: many matching rows share a page, and a clustered table turns random fetches sequential — both make the real index cost lower than our estimate. Break-even selectivity here ( $c_{rand} = 4$ ) ::: around $0.7%$ of the table (unclustered) — below it index wins, above it Seq Scan wins. The #1 cause of bad plans ::: stale statistics → huge estimate-vs-actual row gap; fix with ANALYZE. LIMIT changes the winner ::: low startup cost beats low total cost, because startup is fixed setup that cannot be split across fewer rows.