Reading EXPLAIN Like a Planner: Debugging Slow Queries in PostgreSQL

# POST 2: Reading EXPLAIN

A query that ran in 20 milliseconds yesterday takes 40 seconds today. Nothing in the application changed. The schema did not change. Before you add a hint, an index, or a caching layer, you need to answer one question: what is Postgres actually doing with this query? The EXPLAIN command answers it, and learning to read its output is one of the highest-leverage skills a backend developer can pick up.

This post covers how to read a query plan: what the cost numbers mean (and what they do not mean), why rows estimates go wrong and what follows from that, when to use ANALYZE and BUFFERS, and the most common plan shapes you will see in a web application. The reference material is the Using EXPLAIN chapter of the Postgres documentation — everything here is in there, minus the parts you will not need until your second or third slow query.

A Plan Is a Tree of Nodes

Postgres turns every query into a plan: a tree where the leaves are scan nodes that fetch rows, and the nodes above them join, filter, aggregate, or sort. The topmost line is the whole plan, and its total cost is what the planner tried to minimize:

EXPLAIN SELECT * FROM tenk1;

QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..445.00 rows=10000 width=244)

The numbers in parentheses are, in order: estimated startup cost, estimated total cost, estimated output rows, and estimated average row width in bytes. Three things people get wrong about them:

Costs are arbitrary units, not milliseconds. They are expressed relative to parameters like seq_page_cost (1.0) and cpu_tuple_cost (0.01). Use them to compare plans against each other, never to predict wall-clock time. For that, use ANALYZE mode, covered below.
Total cost assumes the node runs to completion. A LIMIT parent can stop a child early, which is why a query whose plan shows a huge top-line cost can still be fast — only a fraction of the work ran.
rows is rows emitted, not rows scanned. A seq scan over a million rows with a filter still scans everything; rows shows only what passes the filter. The gap between the two is the filter's selectivity estimate.

You can even verify the arithmetic yourself. The documentation's tenk1 example has 345 pages and 10,000 rows, so the seq scan cost is (345 × 1.0) + (10,000 × 0.01) = 458. Once you internalize that cost is just a model of pages and tuple processing, the numbers stop being magic.

How Selectivity Changes the Shape of the Plan

Add a moderately selective condition and the plan changes:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;

QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.06..224.98 rows=100 width=244)
   Recheck Cond: (unique1 < 100)
   -> Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=100 width=0)
         Index Cond: (unique1 < 100)

Read it bottom-up: the index scan finds matching row locations, the bitmap step sorts them by physical page, and the heap scan fetches the rows in that order — turning random I/O into roughly sequential I/O. This is the plan you want to see for a selective condition on an indexed column. If you see a Seq Scan with a filter for a query you know touches a tiny fraction of a big table, the planner either lacks an index or lacks the statistics to realize the index would help.

Which brings up the most important mental model in plan reading: the plan is only as good as the row estimates. Estimates come from statistics collected by ANALYZE — random samples, not exhaustive counts. They go stale after bulk loads and large deletes, and they are approximations even at their best. The autovacuum daemon runs ANALYZE automatically as tables change, but a table that just ingested 50 million rows will mislead the planner until fresh statistics land. Stale or skewed estimates do not just pick the wrong index — they cascade. Overestimated rows make the planner prefer hash joins; underestimated rows make it pick nested loops over what should have been a hash join, and a query touching thousands of rows turns into millions of index probes.

A practical rule: when a plan looks insane, check the estimated rows against reality first. If they differ by orders of magnitude, fix the statistics (run ANALYZE, raise the statistics target on the offending column) before touching anything else.

EXPLAIN ANALYZE: Real Numbers, Real Execution

Plain EXPLAIN is a simulation. Add ANALYZE and Postgres actually runs the query, attaching real timing and row counts to every node:

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100;

QUERY PLAN
------------------------------------------------------------------
 Bitmap Heap Scan on tenk1
   (actual time=0.423..2.811 rows=100 loops=1)
   ...

Compare actual rows=100 against the estimated rows=100 at each node — when they diverge wildly somewhere in the tree, you have found your misestimate. The loops value matters too: a node that shows actual time=0.05 with loops=10000 executed ten thousand times, and the per-loop timing is not multiplied for you.

One caveat the documentation is blunt about: EXPLAIN ANALYZE really executes the statement. For SELECT that is merely expensive; for INSERT, UPDATE, or DELETE, the changes are real. The standard practice for tuning a write query is to wrap it in a transaction and roll back:

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE created_at < now() - interval '30 days';
ROLLBACK;

You pay for the execution, but none of its effects survive. On production, also remember that timing instrumentation adds per-node overhead — small compared to the query itself in most cases, but meaningful for very fast queries where the instrumentation can dominate the runtime.

BUFFERS: Where the Time Actually Goes

Timings tell you which node is slow. BUFFERS tells you why:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100;

Each node reports shared blocks hit (served from the shared buffer cache) and read (fetched from the OS or disk), plus written and dirtied blocks where relevant. A plan that is mostly hit is CPU- or estimation-bound; a plan reading tens of thousands of blocks per execution is I/O-bound, and no amount of planner cleverness will save you until the working set fits in cache or the scan becomes selective. When someone says "check the query plan" for a performance regression, EXPLAIN (ANALYZE, BUFFERS) is almost always what they mean. The EXPLAIN reference lists the full set of options, including machine-readable JSON output if you want to diff plans or feed them to tooling.

The Plan Shapes You Will Actually See

Most application queries resolve into a handful of shapes, and each carries a specific diagnosis:

Seq Scan on a huge table with a filter — missing index, or statistics saying the filter matches most rows. Check the estimated selectivity before creating the index.
Nested Loop with a huge outer relation — the classic misestimate injury. Nested loops are excellent when the outer side is genuinely small; with a big outer side they are quadratic. The planner chose it because it believed the estimate.
Hash Join — the workhorse for joining large sets: build a hash table from the smaller side, probe with the larger. Usually the right choice; slow only when the hash table spills to disk.
Sort + expensive node below a LIMIT — Postgres may be sorting everything to return ten rows. Top-N heapsorts mitigate this; an index matching the ORDER BY eliminates it.
Bitmap Heap Scan with Rows Removed by Filter in the thousands — the index is helping less than it looks. The index found many rows; the filter discarded most of them. A composite index matching the full predicate usually fixes it.

The index documentation walks through deciding whether an index is actually used, which is the same investigation in miniature: run EXPLAIN, check whether the planner picks the index, and interrogate the estimates when it does not.

A Workflow That Works

When a query is slow, resist the urge to add an index immediately. This order of operations finds the root cause more often than not:

Run EXPLAIN (ANALYZE, BUFFERS) — wrapped in BEGIN/ROLLBACK if the statement writes.
Compare estimated vs. actual rows at each node. A big divergence means stale or insufficient statistics: run ANALYZE on the table, then re-check.
Look at buffers: read. Heavy reads mean the working set does not fit or the scan is not selective; light reads with high time mean CPU-bound work — usually a mischosen join or sort.
Only then consider structural fixes: a composite index matching the predicate and ordering, a query rewrite, or (rarely) planner cost tuning.

Plan reading rewards a specific habit: distrust every number until you have compared it against its neighbor. Cost against actual time, estimated rows against actual rows, hit against read. The gaps between the pairs are where every slow query hides.