Backend
Reading PostgreSQL EXPLAIN ANALYZE Like a Pro
July 20269 min read
Photo by Heptagon via Wikimedia Commons (Public domain)
EXPLAIN shows the planner's chosen plan with estimated costs and row counts without running the query. EXPLAIN ANALYZE actually executes the query and reports real timings, actual row counts, and loop counts. Use EXPLAIN ANALYZE to see what really happened, but remember it runs the statement, so wrap writes in a transaction you can roll back.
BUFFERS shows how many data blocks each node read from cache (shared hit) versus fetched from disk (shared read). That distinction tells you whether a slow node is CPU-bound or disk-bound, which have completely different fixes. From PostgreSQL 18 BUFFERS is included by default; on version 17 and earlier you must add it explicitly.
No. If a query needs most of a table, a sequential scan is often faster than random index lookups. A sequential scan only signals a problem when Rows Removed by Filter is much larger than the rows returned, meaning PostgreSQL read many rows just to discard them. That is the case where adding an index on the filtered column helps.
Compare estimated rows to actual rows on the join and its children. If the planner expected a few rows and got thousands, it may have chosen a Nested Loop that now runs thousands of times, shown by a high loops counter. A gap beyond roughly ten times usually means stale statistics, so run ANALYZE on the tables and re-check the plan.
Walk the tree and find the node with the largest self time, which is its actual time minus its children's time, not the highest cumulative time. On that node check the estimate-versus-actual row gap, Rows Removed by Filter, and buffer counts. Visualizers like explain.dalibo.com highlight the slowest node automatically when the text output is dense.
Photo by Heptagon via Wikimedia Commons (Public domain)
Key Takeaway
Read a PostgreSQL EXPLAIN ANALYZE plan inside-out: the most-indented node runs first. For each node compare estimated rows to actual rows, watch for large Rows Removed by Filter, and use BUFFERS to tell a slow disk from a slow plan. The real bottleneck is the node with the biggest actual time and buffer count, not the query total.
Every engineer eventually meets a query that was fine in staging and crawls in production. My first move is never to add a random index or throw more RAM at the box. It is to run EXPLAIN ANALYZE and actually read the output. The plan tells you exactly what PostgreSQL did, how long each step took, and where the time went. Most people glance at the total time at the bottom and guess. This post is about reading the tree properly.
I run PostgreSQL 16 on the VPS behind a service I maintain, and I have been testing PostgreSQL 18 since it shipped in September 2025. There is one change worth knowing up front: from PostgreSQL 18, EXPLAIN ANALYZE includes BUFFERS by default. On 17 and earlier you still have to type it. That single flag is the difference between guessing and knowing whether you are looking at a slow plan or a slow disk.
EXPLAIN alone shows the planner's estimates. EXPLAIN ANALYZE actually executes the query and reports real timings and row counts. Add BUFFERS and you also get shared block hits (data found in cache) versus reads (data fetched from disk). This is the number that separates a genuinely expensive plan from one that is only slow because the data was cold. Here is the invocation I use every time.
-- Works on all supported versions; on PG18 BUFFERS is already implied.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '30 days'
ORDER BY o.total DESC
LIMIT 50;EXPLAIN ANALYZE actually runs the statement. For a plain SELECT that is fine, but for INSERT, UPDATE, or DELETE it will modify data. Wrap it in a transaction and ROLLBACK, or run it against a copy. I have seen someone EXPLAIN ANALYZE a DELETE on production and wonder why rows vanished.
A plan is a tree of nodes, and it does not execute top to bottom. The most deeply indented node runs first, feeds its rows up to its parent, and so on to the root at the top. So you read the indentation like a stack: find the innermost operations, understand what feeds what, then follow the rows upward. Each node line carries the estimate in the first parentheses and the reality in the second.
Limit (cost=812.44..812.56 rows=50 width=48) (actual time=6.112..6.128 rows=50 loops=1)
-> Sort (cost=812.44..842.19 rows=11900 width=48) (actual time=6.110..6.118 rows=50 loops=1)
Sort Key: o.total DESC
Sort Method: top-N heapsort Memory: 32kB
-> Hash Join (cost=38.00..416.62 rows=11900 width=48) (actual time=0.401..4.980 rows=11842 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.00..347.00 rows=11900 width=20) (actual time=0.012..2.140 rows=11842 loops=1)
Filter: (created_at >= (now() - '30 days'::interval))
Rows Removed by Filter: 8158
-> Hash (cost=23.00..23.00 rows=1200 width=36) (actual time=0.377..0.378 rows=1200 loops=1)
-> Seq Scan on customers c (cost=0.00..23.00 rows=1200 width=36) (actual time=0.006..0.180 rows=1200 loops=1)
Planning Time: 0.210 ms
Execution Time: 6.180 msReading that plan inside-out: PostgreSQL sequentially scans customers into a hash table, sequentially scans orders applying the date filter, joins them by hashing, sorts the result to get the top 50 by total, and returns them. The cost values are arbitrary planner units, useful only for comparing nodes in the same plan. The actual time values are milliseconds and cumulative, so a parent's time includes its children.
A Seq Scan reads every row in the table. That is not automatically bad. If a query needs most of the table, a sequential read is often faster than jumping around an index. The red flag is a Seq Scan with a large Rows Removed by Filter, like the 8158 above. That means PostgreSQL read a pile of rows just to throw them away, which is exactly what an index would prevent. An Index Scan walks the B-tree to matching entries and fetches only those rows.
After adding an index, run EXPLAIN ANALYZE again and confirm the plan actually switched to an Index Scan. The planner only uses an index if its statistics say it is worth it. If nothing changed, run ANALYZE on the table to refresh statistics, or check that your WHERE clause matches the indexed expression exactly.
PostgreSQL picks a join strategy based on how many rows it expects from each side. A Nested Loop takes each row from the outer side and probes the inner side, which is superb when the outer side is tiny and the inner side has an index, and catastrophic when both sides are large. A Hash Join builds a hash table from one side and probes it with the other, which is the right call for large unsorted joins. The table below is the mental model I carry.
| Aspect | Nested Loop | Hash Join |
|---|---|---|
| Best when | Outer side small, inner side indexed | Both sides large and unsorted |
| Cost pattern | Grows with outer rows times inner lookups | Roughly linear, one pass per side |
| Memory use | Minimal | Needs work_mem for the hash table |
| Danger sign | High loops count on a big inner scan | Batches greater than 1 (spilled to disk) |
| Typical fix | Add index on the join column | Raise work_mem or reduce row width |
The nastiest case I keep seeing is a Nested Loop the planner chose because it expected two rows from the outer side but got twenty thousand. Look at the loops counter on the inner node: if it says loops=20000, that node ran twenty thousand times. That is almost always a stale-statistics problem, and it is why the estimated-versus-actual gap matters more than any single number.
Do not fixate on Execution Time at the bottom. Walk the tree and find the single node with the largest self time, which is its actual time minus the time of its children. Then look at three things on that node: the ratio of estimated to actual rows, the Rows Removed by Filter, and the buffer counts. A node with millions of shared read blocks is hitting disk; a node with millions of shared hit blocks is CPU-bound in cache. Those two problems have completely different fixes.
When the text output gets dense, paste the plan into a visualizer like explain.dalibo.com or explain.depesz.com. They highlight the slowest node and the worst estimate-versus-actual rows for you, so you spend your attention on the fix instead of counting indentation.
None of this requires memorizing every node type. Run EXPLAIN ANALYZE with BUFFERS, read the tree from the inside out, compare what the planner expected against what happened, and follow the self time to the one node that actually hurts. Ninety percent of the slow queries I have fixed came down to a missing index or stale statistics, and the plan told me exactly which one every time.