SQL / INDEXES AND QUERY PERFORMANCE
Reading a query plan with EXPLAIN
Read a PostgreSQL EXPLAIN plan node by node: follow the tree inside-out, interpret cost, rows, width and loops, and spot where estimates miss reality.
What you will learn
- Read a plan innermost-first: every -> line feeds rows into the line above it
- Read cost=A..B as startup cost then total cost, in arbitrary planner units
- Use EXPLAIN ANALYZE to compare estimated rows against actual rows and loops
- Find the deepest node whose actual rows miss the estimate by 10x or more
Understanding Reading a query plan with EXPLAIN
EXPLAIN does not run your query; it asks the planner to print the plan it would use. What comes back is a tree of executor nodes, written with the root at the top and each child indented under its parent behind a -> arrow. Rows are produced at the leaves and pulled upward, so the first thing that happens is the most indented line and the last thing is line one. Getting that direction right is most of the skill, because a slow plan is usually slow because of a leaf, not because of the node you read first.
Everything in the parentheses is an estimate. cost=0.00..538.00 gives startup cost then total cost: startup is the work done before the node can emit its first row, total is the work to emit all of them. The units are arbitrary, calibrated so that 1.0 is roughly one sequential page read, which means a cost is only meaningful next to another cost in the same plan and never next to a number of milliseconds. rows is how many rows the planner thinks this node emits and width the average bytes per row; rows is the number that drives every choice above it, since a node believed to return 15 rows and one believed to return 15000 deserve completely different treatment.
EXPLAIN ANALYZE executes the statement and adds a second parenthesis per node with measured values, which lets you audit those estimates. actual time=0.031..3.412 is milliseconds to the first row and to the last row, and both are per execution of the node and inclusive of its children, so a parent's time is never smaller than its child's and a repeatedly executed node reports averages you must multiply by loops. When a plan is wrong, find the deepest node where estimated rows and actual rows diverge sharply: everything above it was planned from that bad number, so a strange join order higher up is a symptom rather than the cause.
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL,
total_cents integer NOT NULL
);
INSERT INTO orders (id, customer_id, total_cents)
SELECT g, (g % 2000) + 1, (g % 900) * 100
FROM generate_series(1, 30000) AS g;
ANALYZE orders;
EXPLAIN SELECT id, total_cents FROM orders WHERE customer_id = 42;A plan is a tree of operators that runs from the innermost node outward, and every number beside a node is an estimate whose accuracy explains why the plan has that shape.
Worked examples
Estimated rows against actual rows
EXPLAIN ANALYZE on the same table shows what each node really returned and how much work the filter discarded.
-- same orders table as above
EXPLAIN ANALYZE SELECT count(*) FROM orders WHERE total_cents = 0;Example explained
Line 1rows=33 is the estimate and actual rows=33 the measurement; they agree because total_cents holds 900 evenly spread values and ANALYZE saw the whole table.
Line 2width=0 on the scan means it emits no columns at all: count(*) only needs to know that a row survived the filter.
Line 3Rows Removed by Filter: 29967 shows all 30000 rows were fetched and discarded one by one, which is where the 3.4 ms went.
Line 4The Aggregate's actual time=3.427..3.428 includes its child, so the counting itself added only about 0.015 ms on top of the scan.
Startup cost exposes a blocking node
A three-node plan showing why LIMIT 10 does not make a query cheap when a Sort sits underneath it.
-- same orders table as above
EXPLAIN SELECT id FROM orders ORDER BY total_cents DESC LIMIT 10;Example explained
Line 1Read it inside out: the Seq Scan runs first and hands 30000 rows to the Sort, which hands rows to the Limit.
Line 2The Sort's startup cost of 1111.29 sits far above the scan's total of 463.00, because a Sort cannot emit its first row until it has consumed every input row.
Line 3The Limit inherits that same 1111.29 startup cost and adds almost nothing, which is the plan telling you the whole table is still read and sorted.
Line 4rows=30000 on the Sort next to rows=10 on the Limit shows each node reports its own output, not the query's final row count.
Important notes
EXPLAIN ANALYZE really executes the statement, so wrap INSERT, UPDATE or DELETE in BEGIN ... ROLLBACK unless you want the change to stick.
These outputs come from PostgreSQL 16 with default cost settings; exact costs, row estimates and the extra lines printed vary with your statistics, settings and server version, which is why you compare numbers within one plan rather than across machines.
Common mistakes
Reading the plan from the top down and assuming the first line runs first. The top line finishes last, so people try to speed up the Aggregate when the 3.4 ms actually belongs to the Seq Scan beneath it.
Treating cost as milliseconds. cost=538.00 is in units where 1.0 is roughly one sequential page read, so it cannot be compared with a 3.451 ms execution time, or with a cost measured on another table or server.
Ignoring loops. A node printed as rows=2 ... loops=9000 handed 18000 rows upward and paid its per-loop time 9000 times, so the node that looks free is often the entire runtime.
Try it yourself
Change, predict, then run
Against the orders table above, run EXPLAIN and then EXPLAIN ANALYZE on SELECT customer_id, count(*) FROM orders GROUP BY customer_id HAVING count(*) > 40, and write down for each node its estimated rows, its actual rows, and whether its startup cost shows it had to read its whole input.
Open the SQL workspaceCheck your understanding
An EXPLAIN ANALYZE plan contains a Nested Loop whose inner side reads: Index Scan using orders_customer_id_idx on orders (cost=0.29..8.31 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=48000). What does that node actually cost the query?
- It returned one row and was replanned 48000 times.
- It only took 0.005 ms, so it cannot account for much of the runtime.
- It ran 48000 times, returned about 48000 rows in total, and cost roughly 0.005 ms x 48000, near 240 ms.
- The estimate was badly wrong: it predicted 1 row but 48000 came back.
Show answer
actual time and actual rows are reported per execution and loops says how many executions there were, so both must be multiplied: about 48000 rows and around 240 ms hide behind a node that looks free. Option 2 is the usual trap, since 0.005 ms is the cost of a single loop and not the node's contribution, and option 4 confuses loops with rows: the per-loop estimate of one row was in fact correct.