Reading EXPLAIN ANALYZE Without Guessing

Most developers run EXPLAIN ANALYZE the same way they run a stack trace on an error they don’t understand — paste it into the query, get a wall of text back, scan for a big number, and guess. That guess is usually “add an index” or “it’s the JOIN,” and sometimes it’s right by accident. The plan itself already tells you exactly what’s slow and why; the problem is that most of us were never shown how to read it as a structured document instead of a scary block of monospace text.

That gap costs real time. A developer who can’t read a plan will try three unrelated fixes before finding the right one — reach for SELECT * trimming, then a cache layer, then finally an index, when the plan said “missing index” in the first ten seconds if you knew where to look. This post walks through the actual grammar of a Postgres query plan: what each line encodes, which numbers are estimates versus reality, how loop counts multiply hidden costs, what the buffer counters are actually measuring, and the three recurring plan shapes that are Postgres’s way of telling you an index is missing.

You’ll learn:

• How to read the tree structure of a plan and know which node is really driving the cost
• The difference between the planner’s estimated rows and PostgreSQL’s actual rows, and why a large gap between them is the single most useful signal in the whole plan
• Why a cheap-looking node run inside a loop can dominate total query time
• How to read BUFFERS output — shared hit vs. read, and what that tells you about cache pressure
• The three plan patterns — sequential scan on a filtered large table, nested loop with a high loop count, and sort spilling to disk — that almost always mean a missing or wrong index
• A full worked example diagnosing a genuinely slow query from its plan alone

Table of Contents

1. What EXPLAIN ANALYZE Actually Does
2. Anatomy of a Plan Line
3. Estimated vs. Actual Rows
4. Loops: Why a Cheap Node Can Dominate
5. Reading Buffers: Cache Hits vs. Disk Reads
6. The Three Patterns That Mean Missing Index
7. A Full Walkthrough
8. Common Pitfalls
9. Where to Go From Here

What EXPLAIN ANALYZE Actually Does

EXPLAIN alone asks the planner what it would do — it estimates a plan using table statistics and prints it without running anything. EXPLAIN ANALYZE actually executes the query, times each step, and then prints the same tree annotated with what really happened. That distinction matters more than it sounds like it should: EXPLAIN is safe to run against anything, including a DROP wrapped in a transaction you intend to roll back, but EXPLAIN ANALYZE genuinely runs an INSERT, UPDATE, or DELETE unless you wrap it in BEGIN; ... ROLLBACK;.

The output you actually want for real debugging is this form:

