Tracking Plan Changes with pg_stat_statements #

Latency alerts fire when a query gets slower, which is the right signal and the wrong metric: they also fire when traffic doubles, when rows returned grow, and when the cache is cold after a failover. The metric that isolates a plan change is narrower — time spent per row returned — and it can be computed entirely from pg_stat_statements samples, without touching the application.

The broader detection strategy is in plan regression detection. This page is the sampling implementation.

The Condition #

pg_stat_statements counters are cumulative since the last reset. A single reading is almost useless; two readings a known interval apart give rates:

A plan regression changes the last metric while leaving rows per call unchanged: the query asks for the same thing and the server does more work to produce it. A traffic increase changes call rate but not time per row. A query that starts returning more rows changes rows per call, which explains a latency increase without any plan change.

Two more columns sharpen the picture: shared_blks_read per row (an I/O efficiency measure that catches cache and plan changes) and, with track_planning enabled, total_plan_time per call.

Sampling needs care with resets: counters drop to zero on pg_stat_statements_reset() and on server restart, so a negative delta means a reset and the interval should be discarded rather than treated as a rate.

Four rates, four different signals A table. Calls per second detects traffic changes. Milliseconds per call detects latency changes of any cause. Rows per call detects a change in what the query asks for. Milliseconds per row isolates efficiency changes, which is what a plan regression looks like. formula detects calls/sec Δcalls ÷ Δt traffic ms/call Δtime ÷ Δcalls latency, any cause rows/call Δrows ÷ Δcalls what is requested ms/row Δtime ÷ Δrows efficiency: plan changes alert on the last one, with the third as a guard

Annotated Evidence #

A minimal history table and sampler:

CREATE TABLE stmt_history (
  sampled_at   timestamptz NOT NULL DEFAULT now(),
  queryid      bigint      NOT NULL,
  calls        bigint      NOT NULL,
  total_time   double precision NOT NULL,
  rows         bigint      NOT NULL,
  blks_read    bigint      NOT NULL,
  PRIMARY KEY (sampled_at, queryid)
);

INSERT INTO stmt_history (queryid, calls, total_time, rows, blks_read)
SELECT queryid, calls, total_exec_time, rows, shared_blks_read
FROM pg_stat_statements
WHERE queryid IS NOT NULL
ORDER BY total_exec_time DESC
LIMIT 500;                    -- keep the history small and relevant

Rates from consecutive samples:

SELECT sampled_at, queryid,
       calls - lag(calls) OVER w                                    AS d_calls,
       round((total_time - lag(total_time) OVER w)::numeric, 1)     AS d_ms,
       rows - lag(rows) OVER w                                      AS d_rows,
       round(((total_time - lag(total_time) OVER w) /
              nullif(rows - lag(rows) OVER w, 0))::numeric, 4)      AS ms_per_row
FROM stmt_history
WHERE queryid = 8123456789012345678
WINDOW w AS (PARTITION BY queryid ORDER BY sampled_at)
ORDER BY sampled_at DESC LIMIT 5;
     sampled_at      | d_calls |   d_ms   | d_rows | ms_per_row
---------------------+---------+----------+--------+------------
 2026-09-15 11:00:00 |  101204 | 415104.2 | 2024080|     0.2051   ← 24× worse
 2026-09-15 10:00:00 |  100112 |  17204.6 | 2002240|     0.0086
 2026-09-15 09:00:00 |  102004 |  17510.2 | 2040080|     0.0086
-- calls and rows per call unchanged; time per row jumped: a plan or data change

Corroborating with I/O:

 blks_read per row: 0.004 → 1.82
-- the new plan reads a page per row: an index scan replaced by a heap-heavy path
Which metric moved? Three items are annotated. Delta calls unchanged rules out a traffic change. Delta rows per call unchanged rules out the query returning more data. Milliseconds per row jumping by a factor of 24 identifies an efficiency change, corroborated by blocks read per row. d_calls ≈ 101,000 both intervals traffic unchanged d_rows ÷ d_calls ≈ 20 both intervals same rows requested ms_per_row 0.0086 → 0.2051 efficiency collapsed blocks read per row corroborates: 0.004 → 1.82

