Lesson 11 · Design

Query plans

SQL says what to find, never how. Before PostgreSQL runs a query, its planner builds the possible ways to answer it, estimates the cost of each from the table's statistics, and keeps the cheapest. Follow the planner through the PostgreSQL documentation's own example, SELECT * FROM tenk1 WHERE unique1 = 42, read the plan with EXPLAIN and EXPLAIN ANALYZE, and watch the best plan flip when the question asks for 7,000 rows instead of one.

12 min10 steps5 questionsIntermediate

Fig 11 · Interactive

Loading figure…

or ← →

Check yourself

5 quick questions

Pick an answer to see why. Your score saves with your progress.

01What does PostgreSQL's planner do with a SELECT before it runs it?

Running every plan would cost more than the query itself. The planner estimates instead: it guesses the rows from statistics, prices each plan in cost units, and hands the cheapest one to the executor.

02tenk1 has 345 pages and 10,000 rows. With the default settings (seq_page_cost 1.0, cpu_tuple_cost 0.01), what does EXPLAIN SELECT * FROM tenk1 print as the total cost?

345 pages × 1.0 + 10,000 rows × 0.01 = 445. A WHERE condition adds 10,000 × cpu_operator_cost (0.0025) = 25 more, which gives the 470 in the figure.

03In "Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)", what is 0.29?

EXPLAIN prints the estimated start-up cost, then the estimated total cost, both in arbitrary cost units (one page read in order = 1.0). Only EXPLAIN ANALYZE prints real times, in milliseconds.

04Why does PostgreSQL choose a sequential scan for WHERE unique1 < 7000, even though an index on unique1 exists?

An index wins when it lets the executor skip most of the table. When most rows match, the jumps to random pages (random_page_cost 4.0 each) cost more than reading every page in order once.

05Your EXPLAIN ANALYZE shows estimated rows=3 but actual rows=48,000 for a big table you just bulk-loaded. What is a sensible first step?

Every plan choice rests on the row estimate. A big gap between estimated and actual rows usually means missing or stale statistics. ANALYZE refreshes them (autovacuum also does, in the background, but not instantly after a big load).

Key terms

Glossary

Query plan: The step-by-step way the database will answer a query, such as "scan the whole table" or "walk this index, then fetch the rows".
Planner (optimizer): The part of the database that builds the possible plans for a query, estimates their cost and picks the cheapest.
Sequential scan: A plan that reads every page of a table in order and tests every row.
Index scan: A plan that walks an index to the matching entries, then fetches only those rows from the table.
Cost: The planner's estimate of work, in arbitrary units where reading one page in order costs 1.0. It is not a time.
Statistics: What the database knows about a table without reading it all: rows, pages, and how values are spread. ANALYZE collects them.
EXPLAIN: A command that prints the plan PostgreSQL chose for a query, with its cost and row estimates, without running it.
EXPLAIN ANALYZE: EXPLAIN that also runs the query and prints the real row counts (and times) next to each estimate.

Fine print

Notes and sources

The costs are the ones printed in the PostgreSQL 17 documentation for its tenk1 test table, with the default cost settings. Your server may print slightly different numbers: statistics come from samples, and costs depend on the version and platform.
The planner also builds other plans, such as a bitmap scan (it reads the matching index entries first, sorts their page numbers, then visits each page once). For unique1 < 100 the docs show it chosen, between the two extremes here.
The breakdown of 8.30 into index descent, two random page reads and a little CPU work follows PostgreSQL's source code (cost_index and btcostestimate) and is rounded; the docs give only the total.
Which table page holds unique1 = 42 depends on how the rows were loaded; the highlighted page in the figure is illustrative.
EXPLAIN ANALYZE executes the statement for real: for INSERT, UPDATE or DELETE, wrap it in BEGIN ... ROLLBACK if you only want the plan.
SQLite's planner is simpler and prints EXPLAIN QUERY PLAN without costs, but it makes the same kind of choice. The Python example shows it changing plan once ANALYZE has collected statistics.

Quiz Unlocks after the last step.