EXPLAIN — reading query plans, cost estimation

HOW to read a Postgres plan

A typical line looks like:

Seq Scan on orders  (cost=0.00..18334.00 rows=1000000 width=33)
                                                  (actual time=0.01..95.2 rows=1000000 loops=1)
  Filter: (amount > 500)
  Rows Removed by Filter: 200000

Decode it:

Field	Meaning
`Seq Scan on orders`	the operator + its table
`cost=0.00..18334.00`	startup cost `..` total cost (abstract units)
`rows=1000000`	estimated rows this node emits
`width=33`	estimated bytes per row
`actual time=0.01..95.2`	real ms: startup..total (only with ANALYZE)
`loops=1`	how many times the node ran

WHY two costs: startup vs total

This matters for LIMIT! With LIMIT 1, a plan with low startup cost wins even if its total cost is high — you stop after one row.

Deriving the cost formula from first principles

Postgres exposes these planner constants (defaults shown):

Constant	Default	Meaning
`seq_page_cost`	$1.0$	cost to read one page sequentially
`random_page_cost`	$4.0$	cost to read one page randomly
`cpu_tuple_cost`	$0.01$	cost to process one row
`cpu_operator_cost`	$0.0025$	cost to evaluate one operator/filter

Derive the Seq Scan cost. A sequential scan reads every page once and processes every tuple, applying the filter to each:

\text{cost}_{\text{seqscan}} = \underbrace{P \cdot \texttt{seq\_page\_cost}}_{\text{read all pages}} + \underbrace{T \cdot \texttt{cpu\_tuple\_cost}}_{\text{emit each row}} + \underbrace{T \cdot \texttt{cpu\_operator\_cost}}_{\text{run filter once per row}}

where $P$ = number of pages in the table, $T$ = number of tuples (rows).

Now an Index Scan. An index scan follows a B-tree (height $\approx \log$ ) then fetches matching rows from the heap by random page hits:

C_{\text{idx}} \approx \underbrace{\log_2(T)\cdot c_{\text{op}}}_{\text{tree descent}} + \underbrace{S \cdot \texttt{random\_page\_cost}}_{\text{random heap fetches}} + S\cdot c_{\text{tuple}}

where $S$ = number of selected rows.

WHERE row estimates come from (selectivity)

Two key formulas the planner uses:

s_{=}(\text{col} = v) \approx \frac{1}{n_{\text{distinct}}} \qquad s_{<}(\text{col} < v) \approx \frac{v - \min}{\max - \min}\ \text{(from histogram)}

Flashcards

What does plain EXPLAIN show vs EXPLAIN ANALYZE?

EXPLAIN shows estimated cost/rows without running; EXPLAIN ANALYZE actually executes and shows estimated vs actual rows and real time.

In cost=0.00..18334.00, what are the two numbers?

Startup cost (0.00 = work before first row) and total cost (18334 = work to last row).

Why does a Sort node have a high startup cost?

It must consume all input rows before it can emit even the first sorted row.

Write the Seq Scan cost formula.

C = P\cdot c_{seq} + T(c_{tuple} + c_{op})

— pages×seq_page_cost + rows×(cpu_tuple_cost+cpu_operator_cost).

Default seq_page_cost and random_page_cost?

1.0 and 4.0 — random page reads cost 4× sequential.

When does an index scan beat a seq scan?

When selectivity is low (few matching rows

S

), so few random page fetches outweigh reading all

P

pages sequentially.

How is equality selectivity estimated?

s \approx 1/n_{distinct}

, assuming uniform distribution.

Estimated output rows of a node = ?

selectivity × input rows (

s\cdot T

What command refreshes the statistics used for estimates?

ANALYZE (populates pg_statistic with n_distinct, MCVs, histogram).

Which direction do you read a Postgres plan tree?

Bottom-up / inside-out: most-indented leaf nodes execute first and feed their parents.

A node shows rows=10 ... actual rows=900000. What's the diagnosis?

Severe misestimation (stale/insufficient stats); likely cause of a bad plan — re-ANALYZE.

Is cost= comparable across different queries/machines?

No — only between alternative plans for the same query on the same config; it's an abstract unit, not time.

Recall Feynman: explain to a 12-year-old

You ask a giant library for "all books about dragons." The librarian can either walk past every shelf (slow but steady), or use the card catalog to jump straight to the dragon books (fast if there are only a few). EXPLAIN is you peeking at the librarian's plan before they start: "Are you going to walk every shelf? How many books do you think you'll find?" If the librarian guesses "2 books" but there are actually 900,000, they made a dumb plan — and you tell them to re-count what's on the shelves (that's ANALYZE). "Cost" is just the librarian's guess of how tiring the trip will be, not minutes.

Connections

B-Tree Indexes — why index scans cost what they do (tree descent + heap fetch)
Database Statistics & ANALYZE — where selectivity and row estimates come from
Join Algorithms — Nested Loop, Hash, Merge — the inner nodes whose costs EXPLAIN reveals
Selectivity & Cardinality Estimation — the math behind rows=
Query Optimizer — the component that produces the plan EXPLAIN prints
Sequential vs Random I/O — the physical reason random_page_cost > seq_page_cost
LIMIT and Pagination — why startup cost matters when you stop early

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, jab tum SELECT likhte ho, tum database ko sirf batate ho ki tumhe kya chahiye — kaise layega yeh database khud decide karta hai. Yeh decision lene wale part ko query optimizer kehte hain, aur EXPLAIN us optimizer ke dimaag ko padhne ki khidki hai. EXPLAIN sirf plan dikhata hai (query chalata nahi), jabki EXPLAIN ANALYZE query actually chalata hai aur estimated vs actual rows/time dono dikhata hai — debugging ke liye yahi sabse powerful tool hai.

Plan ek tree hota hai. Sabse andar (zyada indent wale) nodes pehle chalte hain aur upar wale parent ko rows dete hain — yaani tree ko neeche se upar padho, code ki tarah upar se neeche nahi. Har line par cost=0.00..18334 likha hota — pehla number startup cost (pehli row aane se pehle ka kaam), doosra total cost (last row tak). Sort ka startup cost high hota kyunki use sab rows pehle chahiye. Yaad rakho: cost ek abstract unit hai, seconds nahi — isliye alag-alag queries ka cost compare mat karo, sirf same query ke alag plans compare karo.

Sabse important intuition: Index Scan tabhi jeetta hai jab matching rows kam ho (low selectivity). Sequential scan poori table ko sequentially padhta hai (har page sasta), jabki index random page fetches karta hai (har page 4× mehnga — random_page_cost=4). Agar filter 80% rows match karta hai, to index lagana suicide hai, aur planner sahi me usse ignore karta hai. Isliye "mera index use kyu nahi ho raha" ka jawab aksar yeh hota hai ki planner smart hai.

Aur ek cheez: estimated rows ka stats se aata hai (ANALYZE se banti). Agar rows=10 likha ho par actual rows=900000 ho, samajh jao stats purani/galat hain — ANALYZE chalao. Yeh estimation error hi 90% bure plans ki jadd hai. To pattern: EXPLAIN ANALYZE chalao, estimated vs actual ka gap dekho, aur sabse mehenge node par focus karo.

EXPLAIN — reading query plans, cost estimation

WHAT is a query plan?

HOW to read a Postgres plan

WHY two costs: startup vs total

Deriving the cost formula from first principles

WHERE row estimates come from (selectivity)

Worked examples

Common mistakes

Flashcards

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases