How to Read a PostgreSQL Execution Plan: The Complete Guide (With Real Examples)

If you've ever run EXPLAIN ANALYZE on a slow PostgreSQL query and stared at a wall of text with no idea what to do next, this guide is for you. We'll go from "what is this tree" to "here's exactly how I diagnose any slow query in production," with real SQL, real annotated output, and worked examples at every step.

TL;DR — the short version

• Run EXPLAIN (ANALYZE, BUFFERS) by default, not plain EXPLAIN

• Read the tree bottom-up — most-indented nodes execute first

• Cost is not milliseconds. It's an arbitrary planner unit; only actual time= from ANALYZE is real time

• The single most useful diagnostic: compare estimated vs actual rows on the slowest node

• Under a Nested Loop, multiply rows by loops for the real total — this trips up almost everyone at first

• Buffers: tells you if it's a cache problem (shared read) or a disk-spill problem (temp written)

• Full diagnostic order: find the slowest node → check its row estimate → check its buffers → then decide the fix

EXPLAIN vs EXPLAIN ANALYZE vs BUFFERS

Three related commands, and the difference matters:

-- Shows the planned query and estimated costs. Does NOT execute the query.
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Actually RUNS the query, then shows real timings and row counts alongside the plan.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- Adds cache-hit information (shared hit vs shared read) on top of ANALYZE.
-- This is the version to run by default.
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';

Important caveat: EXPLAIN ANALYZE executes the query for real. If you run it against an UPDATE, DELETE, or INSERT, those writes actually happen. Wrap it in a transaction and roll back if you're testing a write query in production:

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

Official reference: PostgreSQL documentation — EXPLAIN

Reading the plan tree

A plan is a tree of nodes. Indentation means nesting, and execution flows bottom-up: the most-indented nodes run first, and their output feeds into their parent.

Hash Join  (cost=1.45..89.30 rows=120 width=48)
  Hash Cond: (orders.customer_id = customers.id)
  ->  Seq Scan on orders  (cost=0.00..72.00 rows=5000 width=36)
  ->  Hash  (cost=1.00..1.00 rows=50 width=12)
        ->  Index Scan using customers_pkey on customers  (cost=0.00..1.00 rows=50 width=12)

