How a query actually runs: the planner and its costs

Part of the free LogicWiz SQL & Databases course, Module 14: Indexes and query plans. The queries and practice run in the app on real Postgres.

Same query, same indexes. Yesterday it took 20 ms; after a big import it takes 9 seconds and EXPLAIN shows a Nested Loop where there used to be a Hash Join. Nobody touched the SQL. Who changed the plan, and why?

You'll learn to

Name the four steps every query goes through: parse, rewrite, plan, execute
Read cost numbers and work out a Seq Scan's cost yourself
Explain where row estimates come from (ANALYZE, pg_class, pg_stats)
Spot a plan built on stale statistics and fix it

Learn

Four steps for every query

Parse: check the syntax and turn the text into a tree. A typo fails here.
Rewrite: look up every table and column name, and expand views into the queries behind them.
Plan: the planner (also called the optimizer) lists the ways it could run the query (which scan, which join order, which join algorithm), estimates a cost for each, and keeps the cheapest.
Execute: the executor runs the chosen plan tree; rows flow up from the scans at the bottom.

EXPLAIN stops after step 3 and prints the plan. EXPLAIN ANALYZE also runs step 4.

A cost you can work out by hand

EXPLAIN
SELECT * FROM orders;
SELECT relpages,
       reltuples,
       relpages * 1.0 + reltuples * 0.01 AS seq_scan_cost
FROM pg_class
WHERE relname = 'orders';

The plan says cost=0.00..547.00. The second query rebuilds it: reading each of the 247 pages in order costs 1.0 (seq_page_cost), and handling each of the 30,000 rows costs 0.01 (cpu_tuple_cost). 247 + 300 = 547. Costs are planner units, not milliseconds. A random page read, which an index jump needs, costs 4.0 (random_page_cost), which is why many index jumps can lose to one straight scan.

Where the row estimates come from

The planner can't run the query to count rows, so it guesses from statistics that ANALYZE stores:

pg_class: how many pages (relpages) and rows (reltuples) each table has.
pg_stats: for each column, the number of distinct values (n_distinct), the most common values with their frequencies, and a histogram of the rest.

For status = 'pending' it reads the frequency 0.01 and multiplies: 0.01 × 30,000 = 300 rows. Estimated rows drive everything: scan choice, join order, join algorithm. Postgres runs ANALYZE in the background (autovacuum) after enough changes, but a big import can outrun it.

The statistics behind an estimate

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders'
  AND attname IN ('status', 'city')
ORDER BY attname;
EXPLAIN
SELECT * FROM orders WHERE status = 'pending';

status has 3 values with frequencies 0.97, 0.02 and 0.01. The plan line shows rows=300: exactly 0.01 × 30,000.

Why did it pick that plan?

The planner always picks the plan with the lowest estimated cost. So when it picks a "bad" plan, one of two things is true:

The estimate is wrong. Usually stale statistics: it thinks 1 row matches when 10,000 do, so it picks a nested loop that runs 10,000 times. In EXPLAIN ANALYZE, compare rows= (estimated) with actual rows=; a gap of 10x or more is the clue.
The plan is actually right. A Seq Scan for 97% of the table is cheaper than the index.

Fix the first with ANALYZE. Accept the second.

Stale statistics change the join

-- 10,000 orders were just marked 'returned'; no ANALYZE yet
EXPLAIN (COSTS OFF)
SELECT c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'returned';
ANALYZE orders;
EXPLAIN (COSTS OFF)
SELECT c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'returned';

Before ANALYZE the planner has never seen returned, guesses about 1 row, and picks a Nested Loop (10,000 separate customer lookups in reality). After ANALYZE it knows a third of the table matches and picks a Hash Join. Same SQL, same indexes, better plan.

Peek at the plan it turned down

EXPLAIN
SELECT * FROM orders WHERE status = 'delivered';
SET enable_seqscan = off;
EXPLAIN
SELECT * FROM orders WHERE status = 'delivered';
RESET enable_seqscan;

The Seq Scan costs 622. With Seq Scans switched off, the planner shows its runner-up: an Index Scan on idx_orders_status costing about 950. That is why it skipped the index: it is more expensive here. enable_* settings are a diagnosis tool for your own session, never a production fix.

"Walk me through what happens when I run a query" is a classic. Say the four steps, then spend your time on the planner: it enumerates plans, costs them with statistics from ANALYZE, and keeps the cheapest. Then name the usual reason a plan goes bad: estimated rows far from actual rows, fixed by ANALYZE. Strong answers also mention that costs are in planner units and that random_page_cost is why big index scans lose to sequential scans.

In one line

Every query is parsed, rewritten, planned and executed; the planner keeps the cheapest plan by its cost estimates, which come from ANALYZE statistics, so stale stats mean bad plans.

Interview corner

What happens when you run a SQL query?

The parser checks syntax and builds a tree; the rewriter resolves names and expands views; the planner considers scan methods, join orders and join algorithms, costs each with table statistics, and keeps the cheapest; the executor runs that plan and returns rows. EXPLAIN shows the chosen plan.

Why would the optimizer choose a bad plan?

Almost always because its row estimates are wrong: stale statistics after a big load, correlated columns it treats as independent, or a value it has never seen. Compare estimated and actual rows in EXPLAIN ANALYZE; run ANALYZE, and for correlated columns consider extended statistics (CREATE STATISTICS).

What do the cost numbers in EXPLAIN mean?

Estimated work in planner units, not time: startup cost before the first row, then total cost. They are built from settings like seq_page_cost (1.0), random_page_cost (4.0) and cpu_tuple_cost (0.01) times estimated pages and rows. They are only useful for comparing plans of the same query.