PostgreSQL Explain Analyze and Real Execution

EXPLAIN tells you what PostgreSQL plans to do. EXPLAIN ANALYZE does it and reports what happened. Most performance conversations in interviews end up at one of these, and the useful skill isn’t reciting node types. It’s reading a plan quickly enough to point at the line that explains the slowness, which nearly always means comparing what the planner expected with what it got.

Every plan here was produced on PostgreSQL 16.15 against a one-million-row orders table and a 20,000-row customers table. Your costs and timings will differ; the shapes and the reasoning carry over.

Estimates first: plain EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

 Bitmap Heap Scan on orders  (cost=4.81..193.82 rows=50 width=34)
   Recheck Cond: (customer_id = 42)
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0)
         Index Cond: (customer_id = 42)

A plan is a tree. Read it from the most indented node outwards: the bitmap index scan finds matching row locations, then the heap scan above it fetches those rows from the table. Each node has:

• cost=4.81..193.82: startup cost and total cost in the planner’s arbitrary units (roughly “sequential page reads”). They’re only meaningful compared with other plans for the same query. They are not milliseconds.
• rows=50: how many rows the planner expects this node to produce.
• width=34: estimated average row size in bytes.

Nothing has run yet. Plain EXPLAIN is safe on any statement and is what you use to check whether an index will be considered at all.

Adding reality: ANALYZE and BUFFERS

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

 Bitmap Heap Scan on orders  (cost=4.81..193.82 rows=50 width=34) (actual time=0.064..0.157 rows=45 loops=1)
   Recheck Cond: (customer_id = 42)
   Heap Blocks: exact=44
   Buffers: shared hit=44 read=3
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.80 rows=50 width=0) (actual time=0.055..0.055 rows=45 loops=1)
         Index Cond: (customer_id = 42)
         Buffers: shared read=3
 Planning Time: 0.023 ms
 Execution Time: 0.206 ms

The second parenthesis is the measured part. actual time=0.064..0.157 is milliseconds to the first row and to the last row. rows=45 is what the node really produced, against an estimate of 50: close enough that the plan choice is trustworthy.

BUFFERS reports 8 KB pages touched. shared hit pages were already in PostgreSQL’s buffer cache; read pages came from the operating system (which may itself have cached them, so “read” doesn’t always mean disk). Buffers are often a better signal than time, because time varies with cache warmth and machine load while the number of pages a plan touches is stable. Since PostgreSQL 18, BUFFERS is included in ANALYZE output by default; on 16 and 17 you have to ask for it.

The estimate gap is the main clue

The planner picks plans from estimates. When an estimate is badly off, it picks a plan that’s right for imaginary data. Here’s a stale-statistics case. The table has autovacuum disabled for the demo, with 19,868 pending orders when statistics were last gathered. Then a batch job inserts 300,000 more pending orders:

EXPLAIN (ANALYZE, TIMING OFF)
SELECT customer_id, total FROM orders WHERE status = 'pending';

 Index Scan using orders_status_idx on orders  (cost=0.43..3843.30 rows=24570 width=10) (actual rows=319868 loops=1)
   Index Cond: (status = 'pending'::text)
 Execution Time: 135.524 ms

Estimated 24,570, actual 319,868: thirteen times more. An index scan fetching a third of a million rows one by one is a poor plan. After ANALYZE orders:

 Bitmap Heap Scan on orders  (cost=3529.78..18340.15 rows=318110 width=10) (actual rows=319868 loops=1)
   Recheck Cond: (status = 'pending'::text)
   Heap Blocks: exact=10103
   ->  Bitmap Index Scan on orders_status_idx  (...) (actual rows=319868 loops=1)
 Execution Time: 29.695 ms

The estimate is now accurate and the plan switches to a bitmap scan that reads each page once: 30 ms instead of 135. In production, autovacuum’s analyze usually keeps up, but bulk loads, freshly restored databases and tables that swing in size between runs are classic places where it doesn’t.

Stale statistics aren’t the only cause. PostgreSQL assumes columns are independent unless told otherwise. In customers, every Berlin customer is in Germany, but the planner multiplies the selectivities:

 Seq Scan on customers  (cost=0.00..409.00 rows=1250 width=0) (actual rows=5000 loops=1)
   Filter: ((country = 'DE'::text) AND (city = 'Berlin'::text))

