Reading a Postgres query plan

What it is

EXPLAIN shows the plan the optimiser chose. EXPLAIN ANALYZE runs the query and shows what actually happened alongside what was predicted. The gap between those two is where nearly every diagnosis lives.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, 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 >= '2026-07-01' AND c.region = 'EU';

Hash Join  (cost=412.55..8891.20 rows=2140 width=44)
           (actual time=6.104..241.882 rows=48211 loops=1)
  Hash Cond: (o.customer_id = c.id)
  Buffers: shared hit=1204 read=8817
  ->  Seq Scan on orders o  (cost=0.00..7104.00 rows=98412 width=28)
                            (actual time=0.019..118.442 rows=98330 loops=1)
        Filter: (created_at >= '2026-07-01'::date)
        Rows Removed by Filter: 401670
        Buffers: shared hit=1102 read=7404
  ->  Hash  (cost=386.00..386.00 rows=2124 width=24)
            (actual time=6.031..6.032 rows=2118 loops=1)
        ->  Seq Scan on customers c  (cost=0.00..386.00 rows=2124 width=24)
              Filter: (region = 'EU'::text)
Planning Time: 0.284 ms
Execution Time: 244.117 ms

Read it bottom-up and inside-out: the most indented nodes run first, feeding their parents. And read each node's two number sets against each other:

Field	Meaning
`cost=start..total`	The planner's estimate, in arbitrary units. Not milliseconds
`rows=N` (in `cost=`)	Estimated rows
`actual time=start..total`	Real milliseconds, per loop
`rows=N` (in `actual`)	Actual rows, per loop
`loops=N`	How many times this node ran

What it is confused with: cost is not time. Cost is a unitless number the planner uses to compare plans, calibrated so that a sequential page read is 1.0. A plan with cost 8891 is not "8891 milliseconds" and comparing costs between two different queries is meaningless. Comparing costs between two plans for the same query is the only valid use.

The second confusion, and it causes real misreadings: actual time and rows are per loop, and the totals are not. A node showing actual time=0.8..1.2 rows=3 loops=4000 took roughly 1.2 x 4000 = 4,800 ms and produced 12,000 rows. Reading it as 1.2 ms is the most common mistake in plan reading.

The problem it solves

A slow query has a small number of possible causes and the plan distinguishes them immediately. Without it you are guessing between: no usable index, an index that exists but is not selected, a bad row estimate leading to the wrong join strategy, a join executed the wrong way round, work spilling to disk, or a data volume that is simply large.

Those have completely different fixes. Adding an index to a query whose problem was a stale statistic wastes a week. The plan tells you which one it is in about thirty seconds, and the skill is knowing where to look.

Mechanics

The scan nodes

Seq Scan: read every page of the table. Not automatically bad. For a query returning 40 percent of a table, a sequential scan is correct, because random index lookups for 40 percent of rows costs more than reading the whole thing in order.

Index Scan: walk the index, then fetch each matching row from the heap. One random heap access per row, so it is good for a small fraction of the table and worse than a sequential scan for a large one.

Index Only Scan: the index contains every column the query needs, so the heap is never touched. The fastest option, and it depends on the visibility map being current, which depends on vacuum having run (see PostgreSQL MVCC and autovacuum). A high Heap Fetches number on an index-only scan means the visibility map is stale:

Index Only Scan using idx_orders_created on orders
  (actual time=0.031..44.118 rows=48211 loops=1)
  Heap Fetches: 47992          <- nearly every row went to the heap anyway

That is an index-only scan in name only, and the fix is vacuum, not an index change.

Bitmap Heap Scan with a Bitmap Index Scan beneath it: build a bitmap of matching page locations, sort it, then read the heap in physical order. This is the planner's middle option between the two above, chosen when the row count is too large for per-row random access and too small for a full scan. Seeing it usually means the planner made a reasonable choice.

