Every slow query investigation eventually arrives at the same place: the EXPLAIN output. It is the single most information-dense tool PostgreSQL gives you, and also the most misread. Developers glance at whether an index is being used, shrug, and move on — while the real answer, a twelve-million-row estimate against nine hundred actual rows, sits quietly in parentheses two lines down. Learning to read the plan properly turns query tuning from trial and error into diagnosis.
This post covers what the numbers in a plan actually mean, which options to enable, and how to walk from a suspicious plan node to a concrete fix. The examples use PostgreSQL’s current syntax, checked against the official EXPLAIN reference.
Costs Are Estimates, Not Measurements
Plain EXPLAIN shows the plan the planner intends to run, annotated with cost estimates. Each node shows two numbers: the startup cost, how much work happens before the first row can be returned, and the total cost for the whole node. Costs are in arbitrary units calibrated so that one sequential page read costs 1.0 and one indexed page read costs 4.0 by default. They are not milliseconds and never will be — their only job is to let the planner, and you, compare candidate plans.
Two consequences follow. First, a lower total cost is not automatically a faster query for your purposes: the planner optimizes total cost, but for a paginated query with a small LIMIT, startup cost dominates the user experience, and the planner interpolates accordingly. Second, the cost is only as good as the statistics feeding it. Everything downstream — join algorithm choice, index selection, row estimates — flows from the sampling histograms in pg_statistic, refreshed by ANALYZE. The planner statistics documentation is worth reading in full once; it explains why the planner behaves the way it does.
EXPLAIN ANALYZE Executes the Statement
Adding ANALYZE actually runs the query and decorates every node with real timings and row counts. This is where most insight lives, and also the sharpest edge: an UPDATE or DELETE under EXPLAIN ANALYZE commits its side effects exactly like the real statement. The safe pattern for mutating queries is to wrap it in a transaction you roll back:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE accounts SET tier = 'gold' WHERE lifetime_cents > 50000000;
ROLLBACK;Timing instrumentation is not free, either. Per-node timers can distort plans dominated by very cheap operations — an index scan retrieving one row can look several times slower than it really is. For those cases, keep the row counts but drop the timers:
EXPLAIN (ANALYZE, TIMING OFF, BUFFERS)
SELECT * FROM events WHERE device_id = 42;The Options Worth Knowing
A few options earn their place in muscle memory. BUFFERS shows how many pages each node read from shared buffers versus the OS cache and disk, and since PostgreSQL 18 it is included automatically with EXPLAIN ANALYZE — on older versions you must ask for it. WAL (added in PostgreSQL 13) reports write-ahead log volume, essential before bulk loads. SETTINGS (PostgreSQL 12) prints any non-default planner parameters, which explains otherwise mysterious plan changes across environments. GENERIC_PLAN (PostgreSQL 16) plans a parameterized query without requiring literal values, invaluable when debugging statements coming from an ORM. PostgreSQL 17 added two more: MEMORY, which reports planned memory usage per node, and SERIALIZE, which estimates the cost of converting rows for network transmission — relevant when a query transfers enormous text or JSON columns. For programmatic analysis, FORMAT JSON emits the whole tree as structured data.
Reading a Real Plan: The Misestimate
Consider a dashboard query joining customers to their orders, filtered by region:
SELECT c.name, o.total_cents
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.region = 'EMEA';EXPLAIN (ANALYZE, BUFFERS)
Nested Loop
(cost=8.61..4230.15 rows=60 width=40)
(actual time=0.31..8412.77 rows=284914 loops=1)
Buffers: shared hit=612345
-> Index Scan using customers_region_idx on customers c
Index Cond: (region = 'EMEA'::text)
(actual rows=412 loops=1)
-> Index Scan using orders_customer_id_idx on orders o
Index Cond: (customer_id = c.id)
(actual rows=691.54 loops=412)The planner estimated 60 joined rows and got 284,914 — a factor of nearly 5,000. Three reading habits catch this immediately. First, compare rows against actual rows at every node; when they diverge by orders of magnitude, the planner chose its plan under false assumptions. Second, remember that actual rows on an inner node is the average per iteration: 691.54 rows across 412 loops of the outer relation, so the real output is roughly 285,000 rows. Third, scan for the expensive node by actual time, which is inclusive of its children — the deepest node with the largest time is where the query actually spends its life.
Why did the wrong estimate hurt? With 60 expected rows, a nested loop — probing the orders index once per customer — looked cheap. At 284,914 real rows it means 284,914 individual index probes, with 612,345 buffer hits accumulated in consequence. A hash join would have scanned both relations once and built an in-memory hash table, orders of magnitude faster at this scale. The planner did not fail; it was fed wrong inputs. The classic causes: stale statistics after a data load (fix: run ANALYZE), or correlated columns whose independent-sampling assumptions fall apart (fix: CREATE STATISTICS extended statistics, covered in the same planner statistics chapter).
Sorts, Spills, and Buffers
Buffer counters distinguish an I/O-bound query from a CPU-bound one at a glance. shared hit means PostgreSQL found the page in its own cache; shared read means it had to ask the OS, which may or may not have hit disk. A plan full of shared read on a warm system points at a working set larger than shared_buffers, not at a bad plan.
Sort nodes deserve their own habit: check the sort method line.
Sort (cost=98123.44..99123.44 rows=400000 width=32)
(actual rows=400000 loops=1)
Sort Key: total_cents DESC
Sort Method: external merge Disk: 18712kB“External merge” means the sort exceeded work_mem and spilled to temporary files — BUFFERS will show the corresponding temp read and temp written counters. The fix is usually not a bigger work_mem for the whole server but a supporting index that returns rows pre-sorted, eliminating the sort node entirely.
A Practical Workflow
When a query is slow, the sequence that consistently works: run EXPLAIN (ANALYZE, BUFFERS) (with TIMING OFF for cheap-node-heavy queries, and wrapped in BEGIN/ROLLBACK for mutations). Find the node with the largest actual time. Check its row estimate against reality. If they disagree, fix the estimates — statistics before indexes. If they agree, the planner understood the query and chose the best plan it knew; the fix is structural: an index whose column order matches the filter and sort, a join rewrite that reduces the row volume crossing the join, or fewer columns fetched so heap accesses shrink. The performance tips chapter and the EXPLAIN usage guide expand on each of these.
The habit that separates people who tune queries from people who guess at them: never act on a plan you have only estimated. Run it, read the actuals, and let the misestimate tell you which fix to reach for first.