Reading EXPLAIN and EXPLAIN ANALYZE in PostgreSQL Basic

Problem

An endpoint responds after 2 seconds. You open the code, read the SQL, and it looks perfectly normal:

SELECT count(*) FROM orders WHERE status = 'refunded';

There is nothing to guess from here. How many rows does the table have? Does PostgreSQL read the whole table or use an index? How much data does it read from disk to return a single number? Looking at the SQL answers none of these.

EXPLAIN prints the plan PostgreSQL intends to use to run the query. EXPLAIN ANALYZE runs the query for real and prints the actual measurements alongside. It is the only way to know for sure what is happening, instead of guessing and adding indexes on a hunch.

This article reads real plans, run on PostgreSQL 17 against a 500,000-row table. Every output below is copied from a real run, not an illustration.

Quick read: warning signs in a plan

What you see in the plan	What it means	What to check next
Large Rows Removed by Filter	Read many rows then discarded nearly all	Is there an index that can serve that condition
actual rows off from rows by several times	The planner misestimated	Statistics: stale statistics, correlated columns
Sort Method: external merge	The sort had to write to a temp file	Reduce the rows to sort, or work_mem
Large loops on an inner node	The node runs many times	Multiply actual time by loops for the real cost
Large Heap Fetches on an Index Only Scan	Still has to read the table	When the last VACUUM ran
Batches greater than 1 on a Hash	The hash table does not fit in work_mem	Input size, work_mem, hash_mem_multiplier
Buffers large relative to rows returned	Reads many pages for few results	Step 3 below

Four steps to read an unfamiliar plan:

Loading diagram…

Core concepts

A plan is a tree of nodes. Each node receives data from its child nodes below, does one thing, and passes the result up to its parent. A node indented further with an -> arrow is a child.

The most important reading rule: read from the inside out, from the bottom up. The most deeply indented node runs first.

Start with the simplest possible plan, looking up one row by primary key:

Index Scan using orders_pkey on orders  (cost=0.42..8.44 rows=1 width=10) (actual time=0.011..0.011 rows=1 loops=1)
  Index Cond: (id = 420000)
  Buffers: local hit=1 read=3
Planning Time: 0.047 ms
Execution Time: 0.020 ms

What each part means:

Part	Value here	Meaning
Node name	Index Scan using orders_pkey	How this node gets data: through the orders_pkey index
cost=0.42..8.44	0.42 and 8.44	Estimated cost: the first number is the cost to return the first row, the second to return all of them
rows=1	1	The number of rows the planner guesses this node will return
width=10	10	Estimated average size of each row, in bytes
actual time=0.011..0.011	0.011 ms	Real time: to the first row, and to the last row
actual ... rows=1	1	The number of rows the node actually returned
loops=1	1	This node ran once
Index Cond	id = 420000	The condition used to locate entries in the index
Buffers: local hit=1 read=3	4 pages	1 page was already in the buffer cache, 3 were not and had to be loaded (see the local note below)
Planning Time	0.047 ms	Time to build the plan, excluding parse/rewrite. For short queries it can rival Execution Time
Execution Time	0.020 ms	Run time, including triggers but excluding planning

The two most important groups of numbers, often confused:

• cost and rows (the first group) are estimates made before running. EXPLAIN prints them without running the query.
• actual time, actual rows, loops, Buffers are measurements from the real run. They appear only with ANALYZE.

Comparing these two groups is the main diagnostic technique, and most of this article revolves around it.

local or shared in Buffers?

Every output in this article runs on TEMP tables, so Buffers shows local. Regular tables show shared, and temp is the sort/hash temp files.

Prefix	Data belongs to
shared	Regular tables and indexes
local	Temporary (TEMP) tables and indexes
temp	Sort, hash, Materialize spilling to temp files

hit is a block already found in the buffer cache. read means it was not in PostgreSQL’s buffer cache; that block may come from the OS page cache, not necessarily from disk. Use track_io_timing = on to see real I/O Timings.

PostgreSQL 18: EXPLAIN ANALYZE prints BUFFERS automatically, and each index scan also reports its number of index searches. On PG17 you must write EXPLAIN (ANALYZE, BUFFERS).

cost is not milliseconds

cost is an internal unit the planner uses to compare alternatives; it does not convert to time. Two real plans from this article prove it:

Query	cost	Real time
Hash join of 500,000 rows	13,885	85 ms
Sort of 500,000 rows spilling to disk	90,193	169 ms

The costs differ by nearly 6.5x but the times by only 2x (two different queries, for illustration only). Use cost to understand what the planner is weighing, and actual time when you need to know how long a query runs.

How it works

When opening an unfamiliar plan, read it in these four steps instead of top to bottom.

Step 1 — Find the node that really takes time

Execution Time at the bottom gives the total. Then look for the node whose actual time ends close to that total and jumps sharply compared with its child nodes. That is where the time goes.

Note: a parent node’s actual time already includes the time of its children. Do not add up the times of every line.

Step 2 — Compare estimated rows with actual rows

This is the real plan of a query filtering on two conditions:

Aggregate  (cost=11177.12..11177.13 rows=1 width=8) (actual time=14.333..14.334 rows=1 loops=1)
  Buffers: local read=3677
  ->  Seq Scan on orders  (cost=0.00..11177.00 rows=49 width=0) (actual time=0.027..14.309 rows=500 loops=1)
        Filter: ((amount < 1.00) AND (status = 'paid'::text))
        Rows Removed by Filter: 499500
        Buffers: local read=3677

The Seq Scan node estimates rows=49 but actually returns rows=500: off by more than 10x.

The cause here: by default PostgreSQL treats the two conditions amount < 1.00 and status = 'paid' as independent and multiplies their selectivities. In this data they are correlated, so the estimate is lower than reality.

For a simple query like this one, a 10x error does no harm. But if this node sits under a join, the planner may pick the wrong join algorithm because it thinks the input is only 49 rows. Bad estimates are a very common root cause of bad plans.

Step 3 — See how much data the node reads versus rows returned

Buffers counts the 8kB pages the node touches. Compare two plans that both return few rows:

-- Filter by status, no usable index
Seq Scan on orders  (... rows=400 ...) (actual time=0.046..11.547 rows=500 loops=1)
      Filter: (status = 'refunded'::text)
      Rows Removed by Filter: 499500
      Buffers: local read=3677

-- Filter by primary key
Index Scan using orders_pkey on orders  (... rows=1 ...) (actual time=0.011..0.011 rows=1 loops=1)
  Index Cond: (id = 420000)
  Buffers: local hit=1 read=3

The first plan reads 3,677 pages (about 29 MB) and throws away 499,500 rows to keep 500. The second reads 4 pages.

That is a gap of nearly 900x, and Rows Removed by Filter: 499500 is the clearest sign: the node reads a lot and then discards almost all of it.

Distinguish Index Cond from Filter:

• Index Cond: a condition used to jump straight to the needed region of the index. No work is spent on non-matching rows.
• Filter: a condition checked after the row has been read. Every discarded row has already cost a read.

A Filter with a large Rows Removed is a signal to consider an index for that condition.

Step 4 — Check loops

Nested Loop  (cost=0.71..831.55 rows=190 width=10) (actual time=0.009..0.058 rows=200 loops=1)
  ->  Index Only Scan using customers_pkey on customers c  (... rows=19 ...) (actual time=0.004..0.005 rows=20 loops=1)
        Index Cond: ((id >= 1) AND (id <= 20))
        Heap Fetches: 0
  ->  Index Scan using orders_customer_id_idx on orders o  (... rows=10 ...) (actual time=0.001..0.002 rows=10 loops=20)
        Index Cond: (customer_id = c.id)

The inner Index Scan node has loops=20: it runs 20 times, once for each row of the node above it.

Importantly: with loops > 1, actual time and actual rows are the average per run, not the total. Here 10 rows × 20 runs = 200 rows, matching rows=200 on the parent Nested Loop. Likewise, 0.002 ms × 20 ≈ 0.04 ms is the real cost.

The number 20 is harmless. But when you see loops=50000, a node that “only takes 0.1 ms” is really costing 5 seconds.

Trap with parallel plans. Under a Gather node, loops equals the number of processes that ran the node: the leader plus the workers (Workers Launched), provided the leader participates (parallel_leader_participation = on, the default).

For example, Parallel Seq Scan ... rows=33333 loops=3 means about 100,000 rows in total, not 33,333.

Workers Launched lower than Workers Planned means worker slots ran out at execution time (limited by max_worker_processes and max_parallel_workers).

Common node types

Node	When you see it	Number to watch
Index Only Scan	The query needs only columns in the index	Heap Fetches (high when VACUUM has not run)
Bitmap Heap Scan	Many matching rows, scattered	Heap Blocks, Rows Removed by Index Recheck
Hash Join	Equality join, one side fits in memory	Batches, Memory Usage
Sort	ORDER BY, merge join	Sort Method: quicksort or external merge

Core lines from real plans from the same script:

Index Only Scan using orders_customer_id_idx on orders  (cost=0.42..6.44 rows=101 width=4) (actual time=0.005..0.008 rows=110 loops=1)
  Heap Fetches: 0

Heap Fetches: 0 confirms there was no trip back to the table. A large number means the name “index only” no longer holds in practice.

Bitmap Heap Scan on orders  (cost=117.24..3918.37 rows=8275 width=10) (actual time=0.286..2.155 rows=9590 loops=1)
  Heap Blocks: exact=1430
  ->  Bitmap Index Scan on orders_created_at_idx  (cost=0.00..115.17 rows=8275 width=0) (actual time=0.196..0.196 rows=9590 loops=1)

The lower node scans the index collecting row positions; the upper node reads the heap in page order so it does not jump back and forth. Heap Blocks: exact=1430 is 1,430 pages to fetch 9,590 rows.

If the bitmap is too large for memory, it switches to lossy and adds a Rows Removed by Index Recheck line.

Hash Join with customers (50,000 rows) as the hash table:

HashAggregate  (cost=13885.61..13885.64 rows=3 width=14) (actual time=85.364..85.365 rows=3 loops=1)
  ->  Hash Join  (cost=1396.00..11385.61 rows=500000 width=6) (actual time=4.192..54.857 rows=500000 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Hash  (cost=771.00..771.00 rows=50000 width=10) (actual time=4.133..4.133 rows=50000 loops=1)
              Buckets: 65536  Batches: 1  Memory Usage: 2661kB

A Hash Join needs one side (the hashed side) to fit in work_mem × hash_mem_multiplier (default 4MB × 2.0). Batches: 1 means it fits. Greater than 1 means it splits into passes and writes temp data to disk. Details in join algorithms.

A sort spilling to disk:

Sort  (cost=90193.92..91443.92 rows=500000 width=10) (actual time=140.183..160.070 rows=500000 loops=1)
  Sort Method: external merge  Disk: 10344kB
  Buffers: local read=3677, temp read=5149 written=5575

Disk: 10344kB is the size of the final temp file. temp read/written counts the 8 kB blocks touched, much larger because external merge writes and re-reads over multiple passes.

The first fix is to reduce the data to sort (filter more, LIMIT, an index already in the right order), and only then work_mem.

Run it yourself

All output in this article comes from one script that creates two temporary tables (500,000 orders, 50,000 customers) and runs eight scenarios in turn:

cd examples
docker compose up -d postgres
docker compose exec -T postgres psql -U hub -d hub < src/postgres-explain/explain-demo.sql

Temporary tables live only in that session, so nothing is left behind after exit. The exact numbers on your machine will differ, but the plan shapes and the warning signs are the same.

Real-world example

Real-world example

An order-history endpoint gets slower month by month. Do not guess on a dev machine:

1. Enable pg_stat_statements (columns total_exec_time, mean_exec_time) to find the most expensive SQL.
2. Enable auto_explain with auto_explain.log_min_duration = '500ms' so plans of slow queries land in the log automatically.
3. Take the real SQL and parameters and run EXPLAIN (ANALYZE, BUFFERS) on a production-sized copy of the data.

Reading the result:

• A Seq Scan with hundreds of thousands in Rows Removed by Filter means a missing index.
• An external merge Sort while the screen shows only 20 rows means sorting the whole set before paging (see keyset pagination).
• For a long plan, paste it into explain.dalibo.com or explain.depesz.com to see the most expensive node.

Trade-offs

Pros

• Shows what PostgreSQL really does, instead of inferring from the SQL
• ANALYZE and BUFFERS let you compare estimates with real measurements from the same run
• Can output FORMAT JSON for automated analysis tools

Cons

• EXPLAIN ANALYZE runs the real query, so it costs exactly the resources that query costs
• Timing depends on cache and machine load; two consecutive runs can differ noticeably
• Plans of large queries run to dozens of lines, making it easy to read line by line instead of finding the suspect node

Common mistakes

Common pitfall

Running EXPLAIN ANALYZE on UPDATE/DELETE/INSERT and forgetting that it really executes. Data changes, locks are taken, triggers run. Wrap it in a transaction and roll back:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'refunded' WHERE id = 420000;
ROLLBACK;

ROLLBACK undoes the data, but it does not recall emails sent by triggers, sequence values already consumed, or the time locks were held. EXPLAIN ANALYZE also adds per-node timing overhead, so it can be slower than a normal run; add TIMING OFF if you only need row counts.

Another mistake: comparing cost with milliseconds, or adding up actual time across every line. A parent node’s time already includes its children; cost is the planner’s own unit.

🎮 Try it

• Slowing query: read real EXPLAIN output on a 30-million-row table.

Checklist for reading a slow query’s plan

1. Run EXPLAIN (ANALYZE, BUFFERS) (PG17 requires BUFFERS explicitly) with the real SQL and parameters, on production-sized data.
2. For UPDATE/DELETE/INSERT: wrap in BEGIN … ROLLBACK, and remember that triggers, sequences, and locks still take effect.
3. Compare estimated rows with actual rows; if off by several times, look at statistics before adding an index.
4. Multiply actual time and actual rows by loops; under Gather, loops includes both leader and workers.
5. Look for a Filter with large Rows Removed, Buffers large relative to rows returned, Sort Method: external merge, Batches greater than 1.
6. Do not compare cost with milliseconds and do not add up actual time across nodes.

Summary

Key takeaways

• A plan is a tree of nodes; read from the most deeply indented node upward, since children run first
• cost/rows are estimates before running; actual time/actual rows/loops/Buffers are measurements from the real run
• Four diagnostic steps: find the expensive node, compare estimate with actual, look at Buffers versus rows returned, check loops
• With loops > 1, actual time and actual rows are per-run averages; multiply by loops for the real cost
• cost is not milliseconds: in this article cost 13,885 ran in 85 ms while cost 90,193 ran in 169 ms
• EXPLAIN ANALYZE executes the statement for real, including UPDATE/DELETE
• Buffers: local in this article comes from TEMP tables; regular tables show shared
• Under Gather, loops equals the number of processes (leader plus workers)
• Find slow queries with pg_stat_statements and auto_explain first, then EXPLAIN

References

• PostgreSQL 17 — Using EXPLAIN
• PostgreSQL 17 — Examining Index Usage