Query plan regression

The planner is allowed to change its mind

PostgreSQL does not store plans. Every statement is costed against the statistics that exist at the moment it is planned, and the cheapest candidate wins. That is the right design, and it means the plan for a statement is a function of the data, not of the code. Nothing has to be deployed for it to change.

A regression happens when a boundary is crossed. The table grew past the point where a sequential scan costs less than an index scan, or the statistics stopped describing the data, or the value bound to a parameter this time is much less selective than the value bound last time. The statement is byte-identical before and after. The execution is an order of magnitude apart.

Diagnosing one has three parts: prove the timing actually changed, prove the plan is what changed, and find the input that moved.

What the server remembers, and for how long

Before any of that, an inventory, because it decides which questions are answerable at all.

pg_stat_activity knows what is running right now, including the statement text and what it is waiting on, and it does not know the plan. There is no supported way to ask a running backend which plan it chose. What you can do instead, in about ten seconds, is run a plain EXPLAIN of the same statement with the same parameter values from another session: that gives you the plan the planner would pick now, which is not proof of what the slow execution did and is usually the same thing.

pg_stat_statements knows totals per normalised statement since its counters started. Calls, rows, timing, blocks. It carries no plan and it keeps no history at all: there is one row per statement and it is overwritten in place, so the shape of last week exists only if something copied it somewhere.

The log knows whatever auto_explain was configured to write at the moment the statement ran, and nothing retroactive. This is the part that hurts at three in the morning. The evidence available during an incident was decided weeks earlier by somebody who was not thinking about incidents.

Three preparations cost almost nothing and turn all three of those into answers. Load the statistics extension. Snapshot it on a schedule. Configure plan logging with a threshold high enough that it is quiet on a normal day. The rest of this page assumes the first two and spends some care on making the third cheap.

Proving the timing changed

pg_stat_statements is the only practical way to see this after the fact, and it needs to be loaded through shared_preload_libraries, so a server restart is involved if it is not already there.

Its counters are cumulative since the last reset, which means a single query against it tells you what has been expensive since the server started, not what is expensive now. Two snapshots and a subtraction give you the window you actually care about:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

CREATE TABLE plan_baseline AS
SELECT now() AS captured_at, userid, dbid, queryid, calls, total_exec_time,
       rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements;

On PostgreSQL 17 two things in that view shifted under this technique: the block-timing columns took a scope into their names, and every row now records when it started counting, which is the denominator this snapshot pair has always had to assume.

Leave that for an hour, or take it before a release, then compare:

SELECT s.queryid,
       s.calls - b.calls                                   AS calls_in_window,
       round(((s.total_exec_time - b.total_exec_time)
              / nullif(s.calls - b.calls, 0))::numeric, 2) AS mean_ms_now,
       round((b.total_exec_time / nullif(b.calls, 0))::numeric, 2)
                                                           AS mean_ms_before,
       (s.shared_blks_read - b.shared_blks_read)
         / nullif(s.calls - b.calls, 0)                    AS blocks_read_per_call,
       left(s.query, 70)                                   AS query
FROM pg_stat_statements s
JOIN plan_baseline b USING (userid, dbid, queryid)
WHERE s.calls > b.calls
ORDER BY (s.total_exec_time - b.total_exec_time) DESC
LIMIT 20;

blocks_read_per_call is the column that separates a plan regression from ordinary load. If the mean time doubled and the blocks read per call doubled with it, the query is doing more work for the same answer, which is what a lost index access looks like. If the time doubled and the blocks per call did not move, the query is doing the same work more slowly, and the problem is contention or I/O, not planning.

Two version notes on this view. The block timing columns were renamed in PostgreSQL 17: what used to be blk_read_time and blk_write_time are now shared_blk_read_time and shared_blk_write_time, with local_blk_* and temp_blk_* counterparts alongside them. Version 17 also added stats_since and minmax_stats_since, so you can reset the minimum and maximum without losing the totals by passing minmax_only to pg_stat_statements_reset().

