Query performance
The application is slower and every database chart is green
Averages hide the thing you are looking for, and by the time anybody opens a dashboard the evidence has aged out. This page is about what it takes to answer the question afterwards.
Nothing was deployed and it is still slower
This is the complaint that arrives without evidence. Somebody upstream has a p99 that doubled. The database graphs show CPU comfortable, connections normal, no errors. Both parties are looking at true numbers and reaching opposite conclusions, because a mean over a minute of a thousand statements will not move when one of them stops using an index.
Underneath, the usual explanations are dull. A table grew past the point where the planner preferred a scan. Statistics were refreshed and estimates moved with them. A parameter that used to be selective stopped being selective for one large customer. A bulk load ran and nothing was analysed afterwards. None of those is exotic and all of them are invisible in aggregate.
What makes it expensive is that the proof is perishable. Ask at nine in the morning what the database was doing at ten past three and, unless something was sampling at ten past three, the honest answer is that nobody can say. The incident review then turns into a discussion of plausible causes, which is how the same regression happens twice.
Four tests for anything watching queries
Apply them to whatever you already run as well. The third is the one that decides whether the alerts get read.
It was already recording
Nothing lets you go back and sample an incident that has ended. Either something was watching sessions at 03:14 or the question has no answer, however good the tool you install at 09:00.
It compares plans by shape
Two plans differing only in estimated row counts are the same plan. Treating every textual difference as a change produces a feed nobody reads by the second week.
It wants a slowdown as well
Plans change all day and almost all of it is fine. A regression is a structural change and a measurable cost together; either one alone is an observation, not an alert.
It says where the time went
Slow is not a diagnosis. Blocked on a lock, waiting on storage and burning CPU on a sort look identical in a latency graph and want three unrelated responses.
How ours is put together
Sessions are sampled continuously and kept, so a two-minute stall at 03:14 is still there to look at when somebody asks about it after breakfast. Each sample carries what the session was waiting on, which is what turns a slow period into a named bottleneck rather than a shrug, and any two windows can be put side by side to ask what changed between them. The wider picture that sampling feeds is on the observability section.
Plans are captured by structure rather than stored verbatim. Two executions whose shape is identical and whose estimates differ collapse to one plan, so the history of a statement is a short list of genuinely different plans instead of thousands of near-copies. We wrote up what that fingerprinting threw away in an earlier post; the short version is that most of what a plan store holds is the same plan again.
A regression then requires both halves. The structure has to have changed and the statement has to have got measurably slower, against its own history rather than against a threshold somebody guessed. Repeat firings collapse into one incident with a duration, so an ongoing problem reads as one story rather than four hundred notifications. What else that detection layer covers is on anomaly detection.
Where there is something to suggest, the advisor attaches its reasoning: the plan on both sides, the estimated cost difference, and for an index candidate the writes it would slow down. The index advisor goes into what arrives with that kind of recommendation. Nothing is applied by the tool on its own account; a proposal moves only as far up the autonomy ladder as you have taken it, and the gate in front of every mutating action defaults to refusing.
What a regression looks like when it fires
Two independent facts, then the context that tells you whether to care tonight or on Thursday.
regression statement 9f2c · reads have gone from tens of ms to seconds change structure differs: index path replaced by a sequential scan confirmed latency moved with it · not an estimate, measured either side waits time is now spent on storage reads, not on lock acquisition window began during a bulk load · first seen on the reporting replica next candidate rewrite and a candidate index, each with its cost
The fifth line does most of the work. A regression that begins during a bulk load and shows up first on a replica is usually a statistics story, not a query story, and knowing that before opening the plan saves the wrong hour. When the cause turns out to be rows that could not be cleaned up rather than anything the planner did, it belongs on the vacuum side instead.
Build a smaller version of this first
Sampling sessions into a table on a schedule is a short piece of work and it answers most of these questions for one database. Active session history in Postgres has the sampler and what to do with what it collects, and query plan regression covers proving a plan changed and getting the old one back. Both carry the caveats.
You will get a long way. Where it stops is when the sampler itself needs upgrading on thirty machines, when each engine reports its waits slightly differently, and when the person who wrote it has moved teams. Until then this is a weekend, and we would rather say so. If you want to ask questions of the whole estate in plain language instead, that is what the agent interface is for.
Ask what happened at 03:14 and get an answer.
A pilot samples your own clusters from the first day, read-only, and shows you the regressions it would have caught last month before it is trusted with anything else.