The cost-based planner

How it chooses: cost, not rules

When a “wrong plan” bites you in production, the answer is almost never a new SQL syntax — it’s understanding which number the planner got wrong. The planner takes your analyzed query and enumerates candidate physical plans — different scan methods, different join algorithms, different join orders — then assigns each a numeric cost and picks the lowest. The databases track’s execution-plans chapter derives the cost model and the scan/join taxonomy in depth; here the point is the decision loop the SQL author sees from outside.

Cost is built from estimated row counts (from statistics, lesson 03) multiplied by per-operation constants. The defaults that matter most: seq_page_cost = 1.0 (read one page sequentially), random_page_cost = 4.0 (read one page randomly), cpu_tuple_cost = 0.01 (process one row). An index scan does random page reads; a sequential scan does cheap sequential ones. So the planner’s verdict on “index or seq scan?” is really arithmetic: how many rows match × random cost versus whole table × sequential cost.

Scan choice for WHERE status = 'shipped':

• Few matching rows → Index Scan (cheap to jump straight to them).
• Many matching rows → Seq Scan (random I/O per row would cost more than reading the whole table in order). This is correct, not a bug — a Seq Scan on a non-selective filter beats an index.
• A middling fraction → Bitmap Index Scan (collect matching page pointers, sort them, read pages in physical order — index precision without random thrashing).

Join choice for orders ⋈ line_items:

• Tiny outer side → Nested Loop (for each outer row, probe an index on the inner — great when “few × indexed lookup”).
• Two large unsorted sides → Hash Join (build a hash table on the smaller, stream the larger past it).
• Two already-sorted inputs → Merge Join (walk both in lockstep).

-- Watch the scan flip as selectivity changes:
EXPLAIN SELECT * FROM orders WHERE status = 'cancelled';  -- rare → Index Scan
EXPLAIN SELECT * FROM orders WHERE status = 'placed';     -- common → Seq Scan

Why Postgres has no hints

Oracle and SQL Server let you pin a plan with a hint. Postgres refuses, and the reasoning is durable: a hint is a frozen decision against a moving dataset. The query you hint to “use the index” today is the query that does 50M random reads next year when the table grew and that index stopped being selective. The planner, fed fresh statistics, would have switched to a Seq Scan on its own. A hint forbids that adaptation.

So the senior workflow is to fix the inputs so the planner reaches the right plan itself:

1. Statistics — run ANALYZE, or raise default_statistics_target / add extended statistics (lesson 03). Most “wrong plans” are wrong estimates.
2. Indexes — add or drop an index to change which candidates exist. The planner can only pick from what’s available.
3. Config — on SSD/NVMe, random_page_cost = 4.0 is a spinning-disk default that wildly over-penalizes index scans; lowering it to 1.1 is the single most common production tweak. effective_cache_size tells the planner how much OS cache it can assume.
4. Rewrite the query — sometimes a LATERAL, a EXISTS instead of IN, or splitting a query changes the candidate space for the better.
5. join_collapse_limit — past ~8 joined tables the planner stops exhaustively reordering joins (the search space is factorial) and trusts your written order. Raising it lets it optimize bigger joins at planning-time cost.

Together these inputs — statistics, indexes, cost constants, and query shape — are everything the planner has to work with. Fix any one of them correctly and the planner re-derives a good plan on its own; skip to fixing the output instead, and you win a brittle frozen plan that will hurt you when the data changes.