The query that is fast and slow at the same time

A plan that flips does not necessarily look slow. It looks like two queries wearing one name, and the average of the two is a number that describes neither. Averages are what almost every dashboard shows, which is why this class of problem is usually reported by a person rather than caught by a monitor.

Variance is what gives it away, and the statistics extension has been reporting variance for a long time without many people reading it:

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 1)   AS mean_ms,
       round(stddev_exec_time::numeric, 1) AS stddev_ms,
       round(min_exec_time::numeric, 1)    AS min_ms,
       round(max_exec_time::numeric, 1)    AS max_ms,
       round((max_exec_time / nullif(mean_exec_time, 0))::numeric, 1) AS max_over_mean,
       left(query, 60) AS query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY stddev_exec_time DESC
LIMIT 20;

A standard deviation at or above the mean says the samples are not one population. Something about the execution differs between calls, and the candidates are a small list: two plans, two very different parameter values, or a wait that only sometimes happens. That is not proof of a plan flip, because a lock storm produces the same signature. What it is, is a filter that turns a list of a thousand statements into a list of five worth explaining, and it costs one query.

The awkwardness is that min_exec_time and max_exec_time are lifetime extremes. One bad Tuesday poisons the maximum for as long as the counters live, and before PostgreSQL 17 the only way to clear it was a full reset, which also throws away the totals that let you compare windows. Version 17 separated the two. pg_stat_statements_reset(minmax_only => true) clears just the extremes, minmax_stats_since records when that last happened, and the pair makes a nightly reset a reasonable thing to schedule:

SELECT queryid, calls, stats_since, minmax_stats_since,
       round(max_exec_time::numeric, 1) AS max_ms
FROM pg_stat_statements
ORDER BY max_exec_time DESC
LIMIT 5;

SELECT pg_stat_statements_reset(minmax_only => true);

After that, the maximum on a row means “the worst this statement did today” rather than “the worst it has ever done”, and a maximum far above the mean becomes something you can alert on instead of something you have to interpret.

Proving the plan changed

Timing evidence is circumstantial. The plan is the evidence, and auto_explain is how you capture it without being logged in at the right moment:

session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '2s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.sample_rate = 0.05

log_analyze makes each logged plan carry real row counts beside the estimates, which is the whole point: a plan with an estimate of a hundred rows and an actual of four hundred thousand tells you exactly where the planner was misled. It also means the statement is instrumented, which costs real time, so sample rather than logging everything. On PostgreSQL 18 buffer numbers appear in EXPLAIN ANALYZE without asking, which is a small quality-of-life change that makes a lot of pasted plans more useful.

Most of the cost of instrumentation is not the row counting. It is the clock. Per-node timing takes two readings per node execution, and on a machine whose clock source is slow that dominates everything else. auto_explain.log_timing turns it off on its own, leaving the actual row counts and loop counts intact, and row counts are all the estimate error needs. The server ships pg_test_timing so you can find out what a reading costs on that hardware before deciding; on a host where it is expensive, timing off and sampling up is a better trade than the reverse.

One limit worth stating plainly, because it disappoints people during an incident: auto_explain logs when the statement finishes. A query that is still running has not been logged and will not be until it ends. For what is happening right now, the wait events in active session history are the tool, and the plan is a question for afterwards.

Interactively, there is a form worth memorising, and the rest of this page uses a small fixture to show it working. Anything below runs on a scratch database in a few seconds:

CREATE TABLE public.orders (
  order_id    bigserial   PRIMARY KEY,
  city        text        NOT NULL,
  postcode    text        NOT NULL,
  customer_id integer     NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO public.orders (city, postcode, customer_id, created_at)
SELECT 'city_' || (g % 40),
       'pc_'   || (g % 40),
       g % 10000,
       now() - ((g % 2592000) * interval '1 second')
FROM generate_series(1, 300000) g;

CREATE INDEX orders_created_at_idx ON public.orders (created_at);
ANALYZE public.orders;

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE)
SELECT customer_id, count(*)
FROM public.orders
WHERE created_at >= now() - interval '7 days'
GROUP BY customer_id;

