Question: Plans and the planner
Why did my query suddenly get slow?
Answered in the first paragraph. Last updated .
The word “suddenly” is the most useful part of the report. Growth in data, bloat and index decay all produce curves, not steps. A step change means the plan changed, the query started waiting on something it did not wait on before, or the statement is being executed differently. Those three are distinguishable, and checking them in that order costs minutes.
Three step changes, told apart
A plan flip is the most common. Statistics were refreshed, a threshold was crossed, or a prepared statement stopped re-planning per execution and settled on a generic plan that suits the average parameter and not yours. The signature is a large change in rows read for an unchanged number of rows returned.
Waiting is the second. The statement is doing the same work and spending its time queued behind a lock, behind storage, or behind other sessions. The signature is that time went up while the work done did not, and the class of wait is the whole diagnosis: wait event types is the vocabulary for that.
The third is not really a change at all: the statement being measured is not the statement that is slow. Two different queries that normalise to the same fingerprint, or a function whose body does the real work, will move an average around while nothing about the query you are reading has altered. Query fingerprints explains what is being grouped.
Why the averages lie about when
Cumulative statement statistics are totals since the last reset, so a mean calculated over them mixes last Tuesday into this morning and blunts exactly the step you are looking for. Take two snapshots and subtract, rather than reading a single row.
There is also the reset itself. If the counters were cleared, whether by a deployment, a restart or somebody’s script, the numbers restart with them and the graph shows a discontinuity that is an artefact. From PostgreSQL 17 each statement records when its own statistics began, which finally makes it possible to tell a new query from an old one whose history was wiped.
If it turns out the plan did change, query plan regression covers detecting that automatically rather than by memory. If the time is being spent waiting, active session history is how to see what was waiting and on what, after the incident has ended.