Key stats
- 4 likely causes: plan change, stale statistics, data growth, and call volume
- 3 statistics to compare: median latency, rows examined, and shared blocks read
- 2 fixed points: the same business window and the same data shape
- 1 rule: establish a baseline before calling a change a regression
Key takeaways
- Define the baseline window, the query, and the statistic before reviewing a slowdown.
- Compare plans and row estimates together so a code change is not confused with a statistics change.
- Separate execution time from call volume, because a stable query can still slow a page.
- Test one hypothesis at a time and keep the result even when it is negative.
- Weigh the fix against write cost and user impact instead of optimizing every slower query.
Question and scope
This report examines query performance regression as a maintenance signal in outsourced database work. The unit is one query or query family whose response time or resource use becomes materially worse after a change. The scope covers application queries in relational databases, including reporting statements and background jobs. It excludes capacity incidents caused by hardware failure or a network outage.
The question is whether a pod can detect and explain a regression before users report it. A query that slows by twenty percent may be invisible in a dashboard that only tracks averages. The report treats regression as a change in the relationship between a query and its data, plan, or call pattern. It looks for evidence that the change is real and attributable.
Defining a regression
A regression needs a comparison point. Define the baseline query, the data window, and the statistic that will be compared, such as median latency, rows scanned, or shared blocks read. Without a fixed comparison, a slow day looks like a regression and a fast cache looks like an improvement. Write the definition before the review starts. Put the definition in the review template so every comparison uses the same fields.
Set a materiality threshold that fits the user experience. A jump from twenty to thirty milliseconds may not matter, while a jump from two to four seconds will. The threshold should be tied to a page budget or a job window rather than a round percentage. Record the threshold beside the measurement so a later reader can judge the decision.
Baseline and comparison window
Capture the baseline during a period that represents normal use, not during a release freeze or a holiday. Record the query text, the plan, the row counts, and the database version at that time. A baseline without a plan cannot distinguish a code change from a statistics change. A baseline taken after a release can include the release's own effect and make the comparison misleading.
Compare the same business window, because query load follows the calendar. A report that runs at month end has a different profile from the same report on a Tuesday morning. If the comparison must span different periods, say so and adjust the interpretation rather than the number. Record the window in the review so a later reader can tell whether the comparison was fair.
Plan and statistics evidence
Read the plan for both periods and compare the access paths, join order, and row estimates. A change from an index scan to a sequential scan is a concrete finding. So is a join order that reverses because the planner now expects far fewer rows from one side. Record the estimated and actual rows for the steps that changed. The plan comparison should use the same query text, because a rewritten statement is a different unit.
Stale statistics are a common cause of a plan shift after a bulk load or a migration. Check the last analyze time and the sample size before assuming the query itself is at fault. Refresh the statistics in a controlled way and compare again. A plan that returns to the old shape after an analyze points to maintenance, not to code.
Workload and data drift
The same query can slow because the table grew, because a tenant added a large data set, or because a caller started running it more often. Separate execution time from call volume. A query that is stable at five milliseconds but now runs a thousand times per request can still double the page time. The call count is often the missing number in a query review, and it can change without a code edit.
Check whether the data distribution changed. A predicate that used to match a few rows can begin matching a large fraction after a product change. The planner may be making a reasonable choice for the new distribution. Look at the row counts before concluding that the plan is wrong. A distribution change is a reason to revisit the index, not necessarily a reason to rewrite the query.
Review ownership
The pod can gather the evidence and propose a fix, while the buyer-side data owner approves changes that affect shared tables or indexes. Assign the review to one named engineer and one reviewer so the comparison has an owner. A committee can discuss a regression for weeks without producing a decision. The owner should be able to approve a test in a read replica without waiting for a full change window.
Keep the query review separate from a general performance project. One query family, one hypothesis, and one measured result is enough for a review cycle. A broad performance initiative tends to produce a long list of possible causes and no change to the signal. A narrow review also makes the result easier to explain to the buyer's data owner. The result should be attached to the change record so the next reviewer can read it.
A practical protocol
Select the query from a user complaint, a slow-query log, or a changed endpoint. Record the current plan, timing, and call count. Find the last known good period and capture the same fields. If no baseline exists, capture the current state and mark the first review as establishing one. Write down the chosen period before running the comparison so the result cannot be selected after the fact.
Form one hypothesis about the cause: plan change, statistics, data growth, or call volume. Test it with the smallest safe change, such as an analyze or a query rewrite in a read replica. Compare the same statistic over the same window. Record the result whether or not it confirms the hypothesis. A negative result is useful because it removes a hypothesis and narrows the next test.
Add a monitor that compares the query with its baseline and alerts only when the threshold is crossed. Give the monitor an owner and a review date. Without a follow-up, the same regression can return after the next migration or bulk load. The monitor should use the same statistic and window as the review rather than a different dashboard default.
A worked example
A monthly statement query takes four seconds after a migration and took one second before it. The plan shows a sequential scan on a table that grew from two million to nine million rows. The index exists, but the predicate applies a function to the indexed column, so the planner cannot use it. The row growth alone does not prove the cause, but it explains why the plan changed.
The team rewrites the predicate to compare the raw column and adds a test that checks the plan shape. The query returns to about one second in the test environment and stays within the page budget after release. The review records the function-on-column cause so the next engineer recognizes the pattern. The test should fail when the predicate wraps the column again, so the fix survives a later refactor.
Data to collect first
Record the query text, the caller, the baseline window, the comparison window, the median and tail latency, the rows examined, and the plan. Add the database version, the statistics timestamp, and the data volume at each point. Keep the raw plan output so a reviewer can inspect the steps that changed. The record should note whether the plan came from a test system or production, because the plans can differ.
Collect enough runs to see a range rather than a single sample. Five runs on an idle machine are weaker than a week of production samples. If the query is rare, compare the same scheduled run across several periods. Mark the sample size beside the result. A range with a wide spread is a signal to inspect the environment before changing the query.
Counterevidence and limits
A slower query is not always a problem worth fixing. A report that runs once a month and finishes inside its window may be fine even after a large relative increase. Weigh the regression against the user impact and the cost of the change. A fix that adds a risky index to a high-write table can be worse than the delay.
Plans also vary for reasons the team cannot control, including concurrent load and cache state. A single comparison can mislead. Repeat the measurement and look for a consistent pattern before committing to a database change. The review should state which explanation the evidence supports and which one remains open. A second measurement after a week is enough to confirm the pattern.
Evidence table
| Signal | What to inspect | Owner |
|---|---|---|
| Outcome | Acceptance evidence for the bounded task | Task reviewer |
| Control | Access, test, and approval boundary | Internal owner |
| Handoff | Open risks and next decision | Next owner |
Good distributed work is observable at the handoff: the result, evidence, limitations, and next owner are explicit.
Frequently asked questions
Should every slower query be treated as a regression?
No. A report with a generous window can absorb a relative slowdown. Use a materiality threshold tied to user impact or a job deadline.
Who should approve a new index?
The buyer-side data owner should approve indexes on shared or high-write tables. The pod can prepare the plan comparison and the write-cost estimate.
What if there is no historical baseline?
Capture the current state, mark it as the first baseline, and compare future runs against it. State the limitation in the review so the first result is not overread.