Reading this: the Index Scan on customers runs first, feeds into Hash, which builds a hash table. Meanwhile Seq Scan on orders runs (order relative to the hash side isn't guaranteed, but both must complete before the join can run). Then Hash Join combines them. The outermost line is the last thing that happens — it's the final result of the whole query, not the first.

Cost, rows, and width

Every node shows a line like this:

(cost=0.42..8.44 rows=120 width=36)

• 0.42 — startup cost: the planner's estimated cost to produce the first row

• 8.44 — total cost: the planner's estimated cost to produce every row

• rows=120 — the planner's estimated row count

• width=36 — the average width of each row, in bytes

The single most common misunderstanding here: cost is not milliseconds. It's an arbitrary internal unit the planner uses to compare candidate plans against each other — roughly calibrated so that seq_page_cost = 1.0 represents reading one page sequentially. It's useful for comparing two plans for the same query, but it tells you nothing about wall-clock time on its own. The only real timing comes from ANALYZE, in the actual time=... portion:

Seq Scan on orders  (cost=0.00..72.00 rows=5000 width=36) (actual time=0.01..2.10 rows=5000 loops=1)

actual time=0.01..2.10 means: 0.01ms to the first row, 2.10ms total, in real measured milliseconds. That's the number to trust for "is this actually slow."

Illustration: picture a Hash Join node with cost=1.45..89000.30 — an easy number to look at and assume "that's the bottleneck." But the ANALYZE output for that same node might show actual time=0.31..4.87, under 5 milliseconds. The high cost reflects the planner being cautious about a worst-case row estimate that never materialized at runtime — it isn't a real performance problem. This is exactly why cost alone should never be the basis for deciding what to optimize; always check actual time.

The #1 signal: estimated vs actual rows

This is the fastest way to tell if the planner is working with good information.

Index Scan using orders_status_idx on orders  (cost=0.42..45.20 rows=120 width=36)
  (actual time=0.05..38.90 rows=48200 loops=1)

Planned: 120 rows. Actual: 48,200 rows. That's a 400x gap — a strong signal that the planner's statistics are stale, or that it's dealing with a filter it structurally can't estimate well (e.g., a correlated condition across two columns).

The fix:

-- Refresh the table's statistics
ANALYZE orders;

-- For correlated columns the planner consistently misjudges, add extended statistics
CREATE STATISTICS orders_status_created_stat (dependencies)
  ON status, created_at FROM orders;
ANALYZE orders;

You can also raise default_statistics_target for a specific column if the planner needs a finer-grained histogram:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

Illustration: a common trigger for this kind of gap is a bulk data load — say, importing 2 million historical orders in one batch job. Right after the load, the planner is still working off statistics from before the import, so it might estimate 500 rows for a filter that now matches 400,000. The query "suddenly" gets slow with no code change, because the planner picks an Index Scan or Nested Loop appropriate for the old, smaller estimate. Running ANALYZE right after any bulk load is a cheap habit that prevents this entire class of incident.

Planning Time vs Execution Time

At the very bottom of EXPLAIN ANALYZE output, separate from every node's individual cost:

Planning Time: 0.412 ms
Execution Time: 842.113 ms

This is your real, measured total — Planning Time is how long the planner spent choosing a plan; Execution Time is how long running that plan actually took. If Planning Time is unexpectedly high (rare, but happens with very complex queries or huge IN lists), that's its own separate problem from anything inside the node tree.

Scan node types

Postgres has three fundamentally different ways to find rows in a table, and the planner picks between them based on selectivity — how large a fraction of the table you're actually going to need.

Sequential Scan

Seq Scan on orders  (cost=0.00..72.00 rows=5000 width=36)
  Filter: (created_at > '2026-01-01')

Reads every page of the table, in physical order. This is the right choice — not a mistake — when you're going to need a large fraction of the table anyway, since the overhead of consulting an index isn't worth it.

Index Scan

Index Scan using orders_status_idx on orders  (cost=0.42..8.44 rows=12 width=36)
  Index Cond: (status = 'pending')

Walks a B-Tree index, then fetches each matching row from the heap individually. Wins when you only need a small slice of the table.

Index Only Scan

Index Only Scan using orders_status_created_idx on orders  (cost=0.42..4.20 rows=12 width=12)
  Index Cond: (status = 'pending')
  Heap Fetches: 0

This is the one people forget to check for. If every column the query needs is already present in the index, Postgres can skip the heap entirely — no separate row fetch at all.

The catch: it also requires the relevant pages to be marked all-visible in the visibility map, which means the table needs to have been vacuumed recently. A table with heavy, constant UPDATE churn may not get Index Only Scans even with a perfectly matching covering index, because pages keep falling out of "all-visible" status faster than autovacuum can re-mark them. If you see Heap Fetches: above zero, some rows still needed the heap despite using an index-only scan — worth investigating vacuum health on that table.

Often, turning an Index Scan into an Index Only Scan is a five-minute fix — add the missing column to the index, either as a key column or via INCLUDE:

CREATE INDEX orders_status_created_idx ON orders (status) INCLUDE (created_at);

Illustration: consider a dashboard query selecting COUNT(*) grouped by status, hitting the heap on every call despite an index on status — because the query also needs created_at for a date filter, a column not present in that index. Adding created_at via INCLUDE would turn it into an Index Only Scan, meaningfully cutting the query's buffer reads, with zero application code changes required.

Bitmap Index + Heap Scan

Neither a pure Index Scan nor a Seq Scan is always right — Bitmap Scan is the planner hedging its bet in between:

Bitmap Heap Scan on orders  (cost=12.50..145.30 rows=850 width=36)
  Recheck Cond: (status = 'pending')
  ->  Bitmap Index Scan on orders_status_idx  (cost=0.00..12.30 rows=850 width=0)
        Index Cond: (status = 'pending')

It happens in two steps:

1. Bitmap Index Scan builds an in-memory bitmap of matching pages — block-level, not row-by-row.

2. Bitmap Heap Scan visits those pages in physical disk order, turning what would've been random I/O into something closer to sequential I/O.

This shows up when the number of matching rows is too large for an efficient plain Index Scan (too many random heap jumps), but too small to justify reading the whole table.

Rows Removed by Filter

This is the number I check first when a query uses an index but is still slow:

Seq Scan on orders  (cost=0.00..320.00 rows=120 width=36)
  (actual time=0.02..45.10 rows=118 loops=1)
  Filter: (status = 'pending' AND priority = 'high')
  Rows Removed by Filter: 48000

Postgres fetched 48,118 rows, evaluated the filter on each one, and threw away 48,000 of them. This means the condition doing the real filtering isn't part of any index condition — it's applied after the rows are already fetched. If this number climbs into the tens of thousands, that's a strong signal to add an index (or a composite/partial index) covering the actual filter columns:

CREATE INDEX orders_status_priority_idx ON orders (status, priority)
  WHERE status = 'pending';  -- partial index, if 'pending' is a common filter value

Join strategies

Three ways Postgres combines two row sets, and the choice tells you something about your data before you even look at anything else.

Nested Loop

Nested Loop  (cost=0.42..24.60 rows=5 width=48)
  ->  Index Scan using orders_pkey on orders  (rows=1)
  ->  Index Scan using order_items_order_id_idx on order_items  (rows=5)

For each row on the outer side, probe the inner side. Cheap when the outer side is small — classic case: a primary key lookup joining to a small number of related rows.

The trap most people miss: under a Nested Loop, rows= on the inner node is the count per execution, not the total. The real total rows processed is rows × loops:

->  Index Scan using order_items_order_id_idx on order_items
      (actual time=0.01..0.03 rows=5 loops=8400)

Five rows looks tiny — until you notice loops=8400. That's 42,000 total row fetches, not 5. This misread is one of the most common mistakes when eyeballing a plan quickly.

Illustration: this is the classic "N+1 query, but inside a single SQL statement" trap. Imagine an ORM-generated query joining orders to a customer_notes table, expecting roughly one note per order. In reality, some customers have thousands of historical notes attached. The plan can look completely reasonable at a glance — rows=5 on the inner scan — but loops= can reveal it's actually executing that inner scan 12,000 times. The real fix in a case like this is often restructuring the query (e.g., a LIMIT inside a lateral join) rather than touching any index.

Hash Join

Hash Join  (cost=1.45..89.30 rows=120 width=48)
  ->  Seq Scan on orders  (rows=5000)
  ->  Hash  (rows=50)
        ->  Index Scan on customers  (rows=50)

Builds a hash table from one side (usually the smaller one), then probes it with the other. Wins on larger, unsorted inputs where the per-row lookup cost of a Nested Loop would add up.

Merge Join

Merge Join  (cost=0.85..210.40 rows=5000 width=48)
  Merge Cond: (orders.customer_id = customers.id)

Rarer in practice, but shows up when both sides already share a sort order — typically when joining on a column that's indexed (and therefore naturally sorted) on both sides. Since neither side needs to be re-sorted, it can merge them in a single linear pass.

Buffers: cache hits, disk reads, and temp spills

Buffers: is the fastest way to tell if you're looking at a caching problem or a genuine algorithmic problem.

Buffers: shared hit=42 read=6

• shared hit — the page was already in Postgres's own buffer cache (shared_buffers). Cheap.

• shared read — the page had to come from disk, or the OS page cache underneath Postgres. More expensive, but worth investigating specifically: it could mean your working set genuinely doesn't fit in cache, or it could just be a cold-cache first run, or memory pressure from other concurrently running queries. High shared read on a query you run constantly is worth investigating, not an automatic verdict that shared_buffers is undersized.

There's a second, different category worth watching separately:

Buffers: shared hit=120, temp read=850 written=850

temp read / temp written means Postgres spilled a sort or a hash operation to disk mid-query, because that specific operation's work_mem allowance ran out. This is a different fix than the shared-buffer story above — often a single session-level setting, not a schema change:

SET work_mem = '64MB';  -- session-level, for this connection only

Note work_mem is applied per sort/hash operation, not globally per query — a query with several sort/hash nodes can each independently use up to that amount.

Illustration: picture a monthly reporting query that sorts several million rows for a GROUP BY, running for 40+ seconds, with temp written in the tens of thousands of blocks. Rather than raising work_mem globally (risky — it multiplies across every concurrent connection and operation), you can set it just for that one reporting session: SET work_mem = '256MB'; immediately before running the report. A change like that can take runtime from 40+ seconds down to single digits, with no risk to the rest of the production workload's memory budget.

Parallel query: Gather and Gather Merge

When you see a Gather or Gather Merge node, Postgres split part of the plan across multiple parallel worker processes:

Gather  (cost=1000.00..5000.00 rows=1000 width=36)
  Workers Planned: 4
  Workers Launched: 2

Parallel workers aren't guaranteed just because the planner decided to use them. If Workers Launched comes in lower than Workers Planned, something capped it at execution time — usually max_parallel_workers_per_gather, or the server simply didn't have spare worker processes available in the shared pool (max_worker_processes / max_parallel_workers) at that moment.

-- Check current limits
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
SHOW max_worker_processes;

Worth knowing: more parallel workers isn't automatically faster. Parallel query has real coordination overhead (parallel_setup_cost, parallel_tuple_cost in the planner's cost model), and for small result sets, a plan using parallel workers can end up slower than a plain serial scan. The planner already accounts for this in its cost estimates — if you're forcing parallelism with SET max_parallel_workers_per_gather for a small query, you may be fighting the planner's correct judgment.

