How the database decides to run your query

You write what you want. The database decides how to get it. This lesson is what happens in between, because every performance decision later depends on understanding that the decision is being made at all — and that it can change without your code changing.

The four stages

SQL text
   │
   ├─ 1. Parse       is this valid SQL? do these tables and columns exist?
   ├─ 2. Rewrite     expand views, apply rules
   ├─ 3. Plan        ← the interesting one: choose HOW
   └─ 4. Execute     run the chosen plan

Stage 3 is the planner, and it is why SQL is different from the imperative code you know.

The planner chooses between real alternatives

For SELECT * FROM big WHERE member_id = 42, there are at least three ways:

Sequential scan — read every row, keep the matching ones. Sounds bad; it is the fastest option when a large fraction of rows match, because reading a table front to back is the cheapest thing a disk does.

Index scan — walk the index to find the matching entries, then fetch each row. Fast when few rows match. Each fetch is a random read, which is expensive, so this loses when many rows match.

Bitmap scan — the middle ground. Collect the matching row locations from the index, sort them, then read the table in physical order. Better than an index scan when the number of rows is moderate.

And for a join, there are three more: nested loop, hash join, merge join. The planner considers combinations of all of them.

It picks by estimating the cost of each and choosing the cheapest.

Cost is a made-up unit, and that is fine

EXPLAIN SELECT count(*) FROM big WHERE member_id = 42;

 Aggregate  (cost=14.40..14.41 rows=1 width=8)
   ->  Index Only Scan using idx_big_mt on big  (cost=0.42..13.15 rows=499 width=0)

cost=0.42..13.15 is startup cost..total cost, in arbitrary units anchored to seq_page_cost = 1.0, the notional cost of reading one page sequentially.

The numbers are not milliseconds and do not convert to them. They exist only to be compared with each other. A plan costing 13 was chosen because the alternatives cost more.

The startup cost matters for LIMIT: a plan that is cheap to start but expensive to finish wins when you only want ten rows.

Estimates come from statistics

rows=499 above is an estimate. The actual answer was 527.

That estimate comes from statistics gathered by ANALYZE — how many rows the table has, how many distinct values each column has, the most common values and their frequencies, and a histogram of the distribution.

ANALYZE big;
SELECT attname, n_distinct, most_common_vals FROM pg_stats
WHERE tablename = 'big' AND attname = 'member_id';

autovacuum runs ANALYZE automatically as tables change. Two situations where it has not caught up and you should run it by hand:

Immediately after a bulk load. The statistics describe the old table.
After a big data change, such as a backfill.

Stale statistics are a common cause of a sudden bad plan. If a query was fast yesterday and is slow today with no code change, ANALYZE is the first thing to try.

The consequence: a query can get slow on its own

This is the thing to take from the lesson.

1,000 rows      →  planner: "index scan, obviously"
10,000,000 rows →  planner: "this now matches 40% of the table; sequential scan"

Your code did not change. The data did. A query that has been fine for a year can change plan overnight when a table crosses a threshold, and it will look like a sudden inexplicable outage.

That is not a flaw — the planner is right to adapt — and it is why you need to be able to read a plan rather than assume the one you saw in testing is the one running.

Nudging it, and why to be careful

You cannot pick a plan. You can influence it:

SET enable_seqscan = off;       -- discourage, not forbid
SET enable_indexscan = off;
SET random_page_cost = 1.1;     -- tell it random reads are cheap. True on SSDs.

The enable_* settings are diagnostic tools, not production configuration. Turning off sequential scans to prove the index version is faster is a legitimate experiment; leaving it off in production is how you get a much worse plan somewhere else.

random_page_cost is the one genuinely worth changing. It defaults to 4.0, which reflects a spinning disk where a random read is four times a sequential one. On an SSD the real ratio is close to 1, and 1.1 is the usual recommendation — it makes the planner correctly more willing to use indexes.

Parallel query

 Gather
   Workers Planned: 2
   ->  Parallel Seq Scan on big

PostgreSQL can split a large scan across processes. Workers Planned: 2 plus the leader means three processes reading the table.

It kicks in for large tables and is why a sequential scan is less frightening than it used to be. Note that the row counts in a parallel node are per worker, which is a common misreading of a plan.

Plan caching, and the surprise in it

A prepared statement — which most libraries use for parameterised queries — can have its plan cached. PostgreSQL plans it afresh the first five times, then decides whether a generic plan (one plan for any parameter) is as good as a custom one.

The surprise: a generic plan can be much worse for a skewed parameter. If 99% of your rows have status = 'done' and you query status = ?, the generic plan may assume an average selectivity that is wrong for both cases.

If you see a query that is fast for some parameters and slow for others, this is a candidate. plan_cache_mode = force_custom_plan forces per-parameter planning.

What this means for you

Four practical consequences, which the rest of the module builds on:

You cannot tell whether a query is fast by reading it. You have to look at the plan.

The plan can change without you. Data volume, statistics and parameters all affect it.

Your job is to give the planner good options and good information. The right indexes, current statistics, and queries written so an index can be used.

Measure, do not guess. The next lesson is how.

Check your work

The four stages. Parse, rewrite, plan, execute.

Three ways to find matching rows. Sequential scan, index scan, bitmap scan.

When a sequential scan wins. When a large fraction of rows match.

What cost units are. Arbitrary, anchored to seq_page_cost = 1.0, and only meaningful compared with each other.

What the two numbers in cost=0.42..13.15 are. Startup and total.

When startup cost matters most. With LIMIT.

Where row estimates come from. Statistics gathered by ANALYZE.

Two times to run ANALYZE by hand. After a bulk load, and after a big data change.

The first thing to try when a query got slow with no code change. ANALYZE.

Why a query can get slow on its own. The data crossed a threshold and the plan changed.

What the enable_* settings are for. Diagnosis, not production.

The one setting genuinely worth changing on an SSD. random_page_cost, from 4.0 to about 1.1.

How to read row counts in a parallel node. They are per worker.

When a cached generic plan hurts. When the data is skewed by parameter.

Practice

Run EXPLAIN on a simple query and identify the cost, row estimate and node type.
Run it on a tiny table and a large one and compare the chosen plan.
Compare the estimated rows with the actual, using EXPLAIN ANALYZE.
Load 100,000 rows without running ANALYZE, then EXPLAIN a query. Run ANALYZE and compare.
Look up your table in pg_stats and read n_distinct and most_common_vals.
Force a sequential scan with SET enable_indexscan = off and compare the timings.
Set random_page_cost to 1.1 and see whether any plan changes.
Find a query where a LIMIT changes the chosen plan.
Find a parallel plan and work out the real total row count from the per-worker numbers.
Write a query whose plan you can flip by changing only the constant in the WHERE.
Read SHOW random_page_cost and SHOW seq_page_cost on your server.
Explain to somebody why a query that has been fine for a year might suddenly be slow.

Official documentation

PostgreSQL — Performance tips — The chapter this whole module follows.
PostgreSQL — Planner statistics — What ANALYZE collects and how it is used.
PostgreSQL — Row estimation examples — Worked arithmetic showing how an estimate is produced.
PostgreSQL — Query planning configuration — random_page_cost, the enable_* flags, and plan_cache_mode.
PostgreSQL — Parallel query — When it is used, and how to read the row counts.

Next: reading a plan properly.