Step-by-Step Resolution #

  1. Install and size pg_stat_statements. Set pg_stat_statements.max above the workload’s distinct statement count so entries are not evicted.

  2. Sample on a schedule — every five to fifteen minutes — into a history table, keeping only the top statements by total time to bound storage.

  3. Compute per-interval deltas with window functions, discarding intervals where any counter decreased (a reset or restart).

  4. Alert on time per row rising beyond a threshold — a factor of three is a reasonable starting point — while rows per call is stable. That combination is what distinguishes a plan change from a workload change.

  5. Attach context automatically to the alert: the statement text, the current plan from EXPLAIN (GENERIC_PLAN) or a recent auto_explain entry, the relation’s last_autoanalyze, and the table’s size.

  6. Keep enough history to see the previous stable period — a few weeks is usually enough, and the data volume is small.

  7. Review regularly, not only on alert. Ranking statements by the growth of total_exec_time per day surfaces slow degradations that never cross an alert threshold.

Four steps to a useful alert Four stages. Sample cumulative counters on a schedule. Compute per-interval deltas and rates, discarding resets. Compare time per row against the recent baseline while checking rows per call is stable. Attach the statement text, current plan and analyze timestamp to the alert. sample counters every 5–15 minutes compute rates discard resets compare to baseline ms per row attach context plan, stats, sizes the guard on rows per call prevents false alarms from workload changes

Before and After #

-- BEFORE: latency alerting only
alerts fire on traffic spikes, cold caches and plan changes alike; each needs manual triage

-- AFTER: time-per-row alerting with a rows-per-call guard
one alert, correctly attributed: "efficiency changed for queryid 8123…, rows per call stable"

Why time per row, and where it misleads #

Time per row works because a plan regression changes how much work is done to produce each result row, while the result itself is unchanged. It is robust to traffic changes, to cache warmth over long intervals, and to the statement being called from more places.

It has two blind spots worth knowing. Queries that return a fixed small number of rows regardless of work — count(*), LIMIT 1 existence checks, aggregates — have one row per call always, so time per row and time per call are the same metric, and the guard adds nothing. And queries whose row counts legitimately vary with input — a search endpoint — can show time-per-row changes that reflect different result sizes rather than different plans; for those, comparing within similar row-count buckets is more reliable.

For both cases the fallback is the same: alert on time per call, and use the rows-per-call series as context for a human rather than as an automatic guard. The point of the metric is not to be universal but to remove the most common source of false alarms, which is traffic.

Normalisation, and why queryid is the unit #

pg_stat_statements groups executions by a queryid computed from the parsed statement with constants replaced by placeholders, so WHERE id = 1 and WHERE id = 2 share an entry while WHERE id = 1 and WHERE id = $1 may not, and the same text in two schemas does not. That normalisation is what makes trend tracking possible at all: without it, every execution would be its own row and no history would accumulate.

It also sets the limits of the method. A queryid is stable across restarts but not across major version upgrades, because the hashing of the parse tree changes, so a history table spans an upgrade only if statement text is stored alongside the id. Entries also differ per user and per database by default, so the same statement run by two roles produces two rows whose deltas must be summed before the rates mean anything. And a statement whose text varies — the interpolated IN list, a dynamically built ORDER BY — fragments into thousands of entries that individually never look significant while collectively dominating the workload. Checking that the top statements by total time account for most of the server’s execution time is a quick way to confirm the history is measuring what you think it is.

Common Pitfalls #

Treating counters as rates. Cumulative values grow forever. Diagnostic signal: dashboards showing monotonically rising lines. Fix: compute deltas between samples.

Ignoring resets. A restart makes deltas negative. Diagnostic signal: impossible negative rates. Fix: discard intervals with decreasing counters.

Alerting on latency alone. Traffic and result-size changes trigger it. Diagnostic signal: frequent alerts with no plan change. Fix: add the rows-per-call guard.

Sampling everything. History grows and adds nothing. Diagnostic signal: millions of rows of statement history. Fix: top N statements per sample.

Frequently Asked Questions #

How do I detect a plan regression from statistics alone? #

Sample pg_stat_statements periodically, compute per-interval deltas, and watch time per row returned. A sharp rise in time per row with unchanged rows per call and call rate indicates the server is doing more work for the same result — a plan or data change.

Are pg_stat_statements counters rates or totals? #

Totals, accumulated since the last reset or server start. Two samples and the interval between them are needed to derive rates, and any interval where a counter decreased should be discarded as a reset.

How much history should I keep? #

A few weeks of the top few hundred statements is enough to establish a baseline and to place a regression within a deploy window, and it costs only a few megabytes.

Up: Plan Regression Detection