EXPLAIN — reading query plans, cost estimation: Visual walkthrough

Step 1 — What a table actually looks like on disk

WHAT. Before any formula, picture the raw thing we are measuring. A table is not "rows floating in space." It is a stack of fixed-size pages (blocks of 8 kilobytes each). Every page holds a handful of rows.

WHY. The whole cost model counts page touches, so we must first agree on what a page is and how many there are. Three numbers will haunt every formula:

PICTURE. Below, one long table = a column of pages; each page is a small box holding a few row-slots. Count the boxes → that is $P$ . Count the slots → that is $T$ . Slots-per-box → that is $rpp$ .

Figure — EXPLAIN — reading query plans, cost estimation

Step 2 — Assigning a price to a single page touch

WHAT. We now put a price tag on the two ways to touch a page.

WHY. The database cannot predict wall-clock time (it depends on your disk, your cache, the weather). So it invents an abstract currency: reading one page in order costs exactly $1.0$ unit, by definition. Everything else is priced relative to that anchor.

Two ways to reach a page, and they cost differently — see Sequential vs Random I/O:

Definition The four planner constants (Postgres defaults)

Symbol	Name	Value	Means
$c_{seq}$	`seq_page_cost`	$1.0$	read one page in order
$c_{rand}$	`random_page_cost`	$4.0$	read one page after a jump
$c_{tuple}$	`cpu_tuple_cost`	$0.01$	hand one row to the machinery
$c_{op}$	`cpu_operator_cost`	$0.0025$	run one filter/comparison test on one row

WHY is random $4 \times$ costlier? A sequential read lets the disk head (or the OS read-ahead) march forward and grab the next physical block for almost free. A random read forces the head to seek to an arbitrary spot first. That seek is the expensive part.

PICTURE. Left: a green arrow gliding straight down neighbouring pages — each hop costs $1.0$ . Right: a coral arrow leaping to scattered pages — each hop costs $4.0$ .

Step 3 — Building the Seq Scan cost, term by term

WHAT. Assemble the cost of a sequential scan: read every page in order, then for every row, hand it to the machinery and run the filter once.

WHY. A sequential scan skips nothing. It cannot know which rows match amount > 500 until it looks, so it must:

read all $P$ pages (in order → price $c_{seq}$ each),
process all $T$ rows (price $c_{tuple}$ each),
test the filter on all $T$ rows (price $c_{op}$ each).

Three unavoidable jobs → three terms:

Because both $P$ and $T$ grow with table size, $C_{seq}$ grows in a straight line — double the table, double the cost.

PICTURE. A horizontal bar split into three coloured segments sized like the real numbers from Example 1 ( $18334$ , $10000$ , $2500$ ). The I/O segment (pages) dominates.

Step 4 — The index route: a tree plus a scavenger hunt

WHAT. Now the rival plan. An index scan does not read the whole table. It walks a B-tree to find where the matching rows live, then jumps to fetch only those rows.

WHY use a tree at all? A B-tree is a sorted signpost. Instead of reading a million rows to find customer_id = 42, you descend a tree of height $\approx lo g_{2} T$ levels — a few dozen comparisons — and it points you straight to the matching rows. That descent answers the question "where are they?" cheaply.

WHY is the tree descent priced with $c_{op}$ and not a page cost? Two reasons. (1) At each of the $\approx lo g_{2} T$ levels the database does a comparison — "is my key left or right of this node?" — which is exactly what $c_{op}$ , the CPU per-operator cost, measures. That is why the descent term is $lo g_{2} (T) \cdot c_{op}$ : one comparison charge per level. (2) The index's own pages do have to be read from disk, but the upper levels of a B-tree are tiny and almost always sitting in memory (cache), so Postgres models their read cost as effectively free and charges only the CPU comparisons. We keep this term because it is honest, but note it is dust ( $lo g_{2} (1 0^{6}) \approx 20$ , so $\approx 20 \times 0.0025 = 0.05$ ).

