How a Query Is Processed
A report that took 50 ms yesterday takes 20 seconds today, and nobody changed the SQL. To find out why, you need to know what happens between the moment a query arrives and the moment rows come back, because the slow part is almost never the part you typed.
The rule
Parse, rewrite, plan, execute: the planner decides how, from statistics.
Each query passes through four stages inside its backend process:
- Parser: checks the syntax and turns the text into a tree, then looks up every name in the system catalogue. A typo or a missing table fails here.
- Rewriter: applies rules, most visibly by replacing each view with the query that defines it.
- Planner (optimiser): considers the ways to get the answer — a sequential scan or an index scan, a nested loop, hash or merge join, which table first — estimates the cost of each and picks the cheapest. Its estimates come from statistics gathered by
ANALYZE(autovacuum runs it too). - Executor: runs the chosen plan, pulling rows up through a tree of plan nodes and sending results to the client.
EXPLAIN shows the plan the planner picked; EXPLAIN ANALYZE actually runs the query and adds real timings and row counts.
EXPLAIN ANALYZE
SELECT count(*) FROM rentals WHERE bike_id = 7;The output, trimmed to the interesting parts:
Aggregate (cost=48.1..48.1 rows=1) (actual time=0.21..0.21 rows=1)
-> Index Only Scan using rentals_bike_id_idx on rentals
(cost=0.4..47.6 rows=190) (actual time=0.03..0.18 rows=204)
Index Cond: (bike_id = 7)Read it from the most indented line up: the index scan feeds the aggregate. The estimate (190 rows) is close to the actual 204, so the planner's picture of the data is accurate.
The shortcut
When a plan is slow, compare estimated rows with actual rows on each node. A big gap means the planner was working from a wrong picture, usually stale or missing statistics. Fix the picture before touching the SQL.
Run EXPLAIN ANALYZE
The scan on rentals estimates 12 rows but actually returns 480,000.
The trap is running EXPLAIN ANALYZE on a DELETE or UPDATE to "just look". It really executes. Wrap it in BEGIN; ... ROLLBACK; if you must.
Drills
Answer fast — trust the rule.
A query names a table that does not exist. At which stage does it fail?
Which stage replaces a view with its defining query?
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
A node shows rows=3 estimated and rows=250000 actual. What is the first thing to try?
Remember
- Parse, rewrite, plan, execute: the planner decides how, from statistics.
- Compare estimated rows with actual rows; a big gap means stale statistics.
- EXPLAIN ANALYZE really runs the statement.