Reading EXPLAIN ANALYZE

In one line

EXPLAIN shows the chosen plan with estimated costs and rows; EXPLAIN (ANALYZE, BUFFERS) runs the query and adds actual times, row counts, loops and buffer hits and reads. Read plans inside-out, compare estimated with actual rows to find misestimates, and look for the node where time and buffers concentrate.

0/5 · 0%

Think of it like this

A delivery receipt with timestamps at every hand-off. The total delay is obvious, but the receipt tells you which hand-off took four hours.

Key ideas

01
Structure: a tree; child nodes feed parents. Execution starts at the leaves. Each node shows cost=startup..total rows=estimated width=bytes and, with ANALYZE, actual time=first..last rows=actual loops=n.
02
Actual time and rows are per loop: multiply by loops. A nested loop inner side with loops=100000 and 0.05 ms each is 5 seconds.
03
Estimated vs actual rows off by 100× or more is the root of most bad plans: fix statistics, add extended stats, rewrite the predicate, or add an index that makes the estimate irrelevant.
04
BUFFERS: shared hit (from cache) vs read (from OS/disk) vs temp (spilled). High reads mean I/O-bound; temp means increase work_mem or reduce rows sorted.
05
Warnings: Rows Removed by Filter large (the index isn't selective or missing a column), Sort Method: external merge Disk (spill), Heap Fetches high in index-only scans (vacuum needed), SubPlan executed per row. Use EXPLAIN ANALYZE on writes only inside BEGIN ... ROLLBACK.

Code & diagrams

explain-before.txttext

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Limit  (cost=182345.10..182345.15 rows=20 width=96) (actual time=812.4..812.4 rows=20 loops=1)
  Buffers: shared hit=1204 read=86117
  ->  Sort  (cost=182345.10..182351.30 rows=2480 width=96) (actual time=812.4..812.4 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 30kB
        ->  Seq Scan on orders  (cost=0.00..182279.10 rows=2480 width=96)
                                (actual time=0.03..811.9 rows=2517 loops=1)
              Filter: (customer_id = 42)
              Rows Removed by Filter: 9997483
Planning Time: 0.2 ms
Execution Time: 812.5 ms

explain-after.txttext

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

Limit  (cost=0.43..24.10 rows=20 width=96) (actual time=0.04..0.09 rows=20 loops=1)
  Buffers: shared hit=24
  ->  Index Scan using orders_customer_id_created_at_idx on orders
        (cost=0.43..2935.1 rows=2480 width=96) (actual time=0.04..0.08 rows=20 loops=1)
        Index Cond: (customer_id = 42)
Planning Time: 0.3 ms
Execution Time: 0.1 ms          <- 8,000x faster, 24 buffers instead of 87,000

Interview problem

The problem

Diagnose a slow report from its plan

A plan shows a Nested Loop with outer rows=12 estimated but actual rows=480000, and an inner Index Scan with loops=480000. Total time is 95 s. What happened and how do you fix it?

When it breaks

Running EXPLAIN ANALYZE on a DELETE in production

What you see

EXPLAIN ANALYZE executes the statement: the rows are really deleted.

Fix & prevent

Wrap in BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;, or use plain EXPLAIN.

Explain it without notes

What's the first thing you check in a slow plan?

Practice

Run EXPLAIN (ANALYZE, BUFFERS) on a query before and after adding an index, and record time, buffers and plan shape.

Trade-offs

↔
EXPLAIN ANALYZE gives ground truth but runs the query; auto_explain in production captures slow plans with sampling overhead.

Done when you can

I can read EXPLAIN ANALYZE, find misestimates and hot nodes, and verify a fix.

Command Palette