Comparing PostgreSQL Plans Before and After a Change

An index is added and the query "feels faster". A rewrite goes in, and the reviewer asks for proof. Plan comparison is the proof, and it is easy to do badly: one run each, different cache states, different parameters, a gain of 12 percent declared a win. This article is a short protocol for comparing plans so the conclusion survives scrutiny. It belongs to the series that reading EXPLAIN ANALYZE output in PostgreSQL step by step opens, and it assumes you can already read a plan, as in how to read a PostgreSQL query plan step by step.

Capture the before plan first

Before changing anything, record the plan and the context:

\o plan_before.txt
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT c.name, count(*) FROM customer c JOIN app_order o ON o.customer_id = c.id
WHERE o.placed_at >= '2026-03-01' GROUP BY c.name ORDER BY 2 DESC LIMIT 10;
\o

\o file redirects psql output to a file. SETTINGS lists planner settings that differ from the defaults for this session, which exposes differences like a different work_mem or random_page_cost that would make a comparison unfair. Save more than the plan: the PostgreSQL version, the table sizes (pg_size_pretty(pg_total_relation_size(...))), the date of the last ANALYZE, the parameter values and the time. A plan without its context is hard to interpret next week.

Run it at least three times and keep all three. The first is often slower because of cold caches; the later ones show the warm steady state. Note the spread between runs, which tells you how much difference is noise.

Change one thing

Apply a single change: one index, one rewrite, one statistic, one setting. Two changes at once leave you unable to say which helped, and one may hurt while the other masks it. If you need both, test each alone and then together. For index changes, create the index in a transaction you can roll back, or on a copy of the database:

BEGIN;
CREATE INDEX app_order_placed_idx ON app_order (placed_at) INCLUDE (customer_id);
ANALYZE app_order;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ... ;
ROLLBACK;

CREATE INDEX inside a transaction locks writes to the table for its duration, so do this on a staging copy or a small table, not on a busy production primary. DDL is transactional in PostgreSQL, which is what makes the rollback possible.

After the change, re-run ANALYZE if statistics are involved, and capture the after plan three times in the same way.

What to compare

Execution time. The headline number, and the noisiest. Compare medians of warm runs, and treat differences under about 10 to 20 percent with suspicion unless the plan shape changed.

Total buffers. shared hit + read at the top node is the amount of data the query touched. It is stable across runs and measures work, so a drop from 400,000 to 12,000 blocks is meaningful even when the timing is noisy. Include temp blocks.

Estimated versus actual rows. Did the change fix an estimate? A plan that became faster while its estimates stayed wrong is fragile: it improved by luck or by a path that happens to be better, and may regress with the next data change.

Plan shape. Scan types, join methods, join order and whether a sort disappeared. A plan that changed shape for a good reason is the explanation of the gain. A plan with the same shape and a different time is likely noise or caching.

Rows removed by filter and heap fetches. Indicators of wasted work, which should fall if the change did what you intended.

Planning time. For short queries or ones with many partitions, a new index can increase planning time slightly. Check that the sum of planning and execution improved.

A comparison table is a good record:

Diffing plan text

For big plans, a text diff helps. Strip the numbers that always differ (times, costs) and diff the structure:

sed -E 's/\(cost=[^)]*\)//; s/\(actual[^)]*\)//; s/(Planning|Execution) Time:.*//' plan_before.txt > before.struct
sed -E 's/\(cost=[^)]*\)//; s/\(actual[^)]*\)//; s/(Planning|Execution) Time:.*//' plan_after.txt  > after.struct
diff -u before.struct after.struct

The diff shows nodes that changed, appeared or disappeared, which is the quickest way to spot that an unrelated join flipped to a different method. For repeated work, tools that compare two JSON plans visually exist, as in visualizing plans with explain.depesz.com and others.

Keep the data and parameters the same

A comparison on different data is not a comparison. Use the same database, restored from the same backup, or the same moment of a replica. Use the same parameter values; for a query whose behavior depends on them (a skewed column), test the rare large value and the common small one. For prepared statements, compare the generic and custom plans separately, because the application may use the generic one (EXPLAIN (GENERIC_PLAN) in version 16 and later).

Do not compare a production plan with a development plan unless the development database has production-like volume and distribution. A common trap is a plan on a laptop with a hundredth of the data, where a sequential scan wins and an index looks pointless, or the reverse.

Account for cache

Fast second runs are common, and cold runs matter in production after restarts, failovers or when the working set exceeds memory. Report both when they differ: warm for the steady state, cold for the worst case after a cache loss. To approximate a cold run for a test, restart the database (and, where you control the machine, drop the OS page cache). Do not claim a cold improvement from a warm run. Use BUFFERS reads: if the after plan has fewer read blocks, it will be better cold as well.

Also beware of other activity on a shared test server. A background VACUUM, a backup or another developer's query changes timing between runs. Repeat a comparison that looks surprising, and prefer the stable metrics.

Keep a regression guard

Once a query is fixed, protect the win. Keep the before and after plans in the pull request or the ticket. For critical queries, add a test that fails when the plan type regresses: run EXPLAIN (FORMAT JSON) in a test and assert that the plan contains the index scan and not a sequential scan on the big table, on a test database with representative data. Do not assert on cost numbers, which change with statistics. And turn on auto_explain in production so a regression produces a plan in the log.

When a single plan comparison proves nothing

One before and one after proves little when the query is cheap and variable, when the data distribution depends on parameters and you tested one value, when caching differed between the two runs, when concurrent load differs or when the change affects a plan only at larger scale than your test. It also proves nothing about writes: an index that speeds a read adds work to every insert and update, so measure the write path too (EXPLAIN (ANALYZE, WAL) on a representative insert, or a short benchmark). And a faster plan is not an improvement if it uses far more memory or CPU per execution at the concurrency you run. Decide the success criterion before measuring: a target latency, a block count, a throughput, a cost. Then the comparison answers a question and not a hope.

What to do next

Take the last performance change you made without a recorded comparison and redo it with this protocol on a restored copy. The manual documents the options in EXPLAIN, and the model for planner settings in query planning. For the visual tools that help with this, see visualizing plans with explain.depesz.com and others.