Query Planning & Optimization — How the Database Picks a Plan
The query planner is the part of a relational database that turns a declarative SQL statement — which says only what rows you want — into one specific, executable strategy for getting them, chosen from many that would all produce the same result. It behaves like a search engine with a cost function: it estimates a price for every candidate strategy from statistics gathered earlier, then runs the cheapest one it finds, which is not the same thing as the fastest one.
Key Components
- Query tree
- The parser's output: a syntax-checked internal form of the statement, with table and column names resolved. No decision about performance exists yet — the parser only confirms the query is well-formed.
- Path
- A lightweight candidate strategy for producing part of the result — for example, "scan
transactionsviaidx_txn_merchant, in merchant order, cost 0.29..842.11." The planner generates and costs many paths per table and per join, before building anything executable. - Plan (plan tree)
- The single, fully expanded, executable tree built from the one path the planner judged cheapest. This — not the path — is what the executor actually walks.
- Cost (startup / total)
- A path's price, in an abstract unit anchored on one sequential page read. It is always a pair — startup cost (before the first row can be returned) and total cost (to return every row) — never a single number, and it does not convert to milliseconds.
- Cardinality estimate
- The planner's predicted row count for a step, read from statistics gathered earlier by
ANALYZE. Every cost figure is arithmetic on top of this one number, so an error here propagates through the whole plan.
Concrete Example
Here is a single line of EXPLAIN output, annotated field by field. Every term from Key Components becomes visible in it at once:
Index Scan using idx_txn_merchant on transactions t
(cost=0.29..842.11 rows=1200 width=64)
^^^^ ^^^^^^ ^^^^ ^^
| | | average row size in bytes
| | ESTIMATED row count <-- the number that matters
| total cost (all rows)
startup cost (before the first row)Index Scan using idx_txn_merchant names the winning path's node type and access method — one candidate among however many the planner priced for this table. cost=0.29..842.11 is the startup/total pair: 0.29 units before the first row streams out, 842.11 units to stream all of them. And rows=1200 is the cardinality estimate that produced both cost numbers — the planner pulled that figure from statistics gathered offline, in pg_class (row and page counts) and pg_stats (per-column value distributions), not from running the query. Whether this plan looks cheap, and whether a join built on top of it looks cheap, is arithmetic on top of that one guess.
Visual Model
Think of the planner as a travel-booking search engine, not a fixed timetable. Given "get me from A to B," it does not look up one official route — it drafts several candidate itineraries (paths), attaches a price to each from a schedule it trusts but did not verify today (statistics), and books whichever itinerary that schedule says is cheapest. If the schedule is stale, the booking can be confidently wrong.
Step through the pipeline a query actually travels, from raw text to rows, and watch where the searching happens.
Loading…
Startup cost and total cost pull in opposite directions, and that tension alone decides plans in surprising ways. A sort or a hash build must finish completely before it emits a single row, so both carry a high startup cost. A nested loop over an index scan streams — near-zero startup. Toggle the query below to see how a LIMIT flips which one wins, using the exact cost=0.29..842.11 path from the Concrete Example above against a second candidate plan.
Same query, two costed candidate plans. Toggling LIMIT 10 changes which number the planner compares first.
Loading…
Deeper — Edge Cases & Gotchas
Where the numbers actually come from
Every cost is roughly rows × per-row-cost + pages × per-page-cost, using five constants, all in the same abstract unit:
| Constant | Default | Meaning |
|---|---|---|
seq_page_cost | 1.0 | cost of a disk page fetched as part of a sequential series |
random_page_cost | 4.0 | cost of a non-sequentially-fetched disk page |
cpu_tuple_cost | 0.01 | cost of processing one row |
cpu_index_tuple_cost | 0.005 | cost of processing one index entry |
cpu_operator_cost | 0.0025 | cost of one operator or function call |
The 4:1 ratio of random_page_cost to seq_page_cost describes a spinning disk. On SSDs that penalty is too high and makes index scans look artificially expensive; lowering random_page_cost toward 1.1 is the standard correction. The row counts these constants multiply against come from pg_class.reltuples/relpages and pg_statistic (readable via pg_stats) — both populated only by ANALYZE and VACUUM, so they are a sampled snapshot, always somewhat out of date, and approximate even the moment after they're refreshed.
The search space explodes — and how the planner survives it
Join orderings alone number N-factorial for N tables, before an algorithm is even chosen for each join. Postgres uses the System R approach (Selinger et al., 1979): build up bottom-up, computing the best plan for every subset of relations of size 1, then 2, then 3, reusing smaller answers — dynamic programming, on the assumption that the best plan for a large set is built from the best plans for its subsets. It also prunes hard: a join between two relations with no matching WHERE/ON clause is considered only when there is no other choice, which is why the planner rarely produces an accidental Cartesian product.
Past geqo_threshold relations (default 12), this near-exhaustive search is abandoned in favor of GEQO, a genetic algorithm. GEQO is randomized — a very wide join can plan differently between runs with identical statistics. If a 15-way join has performance that varies for no visible reason, this is the first thing to check.
Planning itself is not free
For a short, well-indexed query, planning can genuinely cost more than execution. This is what prepared statements address: the first several executions build a custom plan from the real parameter values, then Postgres compares its average cost against a generic plan built without knowing the values, and switches if the generic plan is not more expensive. plan_cache_mode can force either behavior. The trade-off is real: a generic plan skips per-call planning cost but cannot adapt to skewed parameters — a value present in 0.01% of rows and one present in 60% of rows want different plans, and a generic plan serves one of them badly.
What the planner cannot do
- Create an index. If no useful access path exists, the cheapest plan is still a sequential scan.
- See correlation between columns. It assumes independence, so
WHERE city='Mumbai' AND state='Maharashtra'is estimated as the product of the two selectivities — badly underestimating when the columns are correlated.CREATE STATISTICSexists specifically to fix this. - See through opaque expressions.
WHERE lower(email) = 'x'gives it no distribution to read, so it falls back to a hardcoded default guess. An expression index restores both the access path and the statistics. - See through
VOLATILEfunctions. It must re-evaluate them per row rather than folding or caching. - Learn from being wrong. It has no feedback loop — the same stale statistics produce the same mistake, run after run, until
ANALYZEexecutes.
Two failure modes, one diagnostic order
| Bad cardinality estimate | Bad cost model | |
|---|---|---|
| Symptom | EXPLAIN ANALYZE shows a large estimated-vs-actual row gap | estimates are accurate but the plan is still poor |
| Typical cause | stale ANALYZE, correlated columns, opaque expressions | random_page_cost wrong for the hardware, work_mem too small |
| Fix | ANALYZE, CREATE STATISTICS, expression index, raise the statistics target | tune the cost constants or the memory settings |
| Frequency | the overwhelming majority | uncommon |
Check the estimate gap first, every time: when a plan goes wrong, the plan is rarely the bug — trace back to the estimate that produced it.
enable_* switch (enable_seqscan, enable_nestloop, enable_hashjoin, …) permanently in application or database configuration to "fix" a bad plan.
-- DON'T: bake this into a migration or startup config
ALTER DATABASE mydb SET enable_seqscan = off;Why it breaks: these switches add a large fixed cost penalty to discourage a plan type — they do not remove it from consideration, so a sufficiently bad alternative can still produce a sequential scan anyway. And the penalty now biases every query on that connection, including the ones where a sequential scan is genuinely the right choice (small tables, or a query that needs most of the table). They are diagnostics: flip one off in a session, re-run EXPLAIN, and read the planner's second-choice cost to see how close the original decision was. If a plan is wrong, the real fix is almost always a missing index or a stale statistic, not a disabled plan type.
What you actually control
| Lever | What it changes |
|---|---|
| Indexes | which paths exist at all — the largest lever by far |
ANALYZE / autovacuum tuning | accuracy of the estimates driving every decision |
CREATE STATISTICS | fixes the column-independence assumption for correlated columns |
ALTER TABLE ... SET STATISTICS | more histogram detail on one skewed column |
random_page_cost | corrects the cost model for SSDs |
work_mem | whether hash joins and sorts stay in memory |
| Query shape | far less than people think — see the rewrites below |
What the planner already does for you
Before costing anything, Postgres applies view expansion, subquery flattening, predicate pushdown, constant folding, join reordering, non-recursive CTE inlining, and IN-to-semi-join conversion — normalizing most "smarter" query shapes to the same internal form anyway. Time spent hand-reordering joins or rewriting IN as EXISTS for speed is usually time not spent on the index or the statistic that would actually matter.
See Also
SQL Fundamentals — Joins, Aggregations, Subqueries & CTEs SQL's declarative "what, not how" model established there is exactly the gap the planner exists to close. Join Algorithms — Nested Loop, Hash Join & Merge Join The planner's search over paths is the process that chooses among nested loop, hash join, and merge join for every join in the plan. B-tree Indexes — Structure & How Range/Equality Queries Use Them Indexes are the access paths the planner can choose among; without one, the cheapest available path is still a sequential scan. Composite Indexes — Column Order & the Leftmost-Prefix Rule Column order changes which access paths exist at all, directly feeding the set of paths the planner has to cost. Covering Indexes & Index-Only Scans An index-only scan is a lower-cost path type the planner favors whenever the visibility map lets it skip the heap fetch. Buffer Pool / Page Cache — How a DB Manages Memory Theseq_page_cost / random_page_cost split the planner costs with only makes full sense once you know which pages are already resident in shared_buffers.
Window Functions — RANK, ROW_NUMBER, LAG/LEAD, Partitions & Frames
Window functions get their own executor node in the plan tree, added after the planner has settled the rest of the query.
EXPLAIN & EXPLAIN ANALYZE — Reading Query Plans in Practice
The tool that exposes exactly where a planner's cardinality estimate diverged from what actually happened.
Statistics & Cardinality Estimation — the mechanics behind how the sampled data in pg_stats becomes the selectivity fraction this entry treats as a given input — is next in this sequence and not yet published.
Sources consulted
- PostgreSQL Documentation — 52.1. The Path of a Query — cited in source research
- PostgreSQL Documentation — 52.5. Planner/Optimizer — cited in source research
- PostgreSQL Documentation — 14.2. Statistics Used by the Planner — cited in source research
- PostgreSQL Documentation — 61.1. Query Handling as a Complex Optimization Problem (GEQO) — cited in source research
- PostgreSQL Documentation — 20.7. Query Planning (cost constants) — cited in source research
Test Yourself
A plan costs 0.29..842.11. Adding LIMIT 10 to the same query makes the planner switch to a plan with a worse total cost. Why would that ever be the right choice?
True or false: querying a plain (non-materialized) view is inherently slower than querying its base tables directly, because the view stores its own copy of the rows.
A query filters WHERE city = 'Mumbai' AND state = 'Maharashtra'. Almost every row with that city also has that state, but the planner assumes the two columns are independent. What happens to the resulting plan, and why?