Comparing Plans Before and After Upgrades #

A major version upgrade improves most queries and makes three of them ten times slower. Nobody knows which three until users report them, because the upgrade plan covered replication, extensions and downtime — but not plans. Capturing a corpus of plans before the upgrade turns that discovery from a week of incidents into an afternoon of comparison.

Detecting regressions in general is covered in plan regression detection. This page is about the version-change case, which is both the most predictable and the easiest to prepare for.

The Condition #

A major upgrade changes the planner in three ways that matter:

Two things do not change automatically: statistics carried over by pg_upgrade are discarded, so the new cluster starts with none until ANALYZE runs; and index on-disk formats stay as they were, so B-tree deduplication is unavailable until a REINDEX, as covered in B-tree deduplication and index size.

Most reported “upgrade regressions” turn out to be the first of those: plans made without statistics, because nobody ran ANALYZE after pg_upgrade.

Where plan work fits into an upgrade A timeline. Before the upgrade, capture a plan corpus on the old version. Run pg_upgrade. Immediately afterwards, run ANALYZE across the database, since statistics are not carried over. Reindex B-tree indexes to enable newer on-disk features. Then re-capture the corpus and compare structurally. before capture plan corpus pg_upgrade statistics discarded ANALYZE rebuild statistics REINDEX new index format re-capture compare structurally skipping ANALYZE produces regressions that look like planner changes

Annotated Evidence #

A corpus entry, captured on the old version:

-- for each statement in the corpus, on the old cluster
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON)
SELECT customer_id, sum(total) FROM orders WHERE placed_at >= $1 GROUP BY customer_id;
-- stored with: server_version, captured_at, reltuples for each relation, last_analyze
{"Plan": {"Node Type": "Finalize GroupAggregate", "Plans": [{"Node Type": "Gather Merge", …}]},
 "Settings": {"work_mem": "64MB", "hash_mem_multiplier": "1"}, "Execution Time": 9204.6}

After the upgrade, before ANALYZE:

Finalize GroupAggregate  (cost=… rows=200 …) (actual rows=3812044 loops=1)
  ->  Sort  (actual rows=3812044 loops=1)
        Sort Method: external merge  Disk: 4102208kB
-- rows=200: the default distinct-count estimate; no statistics exist yet

After ANALYZE:

Finalize HashAggregate  (cost=… rows=3790000 …) (actual rows=3812044 loops=1)
  Planned Partitions: 32  Batches: 33
-- estimates restored; the plan matches the old version's shape

And a genuine version difference, with the new default:

-- PostgreSQL 14, hash_mem_multiplier = 1.0
Hash  Batches: 8  Memory Usage: 64998kB

-- PostgreSQL 15+, hash_mem_multiplier = 2.0 (new default, only if not pinned in the config)
Hash  Batches: 4  Memory Usage: 129996kB
-- the plan is the same shape; the memory budget changed
Three post-upgrade signals Three items are annotated. Default-looking estimates immediately after an upgrade indicate missing statistics rather than a planner change. A carried-over configuration value shown in the Settings line explains behaviour that differs from a fresh install. A new node type in the plan indicates a genuine planner capability change. rows=200 right after pg_upgrade statistics not yet built Settings: hash_mem_multiplier = '1' old config carried over new node type (e.g. Incremental Sort) a real planner change run ANALYZE before concluding anything about the planner

Step-by-Step Resolution #

  1. Build the corpus before the upgrade. Take the top statements by total time from pg_stat_statements, plus business-critical ones, and capture each with EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON) on the old version. For parameterised statements, capture GENERIC_PLAN too so the comparison does not depend on chosen values.

  2. Record the context with each capture: server version, date, relation sizes, last-analyze timestamps, and the settings line.

  3. After pg_upgrade, run ANALYZE across the database before measuring anything. vacuumdb --all --analyze-in-stages builds usable statistics quickly, then refines them.

  4. Reindex B-tree indexes where deduplication and newer index features matter, using REINDEX CONCURRENTLY in batches.

  5. Re-capture the corpus on the new version under comparable data and settings.

  6. Compare structurally, not textually: node types, relations and indexes used, join methods, presence of sorts and spills, and execution time. A small script over the JSON does this reliably.

  7. Triage the differences. Improvements need no action. Regressions get the standard causes checked: statistics, changed defaults carried over from the old configuration, and new node types whose estimates may be poor on your data.

  8. Re-baseline after triage, so the corpus reflects the new version.

Triaging 240 captured statements Of 240 statements, 186 were unchanged in shape, 41 improved, 9 regressed because statistics had not been rebuilt, 3 regressed because a configuration value was carried over, and 1 was a genuine planner regression needing a query change. unchanged 186 statements improved 41 statements regressed: no ANALYZE 9 statements regressed: old config 3 statements genuine regression 1 statement twelve of the thirteen regressions were operational, not planner changes

Before and After #

-- BEFORE: upgrade with no plan corpus
three unknown queries regress; discovered by users over the following week

-- AFTER: corpus captured, ANALYZE run, structural comparison
240 statements compared in an afternoon; one genuine regression fixed before release

Making the comparison cheap #

The corpus does not need to be large. Twenty to fifty statements covering the workload’s shapes — point lookups, list pages, reports, batch writes — catch most planner-relevant differences, and they can be captured with a script that reads statement texts from pg_stat_statements and runs EXPLAIN against a staging clone.

The comparison itself is a structural diff over JSON: extract each node’s type, relation and index names in order, and compare the resulting sequence. Differences in costs, row estimates and timings are expected and should not be flagged; differences in node types or index usage should. Fifty statements compared this way is minutes of work, and it converts an upgrade from a hope into a check.

Where an upgrade is high-stakes, the same corpus supports a rehearsal: restore a copy, upgrade it, run the comparison, and fix what it finds before touching production. That is also the moment to test the newer features deliberately — for example whether raising hash_mem_multiplier to the new default improves the spilling reports, rather than leaving the old value pinned by inheritance.

Minor versions and extension upgrades #

Minor releases do not change the planner’s behaviour deliberately, and they never require a dump and reload, so a corpus comparison is usually unnecessary for them. They can still change plans indirectly, because a restart empties shared buffers and resets every prepared statement, which means the first hours after a minor upgrade show cold-cache timings and custom plans where generic ones had taken over. Both effects fade; neither is a regression, and mistaking them for one is a common way to spend an afternoon.

Extension upgrades deserve more attention than minor releases. An extension that provides operators, index access methods or functions participates directly in planning: a changed function COST, a new operator class, or a different selectivity estimator changes plan choice for every query that touches it. PostGIS, pg_trgm and vector extensions are the usual examples. When one of those is upgraded, the corpus should cover the statements that use it, even if the server version is unchanged.

Common Pitfalls #

Skipping ANALYZE after pg_upgrade. Every plan is built without statistics. Diagnostic signal: default-looking estimates everywhere. Fix: vacuumdb --analyze-in-stages immediately after the upgrade.

Carrying the old configuration wholesale. New defaults never take effect. Diagnostic signal: Settings: showing values that match the old version’s defaults. Fix: review changed defaults for each major version.

Comparing plan text. Output changes create noise. Diagnostic signal: diffs dominated by new fields. Fix: structural comparison from JSON.

Capturing after the upgrade only. There is nothing to compare against. Diagnostic signal: “was it always like this?” Fix: capture the corpus before.

Frequently Asked Questions #

Does pg_upgrade carry over statistics? #

Historically no — statistics are discarded and must be rebuilt with ANALYZE, which is why plans immediately after an upgrade are often poor. Always run ANALYZE, ideally with vacuumdb’s staged mode, before assessing performance.

How should I compare plans across versions? #

Structurally: compare node types, relations and indexes used, and join methods, taken from JSON output. Costs, estimates and new output fields differ between versions for reasons unrelated to plan quality.

Do I need to reindex after a major upgrade? #

Not for correctness in general, but B-tree indexes keep their old on-disk format, so features such as deduplication are unavailable until they are rebuilt. Reindexing large B-trees concurrently after an upgrade is usually worthwhile.

Up: Plan Regression Detection