Reading EXPLAIN
Read a query plan line by line, spot the red flags, and use the plan to fix a slow query.
Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.
On this page
EXPLAIN shows the plan the database chose for a query. EXPLAIN ANALYZE runs the query and shows what each step really did. Reading that output is the single most useful skill for making a query fast, because it replaces guessing with evidence: where the time went, how many rows each step produced, and how wrong the planner's guesses were.
A real plan, step by step#
Revenue by category for December 2024, the five biggest. Step through it below; each step is one node, in the order the database runs them.
select c.name, sum(oi.qty * oi.price) as revenue
from orders o
join order_items oi on oi.order_id = o.id
join products p on p.id = oi.product_id
join categories c on c.id = p.category_id
where o.created_at >= '2024-12-01'
group by c.name
order by revenue desc
limit 5;Reading one line#
Every node prints a line like this, followed by details:
Seq Scan on orders (cost=0.00..2060.00 rows=44 width=27) (actual time=0.600..14.100 rows=44.00 loops=1)| Part | What it means |
|---|---|
Seq Scan on orders | The kind of step, and the table or index it works on. |
cost=0.00..2060.00 | The planner's estimate, in abstract units: the cost before the first row can be returned, then the cost of returning all rows. Units are relative to reading one page in sequence (1.0). Compare costs between plans for the same query, never with milliseconds. |
rows=44 (first) | How many rows the planner expected this node to produce, per loop. |
width=27 | The expected average row size in bytes. |
actual time=0.600..14.100 | Measured milliseconds: until the first row, and until the last, per loop. |
rows=44.00 (second) | How many rows it really produced, per loop. PostgreSQL 18 prints this with decimals because it is an average over loops. |
loops=1 | How many times the node ran. Inside a nested loop the inner side runs once per outer row, so multiply time and rows by loops. |
Lines underneath add detail. The ones worth knowing:
FilterandRows Removed by Filter: a condition tested after reading each row, and how many rows it discarded. A large number here means rows were read for nothing.Index Cond: a condition the index itself satisfied, without reading rows that fail it. That is what you want to see.Recheck CondandHeap Blocks: in a bitmap scan, the condition re-checked on each fetched row, and how many table pages were read.Heap Fetches: in an index-only scan, how often the table still had to be visited.Index Searches(PostgreSQL 18): how many times the index was descended. More than one means anINlist or a skip scan.Buffers: shared hit=… read=…: pages found already in memory (hit) and pages read in from the operating system (read). Together they are the work done, and unlike time they barely change between runs.temp read/writtenmeans the step ran out of memory and used temporary files. Since PostgreSQL 18,EXPLAIN ANALYZEincludes buffers without being asked.Sort Method:quicksortin memory,top-N heapsortwhen aLIMITlets it keep only the best rows, orexternal merge Disk: …when the sort spilled to disk.Planning TimeandExecution Timeat the bottom: total time to choose the plan and to run it.
EXPLAIN ANALYZE really runs the statement. For an UPDATE or DELETE, wrap it in BEGIN; … ROLLBACK; so nothing is changed.
Reading order#
A plan is a tree printed with the root at the top. Work happens from the inside out: the most indented nodes read the tables and pass rows up through joins, sorts and aggregates to the root. Read it bottom-up, or use the stepper above, which walks the nodes in that order. One detail: a Hash Join first builds its hash table from its second child (the Hash node) and only then reads its first child, so the stepper visits the Hash side first.
The red flags#
Reading far more rows than are kept#
select id, total from orders where user_id = 42;Seq Scan on orders (cost=0.00..2060.00 rows=44 width=10) (actual time=0.260..7.744 rows=44.00 loops=1) Filter: (user_id = 42) Rows Removed by Filter: 99956 Buffers: shared hit=810 Planning Time: 0.091 ms Execution Time: 7.796 ms
Seq Scan with Rows Removed by Filter near the size of the table: nearly all the work was thrown away. The fix is usually an index on the filtered column.
The estimate is far from the truth#
The planner chooses joins, scan types and memory by its row estimates. When an estimate is off by ten times or more, the plan built on it is often bad. A common cause is a condition the statistics cannot see through, such as arithmetic on a column. Orders over 2,400 including 18% tax:
select count(*) from orders where total * 1.18 > 2400;Aggregate (cost=2393.33..2393.34 rows=1 width=8) (actual time=22.269..22.271 rows=1.00 loops=1)
Buffers: shared hit=810
-> Seq Scan on orders (cost=0.00..2310.00 rows=33333 width=0) (actual time=0.407..22.232 rows=61.00 loops=1)
Filter: ((total * 1.18) > '2400'::numeric)
Rows Removed by Filter: 99939
Buffers: shared hit=810
Planning Time: 0.166 ms
Execution Time: 22.301 msThe planner has statistics for total, not for total * 1.18, so it falls back to a fixed guess for the fraction of rows matching. Moving the arithmetic to the other side lets it use the column's histogram:
select count(*) from orders where total > 2400 / 1.18;Aggregate (cost=2060.19..2060.20 rows=1 width=8) (actual time=15.672..15.674 rows=1.00 loops=1)
Buffers: shared hit=810
-> Seq Scan on orders (cost=0.00..2060.00 rows=75 width=0) (actual time=0.224..15.638 rows=61.00 loops=1)
Filter: (total > 2033.8983050847457627)
Rows Removed by Filter: 99939
Buffers: shared hit=810
Planning Time: 0.506 ms
Execution Time: 15.706 msOther causes: statistics older than a big change to the data (run ANALYZE), correlated columns whose selectivities the planner multiplies as if they were independent (see CREATE STATISTICS), and joins whose estimates compound.
A sort that spills to disk#
select order_id, product_id, price from order_items order by price desc, order_id;Sort (cost=37378.27..38129.15 rows=300350 width=14) (actual time=487.004..611.011 rows=300350.00 loops=1)
Sort Key: price DESC, order_id
Sort Method: external merge Disk: 7360kB
Buffers: shared hit=1914, temp read=920 written=923
-> Seq Scan on order_items (cost=0.00..4917.50 rows=300350 width=14) (actual time=0.010..89.582 rows=300350.00 loops=1)
Buffers: shared hit=1914
Planning Time: 0.075 ms
Execution Time: 673.853 msSort Method: external merge Disk: … and temp written mean the sort did not fit in work_mem (4 MB by default), so it wrote temporary files. Options: sort fewer rows (filter first, or add a LIMIT so a top-N sort is enough), return the rows already in order from an index, or raise work_mem for that session if the memory is really available.
A nested loop with a big outer side#
A Nested Loop runs its inner side once per row from its outer side (loops= on the inner node). With an index lookup on the inner side and a few outer rows, it is the fastest join there is. With thousands of outer rows and a scan on the inner side, it multiplies the work. Look at loops and at the inner node's time multiplied by it.
A method that works#
- Get the real plan:
EXPLAIN (ANALYZE, BUFFERS)on realistic data, not an empty development table. - Find where the time and the pages go. Start from the inner nodes and look for the one whose own time, or row count, jumps.
- Compare estimated and actual rows at each node. Fix bad estimates first; a good plan needs good numbers.
- Form one hypothesis: "it reads the whole table because there is no index on
user_id". - Change one thing, then explain again and compare buffers and rows, not only milliseconds.
Practise on the playground, where Explain analyze runs any query against the same data as these examples.
Other useful options#
EXPLAIN alone (without ANALYZE) shows the plan without running the query, which is safe on anything. VERBOSE adds output columns and schema-qualified names. SETTINGS lists planner settings that differ from the defaults. FORMAT JSON gives a machine-readable tree, which is what the stepper on this page uses. PostgreSQL 17 added SERIALIZE (the cost of converting the result for the client) and MEMORY (the planner's memory use).
Try it yourself#
Each exercise runs on the same sample database. Your changes are made inside a transaction and undone after every check, so you cannot break anything.
In MySQL#
Not run here; from the MySQL reference manual. EXPLAIN in MySQL prints one row per table by default, with columns such as type (ALL means a full scan), key, rows and Extra. EXPLAIN FORMAT=TREE prints a tree closer to PostgreSQL's, and EXPLAIN ANALYZE (since 8.0.18) runs the query and adds actual times, rows and loops to each node.
Sources#
- PostgreSQL documentation: Using EXPLAIN (including EXPLAIN ANALYZE and Caveats), EXPLAIN (command reference), Statistics Used by the Planner, Resource Consumption (
work_mem), CREATE STATISTICS; PostgreSQL 17 and 18 release notes. - MySQL reference manual: EXPLAIN Statement, Obtaining Information with EXPLAIN ANALYZE.