SETTINGS prints any planner-related setting that differs from its default, which catches the case where one connection pool has enable_seqscan turned off or a different work_mem and the others do not.

Reading the plan for the estimate that moved

A plan is long and only one part of it is the answer. Four habits get you to that part.

Actual row counts are per loop. A node reporting one row and half a million loops returned half a million rows, and the multiplication is yours to do. This is the single most common misreading of EXPLAIN ANALYZE, and it is the one that hides a nested loop that should never have been chosen, because the node at the bottom of it looks tiny.

Find the deepest node where the estimate and the actual diverge by an order of magnitude, and stop reading above it. Estimation error propagates upward and multiplies on the way, so a join whose estimate is out by a thousand usually contains two scans that are each out by thirty. Fixing the top node is fixing a symptom; the bottom one is where the planner was told something untrue.

An estimate of exactly one row is often not an estimate. The planner clamps a relation’s row count to a minimum of one, so “rows=1” on the inner side of a join can mean “somewhere between nothing and one”, and that floor is precisely what makes a nested loop look free. Treat a one-row estimate feeding a loop as a suspect rather than a fact.

Rows Removed by Filter is the difference between a selective index and an index that only got you to the right neighbourhood. A scan that reads two hundred thousand rows to return four hundred is doing index work and sequential-scan volume at the same time, which is the worst of the two.

The classic way to make the planner wrong is to give it two columns it believes are independent:

EXPLAIN (ANALYZE, SUMMARY OFF)
SELECT count(*) FROM public.orders
WHERE city = 'city_7' AND postcode = 'pc_7';

CREATE STATISTICS orders_city_postcode (dependencies, ndistinct, mcv)
  ON city, postcode FROM public.orders;
ANALYZE public.orders;

EXPLAIN (ANALYZE, SUMMARY OFF)
SELECT count(*) FROM public.orders
WHERE city = 'city_7' AND postcode = 'pc_7';

In that fixture the postcode is a function of the city, so the second filter selects exactly the rows the first one already did. The planner multiplies the two selectivities anyway and expects something near a fortieth of what it actually finds. With the extended statistics in place the same estimate lands within about a quarter of the real count. On a two-column count the wrong estimate costs nothing at all; feed it into a join and the same factor is the difference between a hash join and a nested loop running thousands of times.

That plan is also a free demonstration of the first habit above. Both versions come back parallelised, so the scan node reports its rows per worker with a loop count beside them, and the number you have to compare against the estimate is the product rather than either figure on its own.

Why it moved

Five causes cover nearly everything, and they are distinguishable.

The statistics are stale. The planner is costing against a table it thinks is a tenth of its real size. This is common after a bulk load, and it is the first thing to rule out:

SELECT relid::regclass AS table_name,
       n_live_tup, n_mod_since_analyze,
       last_analyze, last_autoanalyze
FROM pg_stat_all_tables
WHERE n_mod_since_analyze > 10000
ORDER BY n_mod_since_analyze DESC;

An ANALYZE on the named table either fixes it or eliminates it as a cause, in under a minute either way.

The columns are correlated and the planner assumes they are not. Independence is the default assumption, so a filter on city and postcode is estimated as the product of two selectivities and comes out far too small. The fixture above is that failure in miniature, and CREATE STATISTICS is the fix. The related trap is the distinct-value estimate itself, which is sampled rather than counted and can be badly wrong on a column with many rare values; n_distinct can be set by hand on a column where the sample will never get it right.

