EXPLAIN — reading query plans, cost estimation: Foundations

This page assumes you have seen nothing. Every word, letter, and symbol the parent note leans on is unpacked below, each one building on the one before it.

0. What is a "row", a "table", and a "page"?

Before any cost or symbol makes sense, picture where the data physically lives.

What this figure shows: three white boxes labelled page 1, 2, 3, each holding five thin magenta horizontal lines. Each thin line is one row (tuple); each box is one page that packs many rows together. Orange arrows lead from one page to the next, and the label reminds you that $P$ counts the pages while $T$ counts all the rows across every page. The picture makes concrete why we track two numbers: rows live inside pages.

1. The two letters $P$ and $T$

The parent note writes formulas using $P$ and $T$ . Here is what they are.

Recall Why does the topic need both

$P$ and $T$ instead of one? Because two different costs are involved ::: reading data is paid per page ( $P$ ), while filtering and emitting is paid per row ( $T$ ) — so both counts appear in the formula.

2. Sequential vs random: the picture behind two page costs

The whole cost model hinges on one physical fact about disks.

What this figure shows: a horizontal strip of eight numbered page-boxes. Along the bottom, one straight magenta arrow sweeps left-to-right through pages 1,2,3,… — that is sequential access, priced $1.0$ per page. Along the top, curved violet arrows hop between pages in the jumbled order 2 → 6 → 1 → 5 — that is random access, priced $4.0$ per page because each jump pays a seek penalty. The orange caption drives home that it is the same 8 KB page either way; only the route to it changes.

3. The planner's four price tags (the constants)

The parent note's formulas multiply things by little numbers like $1.0$ , $0.01$ , $0.0025$ . These are the planner constants — fixed prices the optimizer uses.

Definition The four cost constants

Symbol in formula	Postgres name	Value	What it prices
$c_{seq}$	`seq_page_cost`	$1.0$	reading one page sequentially
$c_{rand}$	`random_page_cost`	$4.0$	reading one page randomly
$c_{tuple}$	`cpu_tuple_cost`	$0.01$	handling one row in the CPU
$c_{op}$	`cpu_operator_cost`	$0.0025$	evaluating one operator/filter once

3b. The recipe: writing an actual cost formula

Now assemble the constants into the formula the parent note uses. Take the simplest operator, a sequential scan (read the whole table, test a one-operator filter on each row):

4. What "cost" means and its two flavours

The plan prints cost=0.00..18334.00. Two numbers, separated by two dots.

What this figure shows: the string cost = 0.00 .. 18334.00 written large. A magenta arrow points at the left number (0.00) labelled startup cost — work before the FIRST row; a violet arrow points at the right number (18334.00) labelled total cost — work to the LAST row. Below, a short timeline runs from a magenta dot ("first row out") to a violet dot ("last row out"), showing that the two cost numbers mark the two ends of the query's output stream.

5. Selectivity — the fraction $s$

The parent uses $s$ , $S$ , and $n_{distinct}$ . Build them now.

6. Where the numbers come from — statistics

6b. The hidden discount — buffer cache

The cost recipe above treats every page read as a real trip to disk. Reality is kinder.

7. The tree shape — leaves, nodes, indentation

Here is a concrete two-node plan — read the indentation as "who feeds whom":

Aggregate  (cost=30840.00..30840.01 rows=1)      <- runs LAST (final output)
  -> Seq Scan on orders  (cost=0.00..30834.00 rows=20)   <- runs FIRST (reads data)
       Filter: (amount > 500)

The indented Seq Scan at the bottom runs first: it reads pages and emits the 20 matching rows upward to its parent. The Aggregate on top runs last: it receives those rows and collapses them into one count. Notice the parent's cost ( $30840$ ) includes the child's cost ( $30834$ ) plus a little more — costs accumulate as rows flow up.

Building blocks used inside the tree draw on B-Tree Indexes (fast lookups) and Join Algorithms — Nested Loop, Hash, Merge (combining tables).

How the foundations feed the topic

A plain-English map of what depends on what (read each arrow as "is needed for"):

Page = disk chunk → gives you $P$ (pages) and $T$ (tuples), the two counts.
Sequential vs random access → justifies the four cost constants ( $c_{seq}, c_{rand}, c_{tuple}, c_{op}$ ).
Counts + constants → let you write the cost formula and split it into startup vs total.
$n_{distinct}$ + uniform-spread assumption → give selectivity $s$ and the row count $S = s T$ .
Statistics from ANALYZE → supply the numbers that feed $s$ .
Cost formula + selectivity + tree of operators → together let you read EXPLAIN output and judge whether the planner chose well.

The same dependencies as a Mermaid diagram (for readers who use one):

Equipment checklist

Test yourself — reveal only after answering.

What is a page, and why is cost counted in pages? ::: A page is a fixed-size (~8 KB) disk chunk holding many rows; disk reads whole pages, so page-reads are the real unit of I/O cost.
What do $P$ and $T$ stand for? ::: $P$ = number of pages in the table; $T$ = number of tuples (rows).
Why is random_page_cost (4.0) bigger than seq_page_cost (1.0)? ::: A random page needs a seek/jump each time, which is about four times slower than streaming the next adjacent page.
Write the sequential-scan total cost formula. ::: $C_{seq} = c_{seq} \cdot P + c_{tuple} \cdot T + c_{op} \cdot k \cdot T$ , with startup $= 0$ .
How many operator evaluations does amount > 500 AND status = 'active' charge per row? ::: Two comparisons (one >, one =), so $k = 2$ in the CPU-operator term.
What are the two numbers in cost=0.00..18334.00? ::: Startup cost (before ..) = work before the first row; total cost (after ..) = work to produce the last row.
Why does a Sort have high startup cost but a Seq Scan near-zero? ::: A sort must read and order all rows before emitting the first; a scan can emit row 1 immediately.
Define selectivity $s$ and the row count $S$ . ::: $s$ is the fraction of rows a predicate keeps ( $0 \leq s \leq 1$ ); $S = s \cdot T$ is the actual number of rows kept.
Why does equality selectivity use $\frac{1}{n _{distinct}}$ ? ::: Assuming values spread uniformly, each distinct value covers about $\frac{1}{n _{distinct}}$ of the rows.
Why can a query with a high printed cost= still run fast? ::: Cost assumes disk reads, but pages already in the buffer cache / OS cache are served from RAM at near-zero I/O cost.
In which direction does a plan tree execute? ::: Bottom-up / inside-out — the most-indented leaves run first and feed their parents; the top line is the final output.