EXPLAIN — reading query plans, cost estimation: Worked examples

Before we start, let me re-anchor the four symbols so you never have to scroll up:

Throughout, our table is orders with $P = 18, 334$ and $T = 1, 000, 000$ , so $C_{seq} = 18334 + 1 0^{6} (0.0125) = 30834$ stays fixed. Only $S$ moves — and $S$ is what decides everything.

The scenario matrix

Every question this topic can ask is one of these cells. The examples below are labelled with the cell they cover so you can see nothing is skipped.

Cell	Case class	What is special about it	Example
A	Very low selectivity ( $s \approx 0$ )	index crushes seq scan	Ex 1
B	Very high selectivity ( $s \approx 1$ )	seq scan crushes index	Ex 2
C	The break-even point	where the planner flips	Ex 3
D	Degenerate: empty table ( $T = 0$ )	costs collapse, no divide-by-zero traps	Ex 4
E	Degenerate: predicate matches 0 rows ( $S = 0$ )	index is nearly free	Ex 4
F	Startup-vs-total with `LIMIT`	low startup wins even at high total	Ex 5
G	A blocking operator (`Sort`)	startup = total, must read all input	Ex 5
H	A join (nested loop, two tables)	costs multiply, not add	Ex 6
I	Word problem (real dashboard)	translate English → $s$ → decision	Ex 7
J	Exam twist: stale stats	estimate right, reality wrong	Ex 8

Figure — EXPLAIN — reading query plans, cost estimation

The picture above is the spine of the whole page: cost on the y-axis, selectivity $s$ on the x-axis. The flat orange line is the seq scan (it ignores $s$ ). The rising teal line is the index scan (it grows with $S = s T$ ). Where they cross is cell C. Left of the cross → cell A (index wins). Right → cell B (seq wins). Keep glancing back at it.

Cell A — very low selectivity

Worked example Example 1 (Cell A) — needle in a haystack

Query: WHERE customer_id = 42. Stats: customer_id has $n_{distinct} = 50, 000$ evenly spread values.

Forecast: guess now — does the planner pick the index or the seq scan, and by roughly what factor?

Selectivity from equality. For col = v on a uniform column, $s = 1/ n_{distinct} = 1/50000 = 2 \times 1 0^{- 5}$ . Why this step? Equality keeps one value out of $n_{distinct}$ equally-likely values, so the surviving fraction is $1/ n_{distinct}$ . This is the only number that changes between cells — everything downstream follows from it.
Rows selected. $S = s \cdot T = 2 \times 1 0^{- 5} \cdot 1 0^{6} = 20$ . Why this step? $S$ is what the index formula charges for; a filter's job is to shrink $T$ down to $S$ .
Index cost. $C_{idx} = lo g_{2} (1 0^{6}) \cdot 0.0025 + 20 \cdot 4.0 + 20 \cdot 0.01 \approx 0.0498 + 80 + 0.2 \approx 80.25$ . Why this step? Tree descent is tiny (a million rows is only ~20 tree levels), the real bill is 20 random page fetches at $4.0$ each.
Compare. $80.25 ≪ 30834$ , ratio $\approx 384 \times$ cheaper. Planner picks Index Scan.

Verify: sanity — 20 random reads cost $20 \times 4 = 80$ page-cost units; the seq scan reads 18,334 pages. We are reading ~900× fewer pages, and even the 4× randomness penalty cannot undo that. Units are consistent (both in page-cost units). ✓

Cell B — very high selectivity

Worked example Example 2 (Cell B) — almost everyone matches

Query: WHERE status = 'active', where 80% of rows are active.

Forecast: the index exists — will the planner use it?

Selectivity. Here $s$ comes not from $1/ n_{distinct}$ but from the MCV stat, which records that 'active' covers $0.8$ of rows. So $s = 0.8$ . Why this step? For a skewed column, uniform equality would lie badly; ANALYZE's MCV list is exactly the fix. Trust MCV over $1/ n_{distinct}$ when the value is common.
Rows selected. $S = 0.8 \cdot 1 0^{6} = 800, 000$ .
Index cost. $C_{idx} \approx 800000 \cdot 4.0 + 800000 \cdot 0.01 + 0.05 = 3, 200, 000 + 8, 000 + 0.05 = 3, 208, 000.05$ . Why this step? Now the random-fetch term dominates catastrophically — 800k random page hits.
Compare. $3, 208, 000 ≫ 30834$ , about $104 \times$ worse. Planner picks Seq Scan.