EXPLAIN (ANALYZE, BUFFERS, 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 '7 days'
  AND o.status = 'pending';

ANALYZE gets you real timings and real row counts. BUFFERS gets you cache-hit information, which is off by default for historical reasons but is genuinely useful in every real investigation — turn it on every time. Skip COSTS OFF unless you’re pasting the plan somewhere that needs to be diffable across runs; you want the cost estimates for comparison against actual numbers.

A plan is a tree, read bottom-up and inside-out. The innermost, most-indented nodes run first; their output feeds into the node directly above them. The top line is the last thing that happens and is where the total time and total cost are reported. It’s tempting to read top to bottom like prose, but the actual data flow — and usually the actual bottleneck — lives in the leaves at the bottom.

Anatomy of a Plan Line

Here’s a single node from a real plan, annotated piece by piece:

Seq Scan on orders o  (cost=0.00..18734.00 rows=812 width=24)
                       (actual time=0.021..142.558 rows=790 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 199210
  Buffers: shared hit=210 read=8312

Breaking that down:

• Seq Scan on orders o — the operation and the table (or alias) it runs against. A sequential scan reads every row in the table or index in physical order; that’s not automatically bad, but it’s the thing to notice on a large table.
• cost=0.00..18734.00 — the planner’s estimated cost range, in arbitrary planner cost units (not milliseconds). The first number is the estimated cost to return the first row; the second is the estimated cost to return all rows. These numbers are only comparable to other cost numbers in the same plan, never across queries or servers.
• rows=812 — the planner’s estimate of how many rows this node will produce, based on table statistics.
• width=24 — the estimated average row width in bytes.
• actual time=0.021..142.558 — real, measured milliseconds: time to first row, then time to completion, averaged across all loop iterations (more on that below).
• rows=790 loops=1 — the real number of rows this node actually returned, and how many times the node executed.
• Filter / Rows Removed by Filter — a post-scan filter applied after the rows were fetched. A large “rows removed” number next to a small final row count is a strong signal the scan is doing far more work than it needs to.
• Buffers — how many 8KB pages were touched, split between cache hits and disk reads.

Every node in the tree carries this same shape. Once you can parse one line, you can parse the whole plan — the skill is entirely about knowing which numbers to compare against each other.

Estimated vs. Actual Rows

This is the single highest-value comparison in the entire plan. The planner estimates rows=812 before running anything, based on statistics gathered by ANALYZE (the maintenance command, not the EXPLAIN option — confusingly, they share a name). After execution, Postgres reports what actually came out. When these two numbers are close, the planner had good information and almost certainly chose a good plan. When they’re off by 10x, 100x, or more, everything built on top of that estimate — join strategy, memory allocation, execution order — was decided using bad information, and the resulting plan is often bad in ways that are hard to predict from the estimate alone.

-> Index Scan using idx_orders_status on orders
     (cost=0.42..8.44 rows=1 width=24)
     (actual rows=48000 loops=1)

An estimate of 1 row against an actual of 48,000 is a massive misestimate. This usually happens for one of a few reasons:

• Stale statistics. The table has changed significantly since the last ANALYZE ran, and autovacuum hasn’t caught up. Run ANALYZE orders; manually and compare.
• Correlated columns. The planner assumes columns are independent by default. A WHERE status = 'pending' AND created_at > now() - interval '7 days' filter might be far more (or less) selective in combination than either column is alone, and the default statistics don’t capture that correlation. Postgres’s CREATE STATISTICS for extended statistics exists specifically to fix this.
• Non-uniform data distribution. If 90% of rows share one value in a skewed column, the default statistics target (100 buckets by default) may not resolve that skew finely enough. Raising default_statistics_target, or setting it per-column with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS, gives the planner a finer-grained histogram.

When you’re doing general postgres slow query debugging, this comparison is where to look first, before buffers, before cost numbers, before anything else — a bad estimate is usually the root cause, and everything downstream is a symptom.

Loops: Why a Cheap Node Can Dominate

loops=1 means the node executed once. Under a nested loop join, an inner node can execute once per row produced by the outer side — and the actual time reported for that node is the average per loop, not the total. This is the single most common misread in the entire plan.

Nested Loop  (actual time=0.045..891.223 rows=48000 loops=1)
  -> Seq Scan on customers c (actual time=0.010..12.400 rows=4000 loops=1)
  -> Index Scan using idx_orders_customer on orders o
       (actual time=0.008..0.019 rows=12 loops=4000)

That inner index scan looks trivially cheap: 0.019ms. But it ran 4,000 times — once per customer row from the outer scan — so its real contribution is roughly 0.019ms * 4000 ≈ 76ms, not 0.019ms. Always multiply actual time by loop count before judging a node’s real cost. A tiny per-loop time with a five-figure loop count is often the actual bottleneck hiding in plain sight, while the node with the single biggest actual time number contributes comparatively little.

This is also the mechanism behind the classic “works fine with 100 rows, falls over with 100,000” bug: a nested loop is a fine strategy when the outer side is small, and a linearly worsening one as it grows, which is exactly the shape of the N+1 problem that shows up at the ORM level too — see the Django N+1 post for that pattern from the application side.

Reading Buffers: Cache Hits vs. Disk Reads

Buffers: shared hit=210 read=8312 reports 8KB page accesses against the shared buffer cache:

• shared hit — pages found already in PostgreSQL’s shared buffer cache. Fast; effectively RAM speed.
• shared read — pages that had to be pulled from disk (or the OS page cache, which Postgres can’t distinguish from true disk I/O) because they weren’t in the buffer cache.
• shared dirtied — pages modified in this operation, relevant on writes.
• shared written — pages written out to make room, often a sign of buffer cache pressure.

A high read relative to hit on a query that runs frequently signals the working set doesn’t fit comfortably in shared_buffers — or that this is simply a cold cache on a rarely-run query. Run the same EXPLAIN (ANALYZE, BUFFERS) twice in a row; a second run showing mostly hits where the first showed mostly reads means you were looking at cold-cache numbers, not steady-state cost.

Buffers are also the most honest cost signal available, because they’re not scaled by arbitrary planner cost units — they’re literal page counts, comparable directly across different queries and different plans for the same query. When two candidate indexes produce plans with similar actual time, the one with fewer total buffer touches is doing genuinely less I/O work and will hold up better under concurrent load.

The Three Patterns That Mean Missing Index

After enough plans, three shapes recur constantly, and all three point at the same root cause.

1. A sequential scan with a selective filter on a large table.

Seq Scan on orders o  (cost=0.00..18734.00 rows=812 width=24)
                       (actual time=0.021..142.558 rows=790 loops=1)
  Filter: (status = 'pending'::text)
  Rows Removed by Filter: 199210

Scanning 200,000 rows to keep 790 of them is the plan doing nearly all its work throwing rows away. Rows Removed by Filter sitting orders of magnitude above the final row count, on a table too large to fit comfortably in cache, is Postgres implicitly saying “I have no index to jump straight to the rows you want.” An index on status — or better, a partial index (WHERE status = 'pending') if that value is rare — turns this into an index scan touching only the matching rows.

2. A nested loop with a very high loop count feeding an unindexed inner scan.

-> Seq Scan on order_items oi
     (actual time=0.412..3.891 rows=6 loops=4000)
   Filter: (order_id = o.id)

An inner sequential scan running thousands of times, each time filtering the whole order_items table down to a handful of matching rows, is the loop pattern from the previous section combined with a missing index on the join column. An index on order_items(order_id) turns each of those 4,000 sequential scans into a cheap index lookup, and the total query time usually drops by an order of magnitude or more.

3. A sort that spills to disk instead of completing in memory.

Sort  (cost=41293.55..41808.36 rows=205925 width=32)
      (actual time=387.223..421.009 rows=205925 loops=1)
  Sort Method: external merge  Disk: 7128kB
  -> Seq Scan on orders ...

Sort Method: external merge Disk: ...kB means the sort didn’t fit in work_mem and spilled to temporary files on disk — meaningfully slower than an in-memory quicksort or top-N heapsort. Two independent fixes apply here, and they’re not mutually exclusive: raise work_mem for the session or query if the server has headroom, or — usually the better fix — add an index matching the ORDER BY clause so the sort is avoided entirely and the rows come out pre-ordered from an Index Scan instead of a Sort node.

All three patterns share the same underlying story: the planner is doing the best it can with a sequential or brute-force strategy because there’s no better path available. For a full tour of which index type fits which of these shapes — B-tree, GIN, GiST, or BRIN — the Postgres index types guide covers the decision in depth.

A Full Walkthrough

Take a genuinely slow endpoint: “list pending orders from the last week for a customer’s account, newest first.” The query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'pending'
  AND created_at > now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 20;

First plan, before any changes:

Limit  (actual time=203.441..203.448 rows=14 loops=1)
  -> Sort  (actual time=203.439..203.443 rows=14 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 26kB
        -> Seq Scan on orders  (actual time=0.033..201.887 rows=14 loops=1)
              Filter: ((customer_id = 4821) AND (status = 'pending')
                       AND (created_at > now() - interval '7 days'))
              Rows Removed by Filter: 611982
              Buffers: shared hit=402 read=9812

Reading it: the sort is trivial (14 rows, in-memory quicksort). The Seq Scan filtered out 611,982 rows to keep 14, touching over 10,000 buffer pages — pattern one, unambiguously. The fix is a composite index matching the filter columns, with the sort key included so the database can potentially skip a separate sort step too:

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Re-running the same query after the index build:

Limit  (actual time=0.061..0.089 rows=14 loops=1)
  -> Index Scan using idx_orders_customer_status_created on orders
        (actual time=0.060..0.086 rows=14 loops=1)
        Index Cond: ((customer_id = 4821) AND (status = 'pending')
                      AND (created_at > (now() - interval '7 days')))
        Buffers: shared hit=6

203ms down to 0.089ms, buffer touches down from ~10,200 to 6, and no Sort node at all — the index’s column order already matches LIMIT 20’s needs. That’s the entire diagnostic loop: read the tree, find the node with the largest real contribution to time, match its shape against the three patterns, fix the index, and re-run to confirm the plan shape actually changed.

Common Pitfalls

Mistake: comparing cost numbers across different queries. Cost is a unitless, planner-internal estimate calibrated by random_page_cost and friends — it’s only meaningful relative to other nodes in the same plan. Solution: compare actual time and buffer counts for cross-query comparisons, not cost.

Mistake: reading actual time on a looped node as a total. As shown above, that number is a per-loop average. Solution: always multiply by loops before judging a node’s real contribution.

Mistake: running EXPLAIN ANALYZE once on a cold cache and concluding the query is slow in production. The first run after a restart or against rarely-touched data pays disk I/O that steady-state traffic usually doesn’t. Solution: run it twice, and trust the buffer hit ratio, not just the first number.

Mistake: assuming “index scan” always beats “seq scan.” On a small table, or when a query needs most of the table’s rows anyway, a sequential scan is genuinely faster — no index traversal overhead. Solution: judge by actual time and row counts, not by node name alone.

Mistake: adding an index and not re-running EXPLAIN ANALYZE to confirm the plan actually changed. Postgres won’t necessarily use a new index if its statistics still favor the old plan, or if the index doesn’t match the query’s leading filter column. Solution: always re-run and check the plan shape changed, not just that the query got faster — a faster time alone doesn’t prove the fix generalizes.

Where to Go From Here

This post covers reading a single plan in isolation. The rest of this series covers the decisions that shape what plans are even possible in the first place:

• Picking the right index type — B-tree, GIN, GiST, and BRIN, and which query shapes each one actually helps.
• Finding and killing N+1 queries in Django — the loop pattern from this post, seen from the ORM side instead of the plan side.
• Connection pooling for Python apps — because a fast query plan doesn’t help if requests are queued waiting for a connection.
• pgvector vs. a dedicated vector database — reading plans for similarity search has its own quirks worth knowing.
• Zero-downtime Postgres migrations — building the very indexes this post recommends, without locking the table you’re trying to speed up.

Wrapping Up

A Postgres query plan isn’t a mystery output to skim for scary-looking numbers — it’s a structured, honest report of exactly what the database did, in what order, and how that compared to what it expected. Read it bottom-up, compare estimated rows to actual rows first, multiply looped nodes by their loop count before judging their cost, check the buffer hit ratio for cache pressure, and match what you see against the three patterns that mean “the planner has no good option here.” Once that reading order becomes automatic, EXPLAIN ANALYZE stops being a wall of text and starts being the fastest debugging tool in the entire stack.

Next time a query is slow, does your first move come from the plan, or from a guess?