track_io_timing

Buffers: alone only shows you hit counts — how many blocks came from cache vs. disk. It doesn't tell you how long the disk reads actually took. For that, enable track_io_timing:

SET track_io_timing = on;  -- session-level, before running EXPLAIN

With it enabled, EXPLAIN (ANALYZE, BUFFERS) shows real read/write timing in milliseconds instead of just block counts:

Buffers: shared hit=42 read=6
I/O Timings: read=12.450

The tradeoff: timing every I/O call has a small measurement overhead (the cost of repeated gettimeofday-style syscalls). Most teams enable it for a focused diagnostic session rather than leaving it permanently on in a high-throughput production system. You can also enable it cluster-wide via postgresql.conf if you want it always available.

Red flags checklist

None of these four are damning on their own — context always matters. But together, they're the fastest way to triage a plan you've never seen before:

• Sequential Scan on a huge table with a selective filter. Only a problem if the filter is selective — scanning everything when you genuinely need most of it back is often correct behavior, not a bug.

• Large gap between estimated and actual rows. Tells you the planner might have chosen a meaningfully better plan with accurate statistics.

• Nested Loop looping over far more rows than expected. Remember the loops multiplier — a small rows= number under a high loops= count can turn what looks like a cheap query into a genuine production issue.

• High shared read on a query that runs constantly. Often the cheapest fix in this whole list — it might just mean the working set needs more memory, not a rewrite.

The 4-step diagnostic walkthrough

This is the order to actually go in — not the order most people instinctively use:

1. Find the slowest node — the one with the biggest jump in cumulative actual time, not necessarily the top of the plan. Everything else is often just noise around this one node.

2. Compare estimated vs actual rows on that specific node — remembering to account for loops if it sits under a Nested Loop.

3. Check its buffers — is this a caching problem (shared read), a disk-spill problem (temp read/written), or neither (meaning it's a genuinely algorithmic cost)?

4. Only then, decide the fix — missing index, stale statistics, wrong join strategy for the actual data distribution, or parallelism capped below what was planned. Jumping straight to "add an index" before doing steps 1-3 is how you end up fixing the wrong problem.

Full worked example

Putting all of it together against one real, annotated plan:

Hash Join  (cost=1.45..89.30 rows=120 width=48) (actual time=0.31..4.87 rows=118 loops=1)
  Hash Cond: (orders.customer_id = customers.id)
  Buffers: shared hit=42
  ->  Bitmap Heap Scan on orders  (cost=12.50..72.00 rows=5000 width=36)
        (actual time=0.15..3.20 rows=5000 loops=1)
        Recheck Cond: (status = 'pending')
        Rows Removed by Filter: 200
        Buffers: shared hit=30 read=6
        ->  Bitmap Index Scan on orders_status_idx  (rows=5200)
              (actual time=0.08..0.08 rows=5200 loops=1)
  ->  Hash  (cost=1.00..1.00 rows=50 width=12) (actual time=0.05..0.05 rows=50 loops=1)
        ->  Index Scan using customers_pkey on customers  (rows=50)
              (actual time=0.02..0.04 rows=50 loops=1)
              Buffers: shared hit=6
Planning Time: 0.180 ms
Execution Time: 5.120 ms

Reading this the way you actually would in production:

• Top-level: the Hash Join returned 118 rows, taking 4.87ms — every buffer at this level (shared hit=42) was already cached, so nothing here is disk-bound.

• Which child took the most buffers? The Bitmap Heap Scan on orders — 30 cache hits plus 6 disk reads. Not a red flag today, but it's the one line to watch if this query starts showing up in slow-query logs later, since it's the only place any disk I/O happened at all.

• Filter loss: Rows Removed by Filter: 200 on the same node — small and cheap here, but worth remembering the pattern for when it shows up somewhere with a much bigger number.

• Total execution: 5.12ms, planning added a negligible 0.18ms. Nothing in this plan needs a fix — this is what a healthy plan looks like end-to-end.

That's the whole method. You don't need to memorize every node type going in — you need to know where to look: the tree's shape, the row counts (both estimated and actual, with loops accounted for), and the buffers. Once you're reading those three things together, the bottleneck in any plan tends to find itself.