Issue-027 How the Planner Picks a Plan (and Why Cost Isn’t Money)

Bytes & Database'sNov 1, 2026

Issue-027 How the Planner Picks a Plan (and Why Cost Isn’t Money)

Row estimates · cost model · cheapest candidate wins

Nov 1

∙

Preview

After parse and rewrite, the engine still has not touched your heap pages. It must invent candidates—SeqScan, IndexScan, a few join shapes—price each one with a unitless cost model, and keep the cheapest. That pricing step is today’s whole lesson.

Builds on: Issue #026 (parse → analyze → rewrite hands you a tree).
Leads to: Issue #028 (EXPLAIN ANALYZE — estimate vs reality), Issue #034 (how ANALYZE fills the stats the cost model trusts).

Cost is ranking, not currency

Postgres (and cousins) do not guess wall-clock milliseconds in the planner. They accumulate relative costs: sequential page reads, random page reads, CPU per tuple. Change random_page_cost and the winner can flip even though the SQL text did not. Absolute numbers on an EXPLAIN line matter less than whether IndexScan beat SeqScan for this selectivity on this machine profile.
Row estimates come first. Selectivity from pg_statistic (MCVs, n_distinct, null fraction) turns “status = ‘cancelled’” into an expected row count. Cost formulas then multiply pages and CPU by those rows. Bad stats → wrong rows → wrong winner. Always.
Two candidates, one winner. For a simple filter the planner costs a sequential pass over all heap pages and an index dive (index pages + heap fetches). It keeps min(total_cost). Your simulator in this lesson does the same bake-off with HDD defaults (random_page_cost = 4) and an SSD-tuned knob (1.1).

**Diagram 1:** Stats feed the cost model; the model prices candidates; the cheapest plan is what the executor later runs against buffers, indexes, and WAL. The dashed handoff is the rest of the engine you already studied—not a second product.

Rare mechanics worth stealing

SSD tuning is a plan-shape change, not a vanity GUC. On spinning disks, random I/O was expensive enough that many “obvious” indexes lost. On SSD, dropping random_page_cost toward seq_page_cost makes IndexScan win more often. If you copied cloud Postgres defaults meant for SSD onto an old HDD volume (or the reverse), you are debugging hardware assumptions dressed up as SQL.

Correlation quietly shrinks heap fetch cost. Clustered / correlated indexes touch fewer heap pages for a range. Postgres folds that into the index-scan cost. Two indexes with identical keys but different physical order do not cost the same—EXPLAIN will not spell that out in English.

Default selectivity without ANALYZE is a rumor. Missing stats often fall back to crude constants (think “~⅓ of the table”). A PK lookup estimated as a third of a million-row table will happily choose SeqScan. ANALYZE is not chores; it is the planner’s eyes.

enable_seqscan = off is a lab tool, not a production fix. Forcing index use proves the index can be used. The durable fix is selectivity, stats target, or an index that matches the predicate—not a session GUC in the app pool.

Cost units do not compare across major versions or forks. Treat “cost = 12.5” as local ranking. When you A/B plans, compare shape and measured time (EXPLAIN ANALYZE), not raw cost integers copied between environments...

Continue reading this post for free in the Substack app

Claim my free post

Or upgrade your subscription. Upgrade to paid