A slow query arrives. Someone runs EXPLAIN ANALYZE, pastes the output into a search engine, and a verdict comes back: “add an index.” Sometimes that is right. More often it treats the symptom while the real problem — a planner misestimate, a memory spill, a bad join order — stays in place and bites the next query instead. Reading a query plan is a diagnostic skill, and like most diagnostic skills it gets a lot easier once you know which lines actually matter.
This post is a field guide to EXPLAIN (ANALYZE, BUFFERS): how to read a plan tree, which numbers tell the truth, which ones mislead, and the recurring patterns that explain the majority of slow queries in production systems.
The Setup: Always Three Options
Plain EXPLAIN shows the plan without running anything — useful only for the cheapest sanity check. The trio you almost always want is:
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT ...;ANALYZE actually executes the query and annotates every node with real row counts and timings. BUFFERS reports the I/O each node performed — shared pages hit in cache versus read from disk — and in recent PostgreSQL versions is implied by ANALYZE anyway. SETTINGS appends any non-default planning parameters, which explains half of the “works on my machine” plan mismatches.
Two cautions before you run it. ANALYZE really executes: an INSERT/UPDATE/DELETE will commit its changes, so wrap destructive statements in a transaction and roll back. And a plan that runs fast under EXPLAIN may run slow in production, because timing accumulates per node with per-tuple overhead, and because warm caches flatter the plan — a node showing read=0 might read hundreds of pages the first time a real client runs the query.
Reading the Tree
A plan is a tree of nodes. Data flows bottom-up, execution is pipelined, and indentation encodes the parent-child relationship. A representative plan:
Sort (cost=717.34..717.59 rows=101 width=488)
(actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
Buffers: shared hit=64
-> Hash Join (cost=229.20..713.98 rows=101 width=488)
(actual time=0.719..7.549 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
Buffers: shared hit=64
-> Seq Scan on t2 (cost=0.00..445.00 rows=10000 width=244)
(actual time=0.012..3.200 rows=10000 loops=1)
Buffers: shared hit=44
-> Hash (cost=229.20..229.20 rows=101 width=244)
(actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on t1 (cost=4.36..39.38 rows=10 width=244)
(actual time=0.049..0.526 rows=100 loops=1)
Buffers: shared hit=3 read=5
-> Bitmap Index Scan on t1_unique1
(actual time=0.021..0.021 rows=100 loops=1)
Index Cond: (unique1 < 100)Read cost=0.00..445.00 as relative units, not milliseconds: the first number is startup cost (when the node emits its first row) and the second is the total. A large startup cost signals blocking operations — sorts, hashes — that must consume their input before producing anything. width is the estimated average row width, which is how the planner reasons about memory.
The actual line only appears with ANALYZE. Its most misunderstood value is time=7.761..7.774: the first figure is time to first row, the second is time to completion — and both are per-loop averages. Multiply by loops before comparing a node against its parent. A nested-loop inner scan showing time=0.020..0.020 rows=1 loops=10000 cost 200ms of real time, not 0.02ms. Miss this and you will optimize a node that contributes almost nothing.
Misestimates: The Root Cause Hiding in Plain Sight
Compare rows (estimate) against actual rows on every node. When they agree within an order of magnitude, the planner understands your data. When they diverge, everything downstream of the misestimated node inherits the error — and the visible symptom is usually a join strategy or memory decision that looks insane.
The classic cascade: a subquery is estimated at 1 row but actually returns 50,000. Based on “1 row,” the planner picks a nested loop against a large table. Nested loops shine when the outer side is small; at 50,000 iterations the same plan is quadratic disaster. The index was never the problem — the estimate was.
Common causes, in rough order of frequency:
- Stale statistics. The planner assumes uniform distribution within the min/max range it sampled. After a bulk insert, or when a table’s hot range moves (recent dates, recent IDs), estimates drift badly.
ANALYZE table;is both the diagnosis and the fix. - Cross-column correlation. Statistics are per-column by default. A query filtering
WHERE city = 'X' AND zip = 'Y'multiplies two independent selectivities even though the columns are tightly correlated, producing estimates that are orders of magnitude off. Extended statistics (CREATE STATISTICS ... ON city, zip FROM t;) fix this properly. - Correlated predicates in joins compound the error: join selectivity is itself an estimate, and errors multiply across joins.
- Function-wrapped predicates.
WHERE upper(email) = 'X'has no statistics for the expression unless you create an expression index, in which case you get both the stats and the fast path.
A 10x misestimate is noise. A 1000x misestimate changes the plan shape, and the fix is statistics, not indexes.
Buffers: The Only Honest I/O Numbers
Timings on a shared machine are noisy and environment-dependent. Buffer counts are not. Buffers: shared hit=44 read=5 means 44 pages found in the buffer pool and 5 fetched from the OS. Every read is a potential disk trip; every hit is a memory access. When comparing before/after performance, the sum of read across nodes is a far more stable metric than wall-clock time.
Patterns worth knowing:
- Large
readon a Seq Scan is often fine — reading a whole table sequentially is cheap per page. Largereadon a random index scan is the pattern that hurts, because each page fetch is a separate trip. - A bitmap heap scan with many
readand a lossy bitmap (watch forHeap Blocks: lossy=N) means the in-memory bitmap ran out ofwork_memand degraded to page-granularity lossy mode, forcing rechecks of every row on those pages. - Temp buffers or “temp read” appearing under a sort or hash node means it spilled to disk. This is a
work_memproblem, and it is one of the most common silent killers in production — a sort estimated at 10MB that actually needs 400MB serializes on temporary files.
The Hash node reports its own memory line: Batches: 1 Memory Usage: 28kB. Batches above 1 means the hash table exceeded work_mem and was partitioned to disk, multiplying I/O. It is the single most diagnostic line for hash joins that suddenly regress.
Join Strategies and Why the Planner Picks Each
PostgreSQL has three join algorithms, and the choice is driven almost entirely by row estimates:
- Nested Loop — for each outer row, probe the inner side. Fast when the outer side is small and the inner probe is an index lookup. Catastrophic when the outer estimate is wrong in the direction of “small.”
- Hash Join — build an in-memory hash of the smaller side, probe with the larger. The workhorse for large-to-large joins, but it needs
work_memto stay in one batch and it blocks until the build side is consumed. - Merge Join — both inputs sorted on the join key, then merged. Useful when indexes already provide the order, but sensitive to misestimates about whether a sort will be needed.
When a plan looks wrong, ask which estimate drove the join choice. Fixing the estimate fixes the choice; forcing the join with planner GUCs is a band-aid that leaks into every other query on the system.
A Practical Triage Sequence
Given a slow query, this order catches most real problems:
- 1. Find the expensive node. Take
actual timexloopsat every node, walk up the tree, and identify where time concentrates. Usually one node owns the majority. - 2. Check the estimate vs. actual at that node and its ancestors. Off by orders of magnitude? Run
ANALYZEon the involved tables, then look at extended statistics if columns are correlated. - 3. Check buffers at the expensive node. High
readon random-access patterns, or any temp file activity, points atwork_memand cache behavior rather than plan shape. - 4. Look for Rescan. A
loopsvalue far above 1 on a subtree with a scan inside means repeated execution; the inner scan’s per-iteration cost is what you need to fix, usually with an index that matches the join condition. - 5. Only then consider an index. If the expensive node is an honest sequential scan over a large table with a selective filter, an index is probably right. If the plan shape is driven by a misestimate, the index will be ignored or make things worse.
Plan Changes Without Query Changes
One subtlety worth internalizing: plans are not properties of queries. They are properties of queries plus statistics plus settings plus data distribution. A deployment that only changes data volume can flip a plan from an index scan to a sequential scan and a 50ms query into a 5s one. This is why generic plan caching (with prepared statements) can backfire — the cached plan was optimal for the data as it was. PostgreSQL’s plancache switches to generic plans after a few executions precisely when custom plans stop winning, but the mechanism is statistical, not omniscient.
For anything you depend on, capture the plan with EXPLAIN (ANALYZE, BUFFERS) before and after schema changes, index additions, or major version upgrades. Plan drift is far easier to prevent than to diagnose at 3am.
Wrapping Up
Most slow PostgreSQL queries are not missing-index problems. They are estimate problems, memory-spill problems, or join-order problems — and all three are visible in EXPLAIN (ANALYZE, BUFFERS) once you know where to look: actual-vs-estimated rows, per-loop timing, and buffer/temp-file counts. Read those three signals first and the “just add an index” reflex will start feeling like what it is: a guess that works often enough to survive.
The official Using EXPLAIN documentation covers every option and node type in depth, the planner statistics docs explain how estimates are actually produced, and runtime resource configuration documents the work_mem knobs that govern sorts and hashes.