Verify: the index cost is dominated by $S \cdot c_{rand}$ ; since $S$ here is $40, 000 \times$ larger than in Ex 1, and $80 \times 40000 = 3.2 M$ , the arithmetic is internally consistent. This is the "my index isn't being used!" trap — it's correct behaviour. ✓

Cell C — the break-even point

We derive where the two lines in the figure cross. Set $C_{idx} = C_{seq}$ and ignore the tiny tree-descent term:

S (c_{rand} + c_{tuple}) = C_{seq} \Rightarrow S = \frac{30834}{4.0 + 0.01} = \frac{30834}{4.01}

We write the break-even value with a star, $S_{⋆}$ , to mean "the special $S$ where the two plans tie."

Worked example Example 3 (Cell C) — where the planner flips

Forecast: roughly how many matching rows is "too many" for the index — a hundred? A thousand? Ten thousand?

Solve for break-even $S_{⋆}$ . $S_{⋆} = 30834/4.01 \approx 7689$ rows. Why this step? Below this the index is cheaper; above it the seq scan is. This single number turns the whole "should I index?" debate into arithmetic.
Turn into selectivity. $s_{⋆} = S_{⋆} / T = 7689/1 0^{6} \approx 0.00769$ , i.e. about $0.77%$ of the table. Why this step? Rules of thumb like "indexes stop helping past ~1% of the table" fall straight out of the cost model — now you know why that number exists.
Interpret the figure. This $s_{⋆}$ is the x-coordinate of the crossing point on figure s01.

Verify: plug $S = 7689$ into both formulas: $C_{idx} \approx 7689 \cdot 4.01 + 0.05 \approx 30833.9$ , and $C_{seq} = 30834$ . They match to the rounding — confirming this is the crossover. ✓

Cells D & E — the degenerate inputs

Worked example Example 4 (Cells D + E) — empty table, and zero-match filter

Forecast: what happens to each formula when there is nothing to read? Does anything blow up?

Case D — empty table, $T = 0$ , so $P = 0$ :

Seq scan: $C_{seq} = 0 \cdot 1.0 + 0 \cdot 0.0125 = 0$ . Why this step? No pages to read, no rows to process — cost floors at (near) zero, no division anywhere, so no divide-by-zero. Postgres actually charges a tiny fixed overhead but the model gives $0$ .
Index scan: $C_{idx} = lo g_{2} (0) \cdot c_{op} + 0 + 0$ . But $lo g_{2} (0)$ is undefined! Why this step? This is the trap the matrix forces us to face. Real planners guard it: with $T = 0$ they treat the tree as height $1$ (a single empty root), so the term is $\approx 0$ , not undefined. Never feed $lo g$ a zero blindly.

Case E — full table but predicate matches nothing, $S = 0$ (e.g. WHERE customer_id = -1): 3. Index scan: $C_{idx} = lo g_{2} (1 0^{6}) \cdot 0.0025 + 0 \cdot 4.0 + 0 \cdot 0.01 \approx 0.05$ . Why this step? The tree descent still happens (you must check the index to learn nothing matches), but there are zero heap fetches. This is the cheapest a real query gets — and it's why an index on a column you filter to "no rows" is nearly free to probe.

Verify: for Case E, $s = 0/1 0^{6} = 0$ , which sits at the far left of figure s01 where the teal line touches its floor (~0.05, the descent cost). Consistent with the plot. ✓

Cells F & G — startup cost, blocking operators, and LIMIT

Now we leave single scans and add a Sort, then a LIMIT. First we must earn two words the parent used but this page has not yet nailed down.

Worked example Example 5 (Cells F + G) —

ORDER BY ... LIMIT 10 Query: SELECT * FROM orders ORDER BY amount DESC LIMIT 10, no index on amount.

Forecast: with LIMIT 10, will a sort of a million rows still cost the same as sorting for LIMIT ALL?

