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.

Before thisScan types
On this page
  1. A real plan, step by step
  2. Reading one line
  3. Reading order
  4. The red flags
  5. Reading far more rows than are kept
  6. The estimate is far from the truth
  7. A sort that spills to disk
  8. A nested loop with a big outer side
  9. A method that works
  10. Other useful options
  11. Try it yourself
  12. In MySQL
  13. Sources

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.

sql
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;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Step through it below
Limit  (cost=8065.69..8065.71 rows=5 width=40) (actual time=198.737..198.751 rows=5.00 loops=1)
  Buffers: shared hit=2740
  ->  Sort  (cost=8065.69..8065.79 rows=40 width=40) (actual time=198.734..198.744 rows=5.00 loops=1)
        Sort Key: (sum(((oi.qty)::numeric * oi.price))) DESC
        Sort Method: top-N heapsort  Memory: 17kB
        Buffers: shared hit=2740
        ->  HashAggregate  (cost=8064.53..8065.03 rows=40 width=40) (actual time=198.673..198.703 rows=40.00 loops=1)
              Group Key: c.name
              Batches: 1  Memory Usage: 32kB
              Buffers: shared hit=2740
              ->  Hash Join  (cost=2171.91..7944.36 rows=12017 width=18) (actual time=11.610..190.120 rows=12028.00 loops=1)
                    Hash Cond: (p.category_id = c.id)
                    Buffers: shared hit=2740
                    ->  Hash Join  (cost=2170.01..7907.58 rows=12017 width=14) (actual time=11.561..182.854 rows=12028.00 loops=1)
                          Hash Cond: (oi.product_id = p.id)
                          Buffers: shared hit=2739
                          ->  Hash Join  (cost=2110.01..7815.96 rows=12017 width=14) (actual time=10.291..174.196 rows=12028.00 loops=1)
                                Hash Cond: (oi.order_id = o.id)
                                Buffers: shared hit=2724
                                ->  Seq Scan on order_items oi  (cost=0.00..4917.50 rows=300350 width=18) (actual time=0.004..73.075 rows=300350.00 loops=1)
                                      Buffers: shared hit=1914
                                ->  Hash  (cost=2060.00..2060.00 rows=4001 width=4) (actual time=10.237..10.238 rows=4001.00 loops=1)
                                      Buckets: 4096  Batches: 1  Memory Usage: 126kB
                                      Buffers: shared hit=810
                                      ->  Seq Scan on orders o  (cost=0.00..2060.00 rows=4001 width=4) (actual time=7.622..8.886 rows=4001.00 loops=1)
                                            Filter: (created_at >= '2024-12-01 00:00:00+00'::timestamp with time zone)
                                            Rows Removed by Filter: 95999
                                            Buffers: shared hit=810
                          ->  Hash  (cost=35.00..35.00 rows=2000 width=8) (actual time=1.265..1.266 rows=2000.00 loops=1)
                                Buckets: 2048  Batches: 1  Memory Usage: 71kB
                                Buffers: shared hit=15
                                ->  Seq Scan on products p  (cost=0.00..35.00 rows=2000 width=8) (actual time=0.004..0.594 rows=2000.00 loops=1)
                                      Buffers: shared hit=15
                    ->  Hash  (cost=1.40..1.40 rows=40 width=12) (actual time=0.043..0.044 rows=40.00 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 6kB
                          Buffers: shared hit=1
                          ->  Seq Scan on categories c  (cost=0.00..1.40 rows=40 width=12) (actual time=0.012..0.025 rows=40.00 loops=1)
                                Buffers: shared hit=1
Planning:
  Buffers: shared hit=25
Planning Time: 1.118 ms
Execution Time: 198.817 ms

Reading one line#

Every node prints a line like this, followed by details:

text
Seq Scan on orders  (cost=0.00..2060.00 rows=44 width=27) (actual time=0.600..14.100 rows=44.00 loops=1)
PartWhat it means
Seq Scan on ordersThe kind of step, and the table or index it works on.
cost=0.00..2060.00The 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=27The expected average row size in bytes.
actual time=0.600..14.100Measured 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=1How 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:

  • Filter and Rows 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 Cond and Heap 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 an IN list 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/written means the step ran out of memory and used temporary files. Since PostgreSQL 18, EXPLAIN ANALYZE includes buffers without being asked.
  • Sort Method: quicksort in memory, top-N heapsort when a LIMIT lets it keep only the best rows, or external merge Disk: … when the sort spilled to disk.
  • Planning Time and Execution Time at 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#

sql
select id, total from orders where user_id = 42;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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:

sql
select count(*) from orders where total * 1.18 > 2400;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

The 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:

sql
select count(*) from orders where total > 2400 / 1.18;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Other 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#

sql
select order_id, product_id, price from order_items order by price desc, order_id;
Plan, executed real output from PostgreSQL 18.6 (PGlite 0.5.9). Timings depend on the machine; the shape and the row counts do not
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 ms

Sort 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#

  1. Get the real plan: EXPLAIN (ANALYZE, BUFFERS) on realistic data, not an empty development table.
  2. 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.
  3. Compare estimated and actual rows at each node. Fix bad estimates first; a good plan needs good numbers.
  4. Form one hypothesis: "it reads the whole table because there is no index on user_id".
  5. 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.

Exercise

An order count per user

A page shows twenty users with how many orders each has placed. The count is a subquery in the select list, and the page takes seconds.

Your task. Make it at least twenty times cheaper. Either change the schema or the query; both can work.

select u.id, (select count(*) from orders o where o.user_id = u.id) as orders
from users u
where u.id <= 20

This exercise runs a real database in your browser and needs JavaScript.

Exercise

After a bulk import

A nightly job has just imported 30,000 new pending orders. Straight afterwards, a query that joins new pending orders to their users is dramatically slower than usual. Nothing about the schema has changed.

Your task. Get the planner to choose a sensible plan again, without adding an index or changing the query.

select o.id, u.email
from orders o
join users u on u.id = o.user_id
where o.status = 'pending' and o.created_at >= '2025-01-01'

This exercise runs a real database in your browser and needs JavaScript.

Exercise

A ranking that spills to disk

A report ranks products by how many different orders included them. The plan shows temporary files being written: part of the work did not fit in memory.

Your task. Let the work run in memory, for this report only, without changing the query.

select product_id, count(distinct order_id) as orders
from order_items
group by product_id
order by orders desc, product_id
limit 10

This exercise runs a real database in your browser and needs JavaScript.

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#

Esc
↑ ↓ to move↵ to open