How Do You Detect a Query Performance Regression?
Learn how to detect and root-cause SQL query performance regressions using baselines, pg_stat_statements, and plan comparison.
Expected Interview Answer
You detect a query performance regression by continuously capturing query execution plans and timing statistics, comparing them against a historical baseline, and alerting when a specific query's latency, row estimates, or plan shape shift significantly from that baseline.
Tools like pg_stat_statements or slow query logs record per-query execution time and call counts over time, so a regression shows up as a sudden or gradual rise in mean or p95 duration for a specific normalized query. Comparing EXPLAIN ANALYZE output before and after a code or data change reveals whether the plan itself changed — for example a lost index, a stale statistics-driven plan flip from index scan to sequential scan, or a new join order. Correlating the regression's timing with deploys, schema migrations, or data growth narrows down the root cause quickly.
- Catches slow-creeping regressions before they become outages
- Pinpoints the exact query and plan change responsible
- Separates data-growth effects from code or schema regressions
- Enables safe rollback or targeted index fixes
AI Mentor Explanation
A bowling coach records every bowler's average speed and economy rate match after match, so a two-kilometer-per-hour drop over several games is caught as a trend, not dismissed as one bad day. Query regression detection works the same way: it tracks a specific query's execution time release after release, so a creeping slowdown is caught as a trend rather than a one-off blip in a single slow request.
Step-by-Step Explanation
Step 1
Capture a baseline
Record execution time, plan shape, and row estimates for critical queries using pg_stat_statements or an APM tool.
Step 2
Run continuously in production
Collect the same metrics on an ongoing basis so trends and sudden shifts are both visible.
Step 3
Compare against baseline on change
After a deploy, migration, or data growth event, diff current timing and EXPLAIN plans against the saved baseline.
Step 4
Root-cause and fix
Identify whether the cause is a lost index, stale statistics, a changed join order, or genuine data growth, then remediate.
What Interviewer Expects
- Mentioning pg_stat_statements, slow query logs, or an APM as data sources
- Understanding EXPLAIN ANALYZE plan comparison, not just timing numbers
- Distinguishing a plan-shape regression from pure data-growth slowdown
- A workflow for correlating regressions with deploys or migrations
Common Mistakes
- Only looking at overall server CPU instead of per-query metrics
- Not comparing execution plans, only comparing timings
- Ignoring statistics staleness as a cause of plan flips
- No baseline captured, so regressions are only noticed anecdotally
Best Answer (HR Friendly)
“I would continuously capture per-query timing and execution plans, using something like pg_stat_statements, and compare them against a saved baseline. When a specific query's latency or plan shape changes significantly, that is a regression, and I would compare the before-and-after EXPLAIN output to figure out whether it is a lost index, stale statistics, or a real change in data volume.”
Code Example
-- Top queries by mean execution time, useful as a rolling baseline
SELECT
query,
calls,
mean_exec_time,
max_exec_time,
rows / nullif(calls, 0) AS avg_rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
-- Compare a suspected query's plan before/after a change
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, o.total, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.created_at > now() - interval '7 days';
-- Reset stats after establishing a baseline snapshot
SELECT pg_stat_statements_reset();Follow-up Questions
- How would you tell apart a regression caused by stale statistics versus a genuinely worse query plan?
- How do you regression-test query performance in a CI pipeline before deploy?
- What is a query plan flip and why does it happen?
- How would you handle a regression that only appears under production data volume?
MCQ Practice
1. What is the most reliable way to detect that a specific query has regressed?
A per-query baseline comparison isolates the specific query and reveals whether its plan or timing has actually changed.
2. A query that suddenly switches from an index scan to a sequential scan after a data load most likely indicates what?
Outdated statistics can make the planner misjudge selectivity and pick a sequential scan when an index scan would be faster; running ANALYZE often fixes it.
3. Why compare EXPLAIN ANALYZE output rather than only timing numbers when investigating a regression?
Timing alone shows that something got slower; comparing execution plans shows the structural cause of the slowdown.
Flash Cards
What tool captures per-query timing in PostgreSQL? — pg_stat_statements, which tracks calls, mean/max execution time, and rows per normalized query.
How do you find WHY a query regressed, not just THAT it did? — Compare EXPLAIN ANALYZE output before and after the change to see if the execution plan itself changed.
What commonly causes a sudden plan flip? — Stale table statistics, a dropped or unused index, or significant data growth changing selectivity.
Why track regressions per query instead of server-wide? — Server-wide metrics can mask a single badly regressed query; per-query baselines isolate the actual culprit.