EXPLAIN — reading query plans, cost estimation: Question bank

The vocabulary here is all built in the parent note: a query plan is a tree of operators, cost is in abstract units where "read one sequential page = 1.0", selectivity $s$ is the fraction of rows a filter keeps, and startup cost is the work before the first row appears. If any of those feels shaky, reread the parent first.

True or false — justify

EXPLAIN (without ANALYZE) runs the query.

False. Plain EXPLAIN only asks the optimizer for its plan and estimates; it never executes the query, so it's safe on huge or side-effecting reads. Only EXPLAIN ANALYZE actually runs it.

A higher cost= number always means a slower query.

False. Cost is in abstract units comparable only between plans for the same query on the same machine. Two plans with cost 100 can have wildly different wall-clock times; use EXPLAIN ANALYZE for real milliseconds.

If a table has an index on a column, a query filtering that column will always use it.

False. The planner uses the index only when it's cheaper. For a filter matching most rows (high selectivity), a sequential scan beats thousands of random heap fetches, so the index is correctly ignored.

The startup cost of a Seq Scan is roughly zero.

True. A sequential scan can emit its first row almost immediately after opening the table — it doesn't need to read everything first, so startup

\approx 0.00

The startup cost of a Sort node is roughly zero.

False. A sort must consume its entire input before it can emit even one row (the smallest could arrive last), so its startup cost is high — nearly its total cost.

EXPLAIN ANALYZE on a DELETE or UPDATE is harmless.

False. EXPLAIN ANALYZE genuinely executes the statement, so it will actually delete or update rows. Wrap it in a transaction and roll back if you must analyze a write.

Running ANALYZE changes your query results.

False. ANALYZE only recomputes statistics (distinct counts, histograms) in pg_statistic. It changes which plan the optimizer picks, never the data returned. See Database Statistics & ANALYZE.

random_page_cost = 4.0 means random reads are literally 4× slower than sequential on every machine.

False. It's a tunable default reflecting spinning-disk assumptions. On SSDs random access is much closer to sequential, so lowering it (e.g. to 1.1) is a common, legitimate tuning. See Sequential vs Random I/O.

If estimated rows equals actual rows, the plan is guaranteed optimal.

False. Good cardinality estimates make a good plan likely, but the optimizer could still misjudge costs (e.g. cache effects, correlated columns) and choose a worse join method. Accurate estimates are necessary, not sufficient.

Adding a LIMIT 1 can make the optimizer pick a completely different plan.

True. With LIMIT, a plan with low startup cost wins even if its total cost is huge, because you stop after one row. See LIMIT and Pagination.

Spot the error

"I read the plan top-to-bottom because that's how the query executes."

Error: execution flows bottom-up / inside-out. The most-indented leaf runs first and feeds its parent; the top node is the final step producing output.

"rows=20 in EXPLAIN means the query returned 20 rows."

Error: in plain EXPLAIN, rows= is the optimizer's estimate, not reality. The actual count only appears as actual rows= under EXPLAIN ANALYZE — and the gap between them is the diagnostic gold.

"My filter's estimate says rows=10 but it actually returned 900000 — that's fine, close enough."

Error: a 90,000× estimation gap is the #1 cause of bad plans. It usually means stale statistics or a skewed value the histogram missed; run ANALYZE.

"The index scan reads

S

pages sequentially, so it's cheap."

Error: an index scan fetches matching heap rows by random page access (weighted random_page_cost = 4.0), not sequentially. That randomness penalty is exactly why it only wins when

S

is small.

"cost=0.00..18334.00 — the query took 18334 milliseconds."

Error: those are abstract cost units (startup .. total), not time. Wall-clock ms only appear as actual time= under EXPLAIN ANALYZE.

"loops=1000 on a node — the planner ran the query 1000 times."

Error: loops counts how many times that inner node was re-executed by its parent (typically the inner side of a nested-loop join), not how many times you ran the query. Multiply per-loop cost by loops for the true node cost.

"Selectivity of col = v is always

1/ n_{distinct}

Error: that formula assumes uniform distribution. For skewed columns the planner uses the most-common-values list instead, precisely because the uniform assumption fails. See Selectivity & Cardinality Estimation.

Why questions

Why does the optimizer prefer Seq Scan when a filter matches 80% of rows?

Because fetching 80% of rows via an index means ~

0.8 T

random page reads at 4× cost each, which dwarfs reading all

P

pages once sequentially. The index only wins at low selectivity.

Why report both startup and total cost instead of just one number?

Because different queries care about different things: a LIMIT 1 cares about reaching the first row (startup), while a full scan cares about the last row (total). One number can't capture both.

Why is EXPLAIN ANALYZE the gold standard for debugging over plain EXPLAIN?

It shows estimated vs actual rows and time side by side. The mismatch pinpoints where the optimizer was wrong — you can't see that error without the real numbers.

Why does the planner descend a B-tree in about

lo g

operations rather than scanning it?

A B-tree is height-balanced, so finding a key touches only one node per level — roughly

lo g

levels — instead of every entry. That cheap descent is what makes index lookups fast for a single key.

Why does stale statistics cause a "phantom" bad plan even though the SQL is unchanged?

The optimizer's cost depends on estimated row counts from pg_statistic. If data grew or skewed since the last ANALYZE, those estimates lie, and the optimizer confidently picks a plan that's now wrong. See Database Statistics & ANALYZE.

Why can't the optimizer just measure real time in advance?

Real time depends on hardware, cache warmth, and concurrent load — none knowable at planning time. So it counts abstract weighted operations (pages, tuples, operators) as a stable, hardware-independent proxy.

Edge cases

What happens to the row estimate when a WHERE filter matches zero rows but stats are uniform?

The formula

s \cdot T

never yields exactly 0 for a plausible value; Postgres clamps estimates to at least 1 row, so you'll see rows=1 even when reality is 0 — a mild, expected inaccuracy.

An empty table (

T = 0

P = 0

): what's the Seq Scan cost?

Every term (

P c_{seq}

T c_{tuple}

T c_{op}

) is zero, so the cost is essentially just the tiny fixed overhead — the scan is trivially cheap because there's nothing to read.

Selectivity of a predicate that matches every row (

s = 1

): does an index ever help?

No —

S = T

means the index would do

T

random fetches plus a tree descent, strictly worse than one sequential pass. At

s = 1

the sequential scan always wins.

WHERE col = v where

v

is one dominant value in 90% of rows, with fresh stats: which estimate is used?

The most-common-values list, not

1/ n_{distinct}

. With fresh stats the planner sees

v

's true frequency (~0.9) and correctly estimates ~

0.9 T

rows, avoiding a disastrous index choice.

LIMIT 1 on a filter that matches nothing: does low startup cost still win?

The optimizer plans as if it might find a row quickly, so it may still choose the low-startup plan — but at runtime it scans everything and finds none. The estimate assumed a match existed; the empty case is the worst outcome for that gamble.

A query with cost 100 and another with cost 50 for different queries: is the second faster?

You can't tell. Costs are only comparable between competing plans for the same query on the same machine; across different queries the numbers are meaningless side by side.