A generic plan was chosen for a parameter that deserved a custom one. A prepared statement is planned with its actual parameter values for its first several executions, and then the server compares and usually settles on one plan for all values, which the glossary sets out step by step. Two details decide whether this is your problem. The comparison is between estimated costs and never between measured times, although the server has just measured five real executions and could have used them, so a generic plan that costs cheaply and runs badly wins permanently and nothing ever revisits it. And the decision is per session, so behind a pooler each server connection keeps its own plan cache and the same statement can be running a custom plan on one connection and a generic plan on another at the same instant. That is the real story behind “it is only slow on some of the pods”, and it is why the plan you capture by hand may not be the plan anyone is complaining about; connection pooling is where that attribution problem lives. SET plan_cache_mode = force_custom_plan proves it in one step.

A cost boundary moved. This one has arithmetic behind it and the arithmetic is worth knowing, because it turns a setting most people copy into a setting they can reason about. A sequential scan pays seq_page_cost, which is 1, for every page in the table. An index scan pays random_page_cost, which is 4, for every heap page it fetches, plus the index pages on top. Ignoring the index itself, an index path therefore stops winning at around a quarter of the table’s pages, because four times a quarter is one. Move random_page_cost to the 1.1 that flash storage deserves and the crossover moves out to something near nine tenths of the table, which is why that one setting changes more plans than everything else in this section combined.

Two caveats on that sum. Pages are not rows: if the rows you want sit next to each other on disk, fetching a tenth of the rows may touch a fiftieth of the pages, and the planner accounts for this through the physical correlation it records per column, which is why a freshly rebuilt table plans differently from the same table six months later. And effective_cache_size allocates nothing at all; it tells the planner how much of a table it may assume is already cached when pages are fetched repeatedly, so a default left in place on a large machine makes repeated index access look more expensive than it is. work_mem set too low is the other member of this family, turning a hash join into a nested loop or spilling a sort to disk, and log_temp_files set to zero puts the spills in the log where you can count them.

The statistics were thrown away by an upgrade. Before PostgreSQL 18, pg_upgrade carried no optimizer statistics across the major version boundary, so a freshly upgraded cluster planned every query blind until something analysed it. Version 18 preserves them, with the exception of extended statistics, which still need rebuilding. There is more on that in pg_upgrade and extensions.

Keeping the answer

Once you know which plan you want, there are three ways to hold onto it, in descending order of how much we would recommend them.

Fix the input. Add the missing statistics object, analyse the table on a schedule that matches its churn, or add the index that makes the good plan obviously cheapest. This is the only fix that survives the next data shift.

Adjust a cost setting at the smallest scope that works: the session, the role, or the database. A random_page_cost that is wrong for your storage is worth fixing globally; anything narrower than that is a signal you are compensating for something else.

Disable a plan type with enable_nestloop = off or a sibling. These are debugging aids and they are blunt: they do not make the good plan cheaper, they make one family of alternatives absurdly expensive, and they will eventually cost you a plan you wanted. If one ends up in production configuration, write down why and when to revisit it.

The identifier under all of this

Everything above is keyed on queryid, so it is worth knowing what that number is made of. It is a hash of the parsed statement rather than of its text, which is what lets two calls with different literals collapse into one row, and what goes into the hash includes the object identifiers of the tables involved rather than their names. Drop and recreate a table, restore a dump into a fresh cluster, or rebuild a replica through logical replication, and every statement touching that table hashes to something new while its text has not changed by a character. A baseline table keyed on queryid then stops joining, and because the join is an inner one the report comes back empty rather than wrong, which is the better of the two failures and still costs an afternoon. What is being grouped, and what is not is worth reading before building anything on top of it, as is when the identifier became a core feature rather than an extension’s private business.

Major versions break it too. PostgreSQL 18 changed how constant lists are normalised, so statements that were tracked separately may now collapse together and the reverse; anything keyed on the identifier starts again from nothing at that boundary. We wrote about that alongside the other silent changes in PostgreSQL 18 moved the WAL I/O counters. The practical consequence is small and easy to forget: capture a fresh baseline on the far side of an upgrade before you need one, because the comparison you would want to make is against a number that no longer exists.