A quarter of a quarter is 1,250, but the real answer is 5,000. Extended statistics fix it:

CREATE STATISTICS customers_country_city (dependencies) ON country, city FROM customers;
ANALYZE customers;
-- Seq Scan on customers  (cost=0.00..409.00 rows=5000 ...) (actual rows=5000 loops=1)

On a small table a 4x misestimate doesn’t matter. Feed it into a join over millions of rows and it decides between a hash join and a nested loop.

Loops: the number people forget to multiply

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT c.id, o.total
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id BETWEEN 100 AND 110;

 Nested Loop (actual time=0.038..0.910 rows=541 loops=1)
   ->  Index Only Scan using customers_pkey on customers c (actual time=0.010..0.015 rows=11 loops=1)
         Index Cond: ((id >= 100) AND (id <= 110))
   ->  Bitmap Heap Scan on orders o (actual time=0.015..0.070 rows=49 loops=11)
         Recheck Cond: (c.id = customer_id)

The inner scan says rows=49 loops=11. Those numbers are averages per loop: it ran 11 times (once per customer) and produced about 49 rows each time, so roughly 540 rows in total, which matches the 541 at the top. Time works the same way: 0.070 ms per loop times 11. When a nested loop’s inner side shows something like loops=200000 and a fraction of a millisecond each, that’s where the seconds went, even though no single number looks large.

ANALYZE really executes the statement

BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

 Delete on orders (actual rows=0 loops=1)
   ->  Bitmap Heap Scan on orders (actual rows=319868 loops=1)
         Recheck Cond: (status = 'pending'::text)
         Heap Blocks: exact=10103
         ->  Bitmap Index Scan on orders_status_idx (actual rows=319868 loops=1)
               Index Cond: (status = 'pending'::text)

Those 319,868 rows were deleted. The ROLLBACK is the only reason they’re still there. The actual rows=0 on the Delete node only means it returned no rows to the client, since there’s no RETURNING. Always wrap a write in a transaction you roll back, and remember that side effects outside the database (triggers calling pg_notify, sequence increments) aren’t undone by the rollback.

Habits that make plans useful

• Run with the real parameter values. A query for a customer with 45 orders and one with 400,000 can need different plans.
• Run twice. The first run often reads cold pages; the second shows the cached case. Mention which one you’re quoting.
• Read Rows Removed by Filter. A scan that discards 99% of what it reads is a candidate for an index whose condition appears in Index Cond instead. The composite index guide shows that difference.
• Paste big plans into a visualiser such as explain.depesz.com, which highlights exclusive time per node.
• Turn on auto_explain with log_min_duration in production to capture plans of slow queries as they actually ran.

Interview practice: the query that’s fast in staging

A report query takes 40 ms in staging and 25 seconds in production. Same indexes, same PostgreSQL version. You get the production EXPLAIN (ANALYZE, BUFFERS) output. What do you look for, in order?

Answer

First, the top-level Execution Time against Planning Time, to confirm the time is in execution. Then I’d scan every node for the biggest gap between estimated rows= and actual rows=, starting from the most indented nodes, because an early misestimate feeds every decision above it. Staging’s smaller or differently shaped data often produces accurate estimates and a different join strategy.

Next, loops on the inner side of nested loops: a plan that expected 10 outer rows and got 300,000 runs its inner index scan 300,000 times. Then Buffers: lots of read versus hit means production is going to disk, and a large Rows Removed by Filter means the index isn’t selective enough for production’s data.

The fixes follow from the cause: ANALYZE if statistics are stale, CREATE STATISTICS if columns are correlated, a better index if a filter discards most of what it reads, or a rewrite if the query shape forces a bad join. I’d confirm the fix by rerunning EXPLAIN (ANALYZE, BUFFERS) in production during a quiet period, inside a transaction if the query writes.

Keep going

• SQL and PostgreSQL interview questions
• SQL and PostgreSQL MCQs
• PostgreSQL composite indexes and query patterns
• PostgreSQL MVCC, old versions and vacuum
• Official docs: Using EXPLAIN