WHY does fetching $S$ rows cost random reads — and how many pages exactly? The tree tells you the matching rows live on pages ${7, 991, 40232, \dots}$ scattered across the disk. Each fetch is a random jump (Step 2's coral arrow, priced $c_{rand} = 4.0$ ). But here is the subtlety: many rows share a page — $rpp$ of them, from Step 1. So the true number of distinct page fetches is not $S$ but

pages fetched = ⌈ \frac{S}{rpp} ⌉ \times (clustering factor) .

If the matching rows are scattered one-per-page (the pessimistic default the planner often assumes for an unclustered index), then every fetched row lands on its own page and pages-fetched $\approx S$ . That is the worst case, and it is what we use below to keep the algebra clean. When rows cluster (Step 7 case 3) far fewer pages are touched. So the $S \cdot c_{rand}$ term is the upper bound; reality can be cheaper.

PICTURE. A triangle (the B-tree) with a lavender path from root to a leaf, then coral arrows leaping out to $S$ scattered heap pages. Compare to Step 3's "read them all in order."

Step 5 — The showdown: which formula wins?

WHAT. Line the two costs up and cancel the tiny terms.

WHY. The tree-descent term ( $\approx 0.05$ ) and CPU terms are dust compared to the page-reading terms. Strip them and the contest becomes brutally simple (using the unclustered worst case, pages-fetched $\approx S$ ):

seq: all pages \times 1 P \cdot c_{seq} vs. index: S pages \times 4 S \cdot c_{rand}

The index wins exactly when

S \cdot c_{rand} < P \cdot c_{seq} ⟹ S < \frac{c _{seq}}{c _{rand}} P = \frac{1}{4} P

In words: the index beats the scan only when it fetches fewer than a quarter of a table's-worth of pages. Fetch more than that and $4 \times$ -costly random reads lose to cheap sequential reads.

PICTURE. Two straight lines on the same axes: $x = S$ (rows selected), $y =$ cost. The flat lavender line is $C_{seq}$ (constant — it always reads everything). The rising coral line is $C_{idx} = 4 S$ . They cross at the break-even point $S^{⋆} = P /4$ .

Step 6 — Turning "how many rows" into a fraction: selectivity

WHAT. The planner does not know $S$ in advance — it must estimate it before running anything.

WHY. To place your query on Step 5's x-axis, the planner needs $S$ . It gets there through selectivity $s$ , the estimated fraction of rows kept, then $S = s \cdot T$ .

Where does the crossing land on the selectivity axis? Divide the break-even row count $S^{⋆}$ by $T$ to convert it from "rows" to "fraction":

PICTURE. A number line from $0$ to $1$ (selectivity). A tiny lavender tick near $0$ = "customer_id = 42, $s = 2 \times 1 0^{- 5}$ " → index territory. A fat coral tick near $0.8$ = "status = 'active'" → seq-scan territory. The dashed line at $s^{⋆} = 0.25 \cdot P / T$ is the break-even.

Step 7 — Degenerate & edge cases (never let the reader fall off the map)

WHAT. Walk the boundaries where the tidy picture bends.

WHY. A formula you trust must survive its extremes. Five corners:

Definition The edge cases

$S = 0$ (no rows match). Index cost $\approx$ just the tree descent ( $\approx 0.05$ ). Seq scan still reads all $P$ pages. Index wins massively. Empty results are the index's dream.
$S = T$ (everything matches). Index would do up to $T$ random fetches ( $4 T$ ) plus re-descend the tree — strictly worse than a single $P$ -page sweep. Seq scan wins. An index over "give me everything" is pure waste.
Clustered rows (the break-even moves). If the matching rows physically sit together on few pages — a clustered index, e.g. rows sorted by date and you ask for one day — then the fetch cost is $⌈ S / rpp ⌉ c_{rand}$ , not $S c_{rand}$ . With $rpp = 55$ ( $= 1 0^{6} /18334$ ), fetching $S$ clustered rows touches roughly $S /55$ pages, so the index stays cheaper for much larger $S$ . The crossing point slides far to the right. Clustering is why the same $S$ can favour an index on one table and a scan on another.
$c_{rand} = c_{seq}$ (SSD tuning). On flash storage people set random_page_cost = 1.1. The break-even $S^{⋆} = (c_{seq} / c_{rand}) P$ shifts right — the index stays competitive for far larger $S$ . The crossing point is not fixed; it depends on your hardware constants.
Stale statistics. If ANALYZE never ran, $n_{distinct}$ is wrong, so $s$ is wrong, so the planner places you at the wrong x-position and may pick the losing line. This is the #1 real-world failure — see Database Statistics & ANALYZE.

PICTURE. The Step 5 graph redrawn: (a) index line's endpoint at $S = 0$ hugging the axis; (b) index line shooting far above the seq line at $S = T$ ; (c) a gentle clustered coral slope ( $4 S / rpp$ ) whose crossing point slides far right; (d) SSD slope ( $c_{rand} = 1.1$ ) also sliding right.

The one-picture summary

Everything above collapses into a single diagram: the two lines, their crossing at $S^{⋆} = P /4$ , the "index zone" (left, lavender) and "scan zone" (right, coral), with the two worked examples pinned where they fall, and the clustered line showing how the crossing can slide.

Recall Feynman retelling — say it like you'd explain to a friend

A table lives in blocks called pages, each holding $rpp$ rows. The only thing the database really pays for is touching a page. Touching the next page in line is cheap (call it $1$ ); jumping to a random page costs $4$ , because the disk has to seek.

A sequential scan just reads every page in order — its bill is basically "number of pages, times $1$ ." Flat. It doesn't care how many rows you want; it always reads the whole thing.

An index scan is smarter if you want few rows: it walks a little tree — paying one cheap CPU comparison per level, no disk cost because those tree pages live in memory — to find where the rows are, then jumps out to grab them. In the worst case each wanted row sits on its own page, so its bill is "rows wanted, times $4$ ." A rising line. But if the wanted rows cluster on the same pages, it pays for far fewer page jumps ( $S / rpp$ of them) and stays cheap for longer.

Draw both against "how many rows you're grabbing." The flat scan-line and the rising index-line cross at $S^{⋆} \approx P /4$ . Want fewer rows than that? Index wins. Want more? The scan wins, because a million cheap in-order reads beat a hundred-thousand expensive random jumps.

The planner just needs to know where you land on that axis — that's what selectivity estimates, and that's why stale ANALYZE stats (which move you to the wrong spot) are the number-one reason it picks the wrong line.

Recall Quick self-test

Break-even rows for an index vs seq scan (default constants)? ::: About $S^{⋆} = P /4$ — fetch fewer than a quarter of a table's-worth of pages. Why is a random page priced $4 \times$ a sequential one? ::: Sequential reads let the disk march forward for free; random reads force a seek first. With LIMIT 1, which cost matters — startup or total? ::: Startup cost (defined just above) — because you stop after the first row (see LIMIT and Pagination). What does $S = s \cdot T$ compute? ::: The estimated number of selected rows, from selectivity $s$ and total rows $T$ . Why is the tree-descent term charged with $c_{op}$ , not a page cost? ::: It counts one CPU comparison per tree level; the tiny upper index pages sit in memory, so their I/O is modelled as free. If the selected rows cluster on few pages, does the break-even $S^{⋆}$ move left or right? ::: Right — fewer page fetches ( $S / rpp$ ) keep the index cheap for larger $S$ .