Bitmap Heap Scan on orders  (actual rows=48211 loops=1)
  Recheck Cond: (created_at >= '2026-07-01')
  Heap Blocks: exact=8104 lossy=0        <- lossy>0 means work_mem was too small
  ->  Bitmap Index Scan on idx_orders_created  (actual rows=48211 loops=1)

lossy heap blocks mean the bitmap did not fit in work_mem, so it degraded to tracking whole pages instead of individual rows, and the recheck condition must then be evaluated against every row on those pages. Nonzero lossy is a signal to raise work_mem.

The join nodes, and what each implies

Nested Loop: for each row from the outer input, probe the inner. Cost is outer_rows x inner_cost. Excellent when the outer side is small and the inner has an index. Catastrophic when the outer row estimate is wrong, because the error is multiplied: an estimate of 10 outer rows that turns out to be 100,000 means 10,000x the expected work.

Hash Join: build a hash table from the smaller side, probe it with the larger. Good for large unsorted inputs, requires the hash table to fit in work_mem or it spills to disk in batches.

Merge Join: both inputs sorted on the join key, walked in parallel. Good when both sides are already sorted (from an index) and expensive when they must be sorted first.

The single highest-value thing in a plan is the estimate-versus-actual ratio on each node. A ratio near 1 means the planner had good information. A ratio of 20x or more means it chose a strategy for a different query than the one it ran:

->  Nested Loop  (cost=0.42..884.10 rows=12 width=44)
                 (actual time=0.055..8841.204 rows=284119 loops=1)
                                   ^^^^^^^^^^      ^^^^^^
                            estimated 12, got 284,119: 23,000x off

That plan is not slow because nested loop is bad. It is slow because the planner believed 12 rows and chose accordingly, and the fix is to correct the estimate, not to force a different join.

BUFFERS is the option people omit

BUFFERS reports actual page access, which converts "this is slow" into "this reads too much data":

Buffers: shared hit=1204 read=8817 dirtied=12 written=0
         ^^^^^^^^^^^^^^ ^^^^^^^^^
         found in cache  read from disk (or OS cache)

shared read is the number that matters: at 8 KB per buffer, read=8817 is about 69 MB pulled for this query. Always run EXPLAIN (ANALYZE, BUFFERS), because a query reading 69 MB to return 200 rows is diagnosable and a query that took 240 ms is not.

temp read/written appearing anywhere means work spilled to disk:

Sort  (actual time=1841.02..2104.55 rows=284119 loops=1)
  Sort Key: o.created_at DESC
  Sort Method: external merge  Disk: 48232kB      <- 47 MB written to disk

Sort Method: external merge means work_mem was too small and the sort went to disk. quicksort Memory: 24kB means it fit. This is one of the cheapest fixes available:

SET LOCAL work_mem = '256MB';    -- per operation, per node, per parallel worker

The caution that must accompany it: work_mem is per sort or hash node, not per query. A query with three sorts and two hash joins running with four parallel workers can use 5 x 4 x work_mem. Setting it globally to 256 MB on a 200-connection server is how you run out of memory.

Correcting bad estimates

Three causes, three different fixes.

Stale statistics. Autovacuum's analyze has not run since a bulk load.

ANALYZE orders;
-- Or make it sample more, for a skewed column:
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;   -- default 100
ANALYZE orders;

Correlated columns. The planner assumes independence, so it multiplies selectivities. For WHERE city = 'Toronto' AND province = 'ON', it estimates P(city) x P(province), when in reality city determines province almost exactly, so the true selectivity is just P(city). Extended statistics fix this:

CREATE STATISTICS stat_city_prov (dependencies, ndistinct)
    ON city, province FROM addresses;
ANALYZE addresses;

This is the fix people do not know exists, and correlated columns are extremely common (city/province, model/manufacturer, order_status/shipped_at).

Expressions the planner cannot see through. WHERE date_trunc('day', created_at) = '2026-07-01' gives the planner no idea of selectivity and prevents index use. Rewrite as a range, or index the expression:

-- Better: a sargable range
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02'