Cost of the sort path (Cell G). Input is a full seq scan, $C_{input} = 30834$ . Add the comparison work: $C_{sort} = 30834 + 1 0^{6} lo g_{2} (1 0^{6}) \cdot 0.0025 \approx 30834 + 1 0^{6} (19.93) (0.0025) \approx 30834 + 49825 = 80659$ . Why this step? Now the number is concrete: sorting a million rows adds ~49,825 units of comparison cost on top of reading them. And because a sort is blocking, both its startup and its total are $80659$ .
Apply LIMIT to the sort (Cell G, no discount). With $C_{startup} = C_{total} = 80659$ , the LIMIT formula gives $C_{limit} = 80659 + \frac{10}{1 0 ^{6}} (80659 - 80659) = 80659$ . LIMIT 10 saves nothing. Why this step? This is the whole point of a blocking node: the limit cannot reach past it to stop the scan early.
The streaming alternative (Cell F). If a B-tree on amount existed (see B-Tree Indexes), it emits rows already in order — startup $\approx 0.05$ , and each of $R = 1 0^{6}$ possible rows adds $c_{rand} + c_{tuple} = 4.01$ , so $C_{total} \approx 4, 010, 000$ . Apply the LIMIT formula with $L = 10$ , $R = 1 0^{6}$ : $C_{limit} = 0.05 + \frac{10}{1 0 ^{6}} (4010000 - 0.05) \approx 0.05 + 40.1 = 40.15$ . Why this step? Cell F in action: even though this plan's total (4 million) is far worse than the sort's 80,659, its tiny startup means LIMIT 10 prices it at ~40 — 2000× cheaper. Low startup wins under a small limit.

Verify: sort path $30834 + 49825 = 80659$ ; streaming path $0.05 + 40.1 = 40.15$ ; ratio $80659/40.15 \approx 2009$ . The blocking node got zero discount, the streaming node got a huge one — exactly the LIMIT lesson. ✓

Cell H — a join

Worked example Example 6 (Cell H) — nested loop join

Outer table customers: $P_{out} = 500$ pages, $T_{out} = 100, 000$ rows, filtered by an equality that keeps $S_{out} = 20$ rows. Inner table orders is probed by an index on customer_id, ~ $4$ matching rows per customer.

Forecast: total cost — closer to $24$ (adding), or closer to a few thousand?

Outer scan cost (derived, no magic number). The outer side is a seq scan of customers: $C_{out} = P_{out} \cdot c_{seq} + T_{out} (c_{tuple} + c_{op}) = 500 \cdot 1.0 + 100000 \cdot 0.0125 = 500 + 1250 = 1750$ . Why this step? The outer table is read exactly once; we use the very same seq-scan formula from the top of the page. No number appears out of thin air.
Cost of one inner probe. $c_{inner} = lo g_{2} (1 0^{6}) \cdot 0.0025 + 4 \cdot 4.0 + 4 \cdot 0.01 \approx 0.05 + 16 + 0.04 = 16.09$ . Why this step? Each probe is a mini index scan for that one customer's orders.
Multiply by outer rows kept. Inner total $= S_{out} \cdot c_{inner} = 20 \cdot 16.09 = 321.8$ . This is what loops=20 means in the plan. Why this step? Cell H's core: the inner side runs once per surviving outer row, so we multiply. Miss this and you'll under-estimate join cost by orders of magnitude.
Combine. Join total $= C_{out} + S_{out} \cdot c_{inner} = 1750 + 321.8 = 2071.8$ . Why this step? Outer is scanned once (add), inner is scanned per-row (multiply) — the plan combines both.

Verify: $500 + 1250 = 1750$ ; $20 \times 16.09 = 321.8$ ; total $2071.8$ . Nested loop is fine here because only 20 outer rows survive. Blow that up to 800k surviving rows and the same multiplication gives $800000 \times 16.09 \approx 12.9 M$ — which is exactly when the optimizer switches to a hash join. ✓

Cell I — the word problem

Worked example Example 7 (Cell I) — the dashboard query

"Our analytics dashboard runs SELECT * FROM orders WHERE region = 'APAC' AND amount > 500 every 5 seconds. region has 4 evenly-used values; amount > 500 keeps ~ $1/8$ of rows. There is a B-tree index on region. Will it be used, and what's the estimated row count?"

Forecast: guess the final estimated row count out of a million.

Selectivity of each predicate. $s_{region} = 1/4 = 0.25$ (equality, 4 uniform values, so $1/ n_{distinct}$ ). $s_{amount} = 1/8 = 0.125$ (range, from the histogram). Why this step? Two predicates, two mechanisms: equality → $1/ n_{distinct}$ , range → histogram fraction.
Combine assuming independence. Combined $s = 0.25 \times 0.125 = 0.03125$ . Why this step? The planner multiplies selectivities when it assumes columns are independent (a known weak spot — see cell J).
Estimated rows. $S = 0.03125 \cdot 1 0^{6} = 31, 250$ .
Index decision. The region index alone gives $S_{region} = 0.25 \cdot 1 0^{6} = 250, 000$ heap fetches → $250000 \cdot 4 = 1, 000, 000$ , far above $30834$ . So the planner seq-scans and filters both conditions. The index on region is not selective enough to help.

Verify: $0.25 \times 0.125 \times 1 0^{6} = 31250$ final rows; but the index's $S$ is the pre-amount count $250, 000$ , which is far above the break-even of $7689$ , so seq scan wins. Both numbers check out and the decision follows from cell C's threshold. ✓

Cell J — the exam twist: stale statistics

Worked example Example 8 (Cell J) — the estimate is right, reality is not

After a bulk load, 90% of orders now have status = 'pending', but ANALYZE has not run since — stale stats still say status has $n_{distinct} = 5$ uniform values.

Forecast: what does EXPLAIN ANALYZE show for estimated vs actual rows on WHERE status = 'pending', and which plan gets picked?

Planner's (stale) estimate. With no MCV skew recorded, it falls back to uniform equality: $s = 1/5 = 0.2 \Rightarrow S_{est} = 0.2 \cdot 1 0^{6} = 200, 000$ . Why this step? Stale stats know only "5 distinct values," so they assume each is $1/5$ of the table. This under-estimates because 'pending' is really 90%.
Reality. True $S_{actual} = 0.9 \cdot 1 0^{6} = 900, 000$ . Why this step? The bulk load skewed the column; only a fresh ANALYZE (see Database Statistics & ANALYZE) would record the 0.9 MCV entry.
The symptom. EXPLAIN ANALYZE prints rows=200000 ... actual rows=900000 — a $900000/200000 = 4.5 \times$ under-estimate. If the plan chose an index at the (wrong) 200k estimate, it now does 900k random fetches and dies. Why this step? The gap between rows= and actual rows= is your smoking gun; that ratio is what you hunt for.
Fix. Run ANALYZE orders; the MCV list records 'pending' → 0.9; the planner re-estimates $S = 900, 000$ and correctly seq-scans. Why this step? Refreshing statistics closes the estimate-vs-reality gap so the cost model can make the right call — this is the concrete lesson of Database Statistics & ANALYZE and Selectivity & Cardinality Estimation.

Verify: estimation error ratio $= 900000/200000 = 4.5$ . The naïve $1/ n_{distinct} = 0.2$ vs the true $0.9$ differ by exactly this $4.5 \times$ — the fingerprint of stale stats on a skewed column. ✓

Recall Self-test — cover the answers

Which cell wins the index and which wins the seq scan? ::: Index for low $s$ (cell A, left of crossover); seq for high $s$ (cell B, right of crossover). What is the break-even selectivity for our orders table? ::: About $0.77%$ , i.e. $S_{⋆} \approx 7689$ rows, from $C_{seq} / (c_{rand} + c_{tuple})$ . Why does LIMIT 10 not speed up a Sort? ::: Sort is a blocking operator — startup ≈ total; with $C_{startup} = C_{total}$ the LIMIT formula gives no discount. In a nested loop, do inner costs add or multiply? ::: Multiply — inner runs once per surviving outer row (loops=). What does rows=200000 ... actual rows=900000 tell you? ::: A 4.5× estimation error, classic stale-statistics symptom; run ANALYZE.