Postgres Query Plan Changed With No Deploy and p99 Tripled

A Postgres query plan is the execution strategy the planner picks for a statement, such as which indexes to use, which join method and in what order. The planner chooses it from statistics and settings, so the plan can change while the SQL stays byte for byte the same.

We see the same scene often. p99 latency on a core service triples, the deploy pipeline has been quiet for days, and the slow query matches the repository exactly. The first hour goes on looking for a code change that does not exist.

In this post we cover why an unchanged query gets a new plan, what to capture before fixing anything, the three mechanisms with a confirming check and a fix for each, why none of them pages anyone, a standing checklist, and when a new plan is the planner doing its job.

Why an Unchanged Query Can Get a New Plan

Debugging usually starts with what changed. For a plan regression, the honest answer is often the data or the planner's view of it, and neither appears in a git log.

The planner estimates how many rows each step will return and picks the cheapest plan for those estimates. When estimates drift from reality, a plan that suited a thousand rows gets applied to a million, and a fast index lookup becomes a slow nested loop.

That is why we treat a plan regression as its own incident type. The fix is rarely in the application, and rolling back a deploy that did not cause it only burns time.

Capture the Bad Postgres Query Plan Before You Fix Anything

Plans are ephemeral. Running ANALYZE or restarting the application often makes the bad plan disappear, along with any proof of why it happened, so we capture it first.

• For a statement we can rerun safely, EXPLAIN (ANALYZE, BUFFERS) shows estimated and actual rows side by side.
• For a prepared statement, EXPLAIN EXECUTE shows the plan the session would use, and parameter symbols such as $1 in the output mark a generic plan.
• For production, the auto_explain module provides "a means for logging execution plans of slow statements automatically," so the bad plan is already in the logs when the page arrives.

We save the captured plan with the incident record. Without it, the postmortem can only guess which mechanism was involved. Root cause analysis for a plan regression is mostly a comparison of the plan before and after, so the captured plan is the single most valuable piece of evidence.

Three Ways Postgres Changes the Plan Under an Identical Query

We check these three mechanisms in order, because the first is the most common and the cheapest to confirm.

1. Statistics Went Stale After Heavy Writes

The planner's row estimates come from table statistics that autovacuum refreshes with ANALYZE. The autovacuum settings docs give the trigger. autovacuum_analyze_threshold defaults to 50 rows and autovacuum_analyze_scale_factor to 0.1, which is 10% of the table.

On a 100-million-row table, that means about 10,000,050 rows must change before an automatic analyze. A bulk load of five million rows can reshape the data distribution without crossing it, and even after crossing it the table waits for one of the three default autovacuum workers, and the launcher visits each database about once a minute by default.

SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

The confirming check is an EXPLAIN ANALYZE where estimated rows differ from actual rows by orders of magnitude, alongside a last analyze time from before the bulk change. The fix is a manual ANALYZE, a lower analyze scale factor on large hot tables, and an explicit ANALYZE at the end of every bulk load or migration.

2. The Sixth Execution Switches to a Generic Plan

Prepared statements follow a rule described in the PREPARE documentation. "The first five executions are done with custom plans and the average estimated cost of those plans is calculated." After that, a generic plan built without the parameter values is used if its cost is not much higher.

That is a problem for skewed data. A generic plan may assume a filter matches a small share of rows while one popular value matches half the table. The query is fast in staging and for the first few calls after a connection opens, then slow from the sixth call onwards.

To reproduce it in psql, we prepare the statement, execute it five times with ordinary values, and then run EXPLAIN EXECUTE with the skewed value. If the sixth plan shows $1 and a very different shape, the generic plan switch is confirmed.

SET plan_cache_mode = force_custom_plan;

We scope that setting as narrowly as we can, to a session or a role, because every execution then pays the cost of planning. Drivers, ORMs and connection poolers often prepare statements without saying so, so we check what the client actually sends before blaming the database.

3. The Predicate Stopped Matching the Partition Key

Partitioned tables are fast because the planner skips partitions that cannot match. That only works when the query's predicate lines up with the partition key, and a changed type, an added cast or a function wrapped around the key can quietly stop it.

When pruning stops, Postgres raises no error. It scans every partition, and run time grows with the whole retained history instead of the requested range. The partitioning docs note that partitions pruned at execution time show up as "Subplans Removed" in EXPLAIN output, which makes the confirming check a plan that lists far more partitions than the filter needs.

Proving pruning across engines is a topic of its own, which we cover in partition pruning failure diagnosis. For all three mechanisms, the evidence lives in the database's statistics, its logs and the code that issued the query. It helps to connect an investigator read-only to your database and logs so the comparison does not depend on someone remembering which view to query.

Why None of These Changes Page Anyone

All three mechanisms are the database behaving as designed. There is no error in the log, no crashed process and no failed health check, so nothing alerts except latency.

Even latency can hide it. A regression on one query moves the tail long before it moves the average, which is why we watch percentiles per endpoint rather than one service-wide number, as we explain in the p99 latency field guide.

Bloat makes the first mechanism worse. Tables held back by long-open transactions accumulate dead rows that skew statistics, which is one reason we also watch idle in transaction sessions.

A Standing Checklist Against Plan Instability

We keep these five practices in place on any database where plan changes have hurt before.

1. Enable auto_explain with a duration threshold, so bad plans are logged when they happen.
2. Watch rows modified since the last analyze on large, frequently written tables.
3. Know which clients prepare statements, and test hot queries past their sixth execution.
4. Assert the number of partitions scanned for key queries in tests, since a missing prune raises no error.
5. Run ANALYZE explicitly after bulk loads, backfills and migrations.

The autovacuum side of this deserves its own procedure, and ours starts from the symptom rather than the settings, as described in the autovacuum decision procedure.

When a Plan Change Is the Planner Doing Its Job

We do not treat every new plan as a regression. As tables grow, a sequential scan can genuinely become cheaper than an index scan, and a plan change after fresh statistics is often an improvement.

Forcing the old plan back with hints, settings or extensions fixes today's latency at the cost of tomorrow's. A plan pinned for last year's data distribution can be wrong for next year's, and the pin is easy to forget.

So we confirm the mechanism before acting. If estimates match actual rows and the new plan is slower, the cost model or the data has a real story to tell, and that story is worth more than a pinned plan.

Treat Every Unexplained Postgres Query Plan Change as Evidence

We would capture the plan, compare estimated with actual rows, check for a generic plan, and count partitions scanned, in that order, before changing any setting. Most plan regressions with no deploy end at one of those three checks.

When the cause still is not clear, Operate can help find it. It brings the evidence from logs, databases and code together through read-only adapters, and proposes any code fix as a patch file an engineer reviews and applies.