-- Or index the expression itself
CREATE INDEX ON orders (date_trunc('day', created_at));

The checklist, in order

Find the node where actual time jumps. Times are cumulative, so subtract children's totals from the parent's to find where the time is spent.
Check estimate versus actual on that node and its children. Anything over 10x is the likely root cause.
Check Rows Removed by Filter. A large number means rows are being read and discarded: a missing index or a missing partial index.
Check Buffers: shared read. How much data is actually moving.
Check for external merge, lossy heap blocks, or Batches: > 1. Any of these means work_mem is too small for this query.
Check loops. A cheap node executed 40,000 times is an expensive node.

A worked example: a 4.2-second query with a perfectly good index

An analytics endpoint. The query joined orders to customers and aggregated, and it had been fine for a year.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.region, count(*), sum(o.total_cents)
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
 WHERE o.status = 'COMPLETED'
   AND o.created_at >= now() - interval '7 days'
 GROUP BY c.region;

GroupAggregate  (actual time=4218.442..4218.501 rows=6 loops=1)
  ->  Sort  (actual time=4218.401..4218.440 rows=284119 loops=1)
        Sort Key: c.region
        Sort Method: external merge  Disk: 42104kB
        ->  Nested Loop  (cost=0.42..1204.55 rows=94 width=20)
                         (actual time=0.061..3901.882 rows=284119 loops=1)
              ->  Index Scan using idx_orders_status_created on orders o
                    (cost=0.43..884.10 rows=94 width=16)
                    (actual time=0.031..142.008 rows=284119 loops=1)
                    Index Cond: ((status = 'COMPLETED') AND (created_at >= ...))
              ->  Index Scan using customers_pkey on customers c
                    (cost=0.29..3.41 rows=1 width=12)
                    (actual time=0.012..0.012 rows=1 loops=284119)
                    Index Cond: (id = o.customer_id)
                    Buffers: shared hit=1136476
Planning Time: 0.402 ms
Execution Time: 4218.622 ms

Three problems, visible in three places.

1. The estimate is 3,000x wrong. The index scan estimated 94 rows and returned 284,119. Everything downstream is a consequence.

2. The nested loop executed 284,119 times. loops=284119 on the inner index scan, each taking 0.012 ms, is 284119 x 0.012 = 3,409 ms. The inner scan is individually fast and collectively the bulk of the query. The planner chose nested loop because it believed 94 outer rows, where nested loop is the correct choice.

3. The sort spilled. external merge Disk: 42104kB sorting 284,119 rows.

Why the estimate was wrong. The status column had five distinct values, but the distribution was extremely skewed: about 94 percent COMPLETED. Default statistics (100 buckets) captured the distinct values and the planner combined status = 'COMPLETED' with the date range assuming independence. It was also using a stale n_distinct for created_at because the table had grown 8x since the last full analyze on a column whose statistics target was default.

The fixes.

-- 1. Better statistics on the skewed column.
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

Re-running after just this:

Hash Join  (cost=1841.20..24104.55 rows=281404 width=20)
           (actual time=41.102..684.229 rows=284119 loops=1)

Estimate now 281,404 against an actual 284,119: within 1 percent. The planner switched from nested loop to hash join on its own, because with a correct estimate hash join is obviously better. Query time went from 4,218 ms to 712 ms with no index change and no query rewrite.

-- 2. Give the sort enough memory.
SET LOCAL work_mem = '128MB';

GroupAggregate  (actual time=498.221..498.280 rows=6 loops=1)
  ->  Sort  (actual time=498.180..498.219 rows=284119 loops=1)
        Sort Method: quicksort  Memory: 38104kB       <- in memory now
Execution Time: 502.118 ms

-- 3. A covering index so the join can be index-only.
CREATE INDEX idx_customers_id_region ON customers (id) INCLUDE (region);

Execution Time: 218.440 ms

Final:

                          before      after stats   after work_mem   after covering idx
Execution time            4,218ms       712ms           502ms              218ms
Join strategy             Nested Loop   Hash Join       Hash Join          Hash Join
Row estimate error        3,022x        1.01x           1.01x              1.01x
Sort                      disk 42 MB    disk 42 MB      memory 38 MB       memory
shared buffers read       1,136,476     28,104          28,104             18,442

A 19x improvement, and the first and largest step was one ANALYZE. The index the query needed already existed and was being used. The plan was slow because the planner was solving a different problem: it thought it was joining 94 rows.

That is the general lesson worth carrying: when a plan looks wrong, check the row estimates before changing anything. The planner is usually making a reasonable decision given what it believes, and the productive question is why it believes something false.

Production evidence

EXPLAIN (ANALYZE, BUFFERS) is the standard recommendation in PostgreSQL's own documentation and in every serious operational guide, specifically because BUFFERS converts a duration into a data-volume measurement that is comparable across runs and across machines.

CREATE STATISTICS for extended statistics arrived in PostgreSQL 10 (functional dependencies and n-distinct) and gained MCV lists in 12, added precisely because the independence assumption produces bad estimates on correlated columns, which is one of the most common causes of planner misbehaviour.

auto_explain ships as a contrib module and logs plans for queries exceeding a duration threshold, which is how you capture a plan for a query that is only slow in production under real data and concurrency:

auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

pg_stat_statements is the companion: it aggregates by normalised query text so you find which queries to explain, ranked by total time rather than by the one someone complained about.

explain.depesz.com and explain.dalibo.com exist as public plan visualisers and are widely used, which is a fair signal that the raw text format is hard to read at scale. Both highlight the estimate-versus-actual ratio prominently, which reflects where the information density is.

PostgreSQL 16 added EXPLAIN (GENERIC_PLAN), for explaining a parameterised query without supplying values, addressing the long-standing difficulty of explaining what a prepared statement will do.

The debate

Should you use planner hints? PostgreSQL deliberately has none, and the core team's position is that hints let applications freeze a plan that becomes wrong as data changes. The counter-argument, from anyone who has had a production query flip to a bad plan at 3am, is that a hint would have fixed it in one minute.

My position: PostgreSQL is right, and the escape hatches are sufficient. SET LOCAL enable_nestloop = off for one transaction, pg_hint_plan as an extension when you genuinely need it, and query restructuring (a CTE with MATERIALIZED, a LATERAL join) cover the emergencies. The discipline of fixing the estimate rather than overriding the decision produces better outcomes, because the same bad estimate is usually harming other queries you have not noticed.

Is a Seq Scan bad? No, and treating it as bad is the most common novice mistake. For a query touching a large fraction of a table, sequential scan is correct: reading pages in order is far cheaper per row than random index lookups. random_page_cost (default 4.0) tells the planner the ratio, and on SSD that default is wrong. Setting it to 1.1 is standard practice on SSD and shifts the planner appropriately toward index scans. A team seeing "too many sequential scans" should check that setting before adding indexes.

How much should you raise work_mem? Enough that the sorts and hashes in your important queries stay in memory, set per session or per transaction, not globally. The global value is multiplied by concurrent connections and by nodes and by parallel workers, so a generous global value is a memory-exhaustion risk. The pattern I would use is a low global default (4 to 16 MB) and SET LOCAL work_mem in the specific analytical transactions that need it.

Is EXPLAIN ANALYZE safe in production? It runs the query, including its side effects, so EXPLAIN ANALYZE DELETE ... deletes. Wrap it in a transaction you roll back. It also adds timing overhead that can be significant on plans with many nodes, which distorts the very measurement you want; EXPLAIN (ANALYZE, TIMING OFF) reduces that when you only need row counts.

Follow-up Q&A

"A query is slow. What do you do first?"

EXPLAIN (ANALYZE, BUFFERS) and then look at three things in order. Where does actual time jump, remembering that times are cumulative so I subtract children from parents. What is the estimate-versus-actual row ratio on that node, because anything over 10x means the planner solved a different problem. And how many buffers were read, which turns "slow" into "moves 69 MB." Only after those would I consider an index, because the most common finding is that the index exists and the planner did not use it correctly.

"The plan shows rows=94 estimated and 284,119 actual. What does that mean and what do you do?"

The planner chose its strategy for a 94-row query, so a nested loop was reasonable and is now executing 284,119 times. The fix is to correct the estimate rather than to force a different join. Causes in order: stale statistics, so ANALYZE; a skewed column whose default 100 statistics buckets do not capture the distribution, so raise SET STATISTICS; correlated columns where the planner multiplies selectivities assuming independence, so CREATE STATISTICS; or an expression the planner cannot estimate through, so rewrite it or index the expression.

"How do you read loops?"

actual time and rows are per loop. A node with actual time=0.012 rows=1 loops=284119 took about 3.4 seconds in total and returned 284,119 rows. Reading it as 0.012 ms is the most common misreading of a plan, and it hides exactly the case where a cheap operation is being done far too many times.

"When is a sequential scan the right plan?"

When the query touches a large fraction of the table, typically more than 5 to 20 percent depending on row width and random_page_cost. Reading pages sequentially is much cheaper per row than random heap access, so at high selectivity the index costs more than it saves. If you see sequential scans where you expected index scans, check random_page_cost first: the default of 4.0 assumes spinning disks and on SSD it should be around 1.1, which changes the planner's crossover point substantially.

"Sort Method: external merge Disk: 42104kB. What is that telling you?"

The sort did not fit in work_mem and spilled to disk, writing 42 MB. Raising work_mem for that query fixes it, and it must be per-transaction rather than global, because work_mem applies per sort or hash node per parallel worker, so a complex query with four workers can use many multiples of it. The same signal appears elsewhere as `Batches:

1on a hash join and aslossy` heap blocks on a bitmap scan.

"How do you capture the plan for a query that is only slow in production?"

auto_explain with log_min_duration set to a threshold, log_analyze and log_buffers on, and log_nested_statements on so plans inside functions are captured. Use pg_stat_statements to decide which queries matter, ranked by total time rather than by mean, because a 40 ms query run a million times outranks a 4 second query run twice. Reproducing locally usually fails because the data volume, the statistics and the cache state all differ.

Common misconceptions

"Cost is milliseconds." It is a unitless number calibrated so a sequential page read is 1.0. It is only meaningful when comparing plans for the same query.

"Sequential scans are bad." For a query returning a large fraction of a table, a sequential scan is the correct choice and an index scan would be slower.

"actual time on a node is that node's total time." It is per loop. Multiply by loops. And because times are cumulative up the tree, a parent's time includes its children's.

"If a query is slow, it needs an index." Frequently the index exists and the planner did not choose it, or chose it and used the wrong join strategy because of a bad row estimate. Adding an index to a statistics problem does nothing.

"Raising work_mem globally is a safe speedup." It applies per node per parallel worker, so a global value is multiplied several times per query and again by concurrent connections. Set it locally for the queries that need it.

Interview delivery note

Say this verbatim: "The first thing I look at is not the time, it is the estimate-versus-actual row count on each node. The planner is usually making a reasonable choice given what it believes, so when a plan looks wrong the productive question is why it believes something false, and the fix is a statistics fix rather than an index." It reframes plan reading from pattern-matching on node types to diagnosing the planner's inputs.

The senior-versus-staff separator is fixing the estimate rather than forcing the plan. A senior engineer identifies the nested loop as the problem and looks for a way to make it a hash join. A staff engineer notices the 3,000x row-estimate error, fixes the statistics, and watches the planner choose hash join by itself, then points out that the same bad estimate was almost certainly harming other queries nobody had investigated.

The second signal is loops. Reading actual time=0.012 loops=284119 as 3.4 seconds rather than 0.012 ms is the difference between reading a plan and glancing at one, and it is exactly where a cheap operation done too many times hides.

The Staff & Team Lead Interview Reference