Plan Regression Detection in PostgreSQL #

A query that ran in 8 ms for a year now takes 4 seconds. Nothing was deployed, the data grew as it always does, and the plan is different. Plans change without warning because they are recomputed from statistics, settings and data that all move, and the first thing anyone wants in that situation is the plan from before — which almost nobody has.

This page covers making plan changes observable: what to record, how to detect a regression from statement statistics alone, and how to narrow the cause to statistics, data distribution, configuration or a version change. It builds on the capture methods in EXPLAIN options and output formats and the diagnosis techniques in identifying plan bottlenecks.

When Plans Change #

PostgreSQL has no stored plans and no plan baselines: every plan is derived, so anything feeding the derivation can change it.

Detection therefore has two halves: noticing that timing changed, and knowing what the plan was before.

Six causes, six pieces of evidence A table of causes and evidence. Statistics changes show in last_autoanalyze timestamps. Data growth shows in reltuples and relpages. Configuration changes show in the Settings line of a captured plan. Schema changes show in index catalogs. Version upgrades show in the server version. Plan cache transitions show in pg_prepared_statements counters. evidence where to find it statistics refreshed last_autoanalyze pg_stat_user_tables data grew reltuples, relpages pg_class settings differ Settings: line EXPLAIN (SETTINGS) schema changed index list pg_indexes version changed server_version SELECT version() generic plan took over generic_plans pg_prepared_statements record all six with every captured plan

Annotated Evidence #

A regression visible in statement statistics:

-- sampled hourly into a history table
SELECT queryid, calls, round(mean_exec_time::numeric, 2) AS mean_ms, rows / nullif(calls,0) AS avg_rows
FROM pg_stat_statements WHERE queryid = 8123456789012345678;
   sampled_at    | calls  | mean_ms | avg_rows
-----------------+--------+---------+----------
 2026-09-15 08:00| 412004 |    8.41 |       20
 2026-09-15 09:00| 408112 |    8.38 |       20
 2026-09-15 10:00| 401228 | 4102.60 |       20   ← same rows returned, 500× slower

Same rows, same call rate, 500× the time: the work changed, not the request. The next question is what the plan was, which is where a recorded baseline or auto_explain entry earns its keep:

-- auto_explain, before
Index Scan using orders_customer_placed_idx on orders  (actual rows=20 loops=1)
  Index Cond: ((customer_id = $1) AND (placed_at >= $2))

-- auto_explain, after
Bitmap Heap Scan on orders  (actual rows=20 loops=1)
  Recheck Cond: (customer_id = $1)
  Filter: (placed_at >= $2)
  Rows Removed by Filter: 1840220
  ->  Bitmap Index Scan on orders_customer_idx
-- the composite index is gone: dropped by a migration as "unused"

And the context that identifies the cause:

SELECT relname, last_autoanalyze, n_live_tup FROM pg_stat_user_tables WHERE relname = 'orders';
SELECT indexname FROM pg_indexes WHERE tablename = 'orders';
SHOW work_mem;
SELECT version();
What identifies a regression Three items are annotated. Mean execution time rising sharply while rows returned and call rate stay constant. A plan showing a different access path than the recorded baseline. A catalog check showing the index the old plan used is no longer present. mean 8.4 ms → 4102 ms, rows unchanged the work changed Index Scan → Bitmap Heap Scan + Filter different access path composite index absent from pg_indexes the cause without a baseline plan, the middle line is unavailable

Internals: Why There Is No Plan Stability Feature #

Some databases let you pin a plan to a statement. PostgreSQL deliberately does not: plans are always derived from current statistics and settings, on the grounds that a pinned plan is a plan that will eventually be wrong for the data. That places the responsibility for stability on the operator, and it makes detection rather than prevention the practical strategy.

What the system does provide:

Together they support a workflow: record plans for the statements that matter, sample statement statistics continuously, and when a change appears, compare the new plan against the recorded one with the six pieces of context above.

A related question is what to compare. Plan text differs between versions and between runs for cosmetic reasons — costs, row estimates, node ordering — so comparisons should be structural: node types, relation and index names, join conditions, and the presence of sorts and spills. JSON output makes that straightforward, as described in parsing EXPLAIN JSON output.

From signal to cause Four stages. Sample statement statistics on a schedule to detect timing changes. Retrieve the baseline plan from stored captures or auto_explain logs. Compare the new plan structurally against the baseline. Check the six context sources to identify which input changed. sample statistics detect the change fetch baseline plan stored or logged compare structurally node types, indexes check context which input moved the baseline is the step that must be prepared in advance

Ranking what is worth a baseline #

Recording baselines for every statement is neither necessary nor sustainable. Three groups cover almost every regression worth catching.

The first is the workload’s top statements by total execution time — calls × mean_exec_time, not mean alone. These dominate the server’s CPU and I/O, so a proportional change in any of them is felt immediately, and they are usually a few dozen statements even on a large application.

The second is statements that are business-critical regardless of cost: the checkout path, the login query, the report an executive runs every Monday. They may be fast and infrequent, but a regression in one is an incident whatever the server-wide numbers say.

The third is statements whose plans are known to be fragile — the ones with skewed predicates, correlated columns, parameterised ranges, or a history of flipping between index and sequential access. A statement that has regressed once will regress again, and it is the cheapest possible place to spend a baseline.

Everything else can be left to the statement-statistics trend, which will still show a change even without a stored plan; the baseline only shortens the diagnosis.

Refreshing baselines matters as much as taking them. A baseline recorded against data a year old describes a system that no longer exists, and comparing against it produces differences that are all expected and none informative. Re-recording quarterly, and always after a deliberate change — a new index, a settings change, a version upgrade — keeps the comparison meaningful. Storing them in version control alongside the schema makes the refresh part of an existing routine rather than a separate one that gets forgotten.

Reproducing a regression safely #

Once a regression is identified, the temptation is to experiment on production. A restored copy is better, but only if it is comparable: statistics, table sizes and settings must match, or the experiment reproduces a different problem. Restoring a physical backup preserves all three. A logical dump and reload does not — it discards statistics, rewrites indexes densely, and removes bloat, so the copy often performs better than production for reasons unrelated to the plan.

Where a copy is not available, two techniques narrow the question without risk. EXPLAIN without ANALYZE is free and shows the plan the planner currently chooses; comparing it with the baseline answers “has the plan changed?” without executing anything. And the enable_* switches, set for a single session, test a hypothesis directly: if disabling sequential scans restores the old shape and the old timing, the question becomes why the planner now prefers the scan, which is nearly always an estimate problem.

Neither technique should be left in place as a fix. A session-level enable_seqscan = off in application code is a plan pin by another name, and it will be wrong for some future data distribution exactly as the planner is wrong now.

Memory, Storage and Overhead Considerations #

pg_stat_statements keeps its entries in shared memory sized by pg_stat_statements.max (default 5000). Its overhead is small, but entry eviction hides statements, so keep the limit above the workload’s distinct statement count and avoid statement-text explosions — the problem in variable IN lists and statement cache bloat.

Sampling history costs storage: a row per tracked statement per sample. Sampling the top few hundred statements every fifteen minutes is a few megabytes a week and is enough resolution to place a regression within a deploy window.

auto_explain with log_analyze adds per-node instrumentation to every statement that exceeds the threshold, so the threshold matters: seconds, not zero. log_timing = off keeps row counts while removing most of the overhead on systems with slow clocks.

Storage of baselines is trivial — a few kilobytes of JSON per statement — and the value is disproportionate, because a baseline is the one artefact that cannot be reconstructed after the fact.

Step-by-Step Detection Workflow #

  1. Enable the sources. pg_stat_statements with a generous max, auto_explain with a threshold of a few seconds, track_io_timing on.

  2. Sample statement statistics on a schedule into a history table: queryid, calls, total_exec_time, rows, and the sample timestamp. Compute per-sample deltas, since the counters are cumulative.

  3. Record baseline plans for the statements that matter — the top statements by total time, plus anything business-critical — with EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT JSON) against production-like data, stored with the server version and the date.

  4. Alert on mean time changes that are not explained by a change in rows returned or call rate. A tripling of mean time with constant rows is a plan or data change.

  5. When alerted, compare structurally — node types, indexes used, join methods — rather than by text diff.

  6. Check the six context sources to name the cause: statistics timestamp, table size, settings line, index list, server version, plan cache counters.

  7. Fix at the source. Statistics problems get statistics fixes; configuration drift gets pinned settings; a dropped index gets recreated; a generic-plan transition gets plan_cache_mode.

  8. Re-record the baseline once the fix is in, so the next comparison starts from the current truth.

Fixing at the right layer #

A named cause maps to a layer, and fixing at the wrong one produces a repair that lasts until the next change.

Estimate causes get statistics fixes. A wrong row estimate from correlated columns is fixed with an extended statistics object; one from an expression predicate with expression statistics; one from an under-sampled skewed column by raising the per-column statistics target. These are covered in statistics and selectivity estimation. Adding an index on top of a wrong estimate sometimes helps by accident and leaves the estimate wrong.

Data-volume causes get schema or query fixes. A plan that was right at one million rows and wrong at fifty million usually needs a different index, a partitioning strategy, or a query that asks for less — not a planner adjustment.

Configuration causes get configuration fixes, applied where they belong. A work_mem that suits a nightly report should be set for that session or that role, not globally, and a setting inherited by an application role should be visible in deployment configuration rather than discovered in pg_db_role_setting.

Plan-cache causes get plan_cache_mode. A prepared statement whose generic plan is wrong for skewed parameters is forced to force_custom_plan for that statement, not for the whole system.

The discipline is simply to name the cause before choosing the fix. A regression diagnosed as “the planner got it wrong” has not been diagnosed; every plan choice follows from an input, and the input is what changed.

Common Pitfalls #

No baseline. The most common gap, and the one that makes every regression a guess. Diagnostic signal: “what did it look like before?” with no answer. Fix: record plans for top statements routinely.

Ranking by mean time only. A regression in a statement called millions of times matters more than one called twice. Diagnostic signal: attention on the slowest statement rather than the costliest. Fix: rank by total time and by delta.

Comparing plan text. Costs and estimates differ run to run. Diagnostic signal: diffs full of irrelevant numbers. Fix: compare structure from JSON.

Assuming a deploy caused it. Autoanalyze, data growth and plan-cache transitions all change plans with no deploy. Diagnostic signal: a regression with no correlated release. Fix: check the six context sources.

Chasing the symptom. Adding an index to fix a regression whose cause was a wrong estimate leaves the estimate wrong. Diagnostic signal: a fix that works until the next data change. Fix: name the cause before acting.

Letting pg_stat_statements evict. Entries vanish and history becomes meaningless. Diagnostic signal: statements disappearing between samples. Fix: raise max, fix statement-text explosions.

Testing the fix on a logical restore. A dump and reload has no bloat, fresh statistics and densely packed indexes, so the regression frequently will not reproduce. Diagnostic signal: “it is fine on staging”. Fix: reproduce on a physical copy, or reason from the plan and the statistics rather than from timings.

Frequently Asked Questions #

Does PostgreSQL support plan baselines or plan pinning? #

No. Every plan is derived from current statistics, settings and data, by design. Stability comes from detecting changes and fixing their causes, not from pinning plans.

How do I know a query’s plan changed? #

Sample pg_stat_statements over time: a large change in mean execution time with unchanged call rate and rows returned indicates the work changed. Confirm by comparing the current plan with a recorded baseline or an auto_explain entry.

What should I record as a plan baseline? #

EXPLAIN with ANALYZE, BUFFERS and SETTINGS in JSON format, stored with the server version, the date, and the table sizes and last-analyze timestamps for the relations involved. That is enough to identify which input changed later.

Why did a plan change without any deployment? #

Autoanalyze refreshing statistics, data crossing a size or distribution threshold, a prepared statement switching to a generic plan, or a configuration change applied at the role or system level. None of them involve application code.

Up: Reading & Interpreting Query Plans