The full answer
Something the query depends on changed: the data, the statistics the planner uses, the plan it chose, or the load on the server. Compare the current plan with what the planner expected, using EXPLAIN (ANALYZE, BUFFERS), and look for the node where estimated rows and actual rows are far apart. A sudden slowdown with the same SQL is most often a plan change caused by stale or misleading statistics.
1. Rule out the environment first
- Is everything slow, or only this query? Check CPU, I/O and connections on the server.
- Is the query waiting rather than working? Look at
pg_stat_activityfor itswait_event_typeand at locks.
If only this query is slow and it is not waiting, it is the plan.
2. Read the plan against reality
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.region, SUM(o.amount)
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
GROUP BY c.region;
For each node, compare rows= estimated with actual rows=. An estimate of 50 rows against an actual 4 million means the planner chose a plan for a tiny result: a nested loop that runs 4 million index lookups, for example, instead of one hash join.
3. The usual reasons the estimate went wrong
| Cause | What you see | Fix |
|---|---|---|
| Stale statistics after a bulk load or a big delete | Estimates far below actual on a recently changed table; last_analyze is old | ANALYZE orders; and check autovacuum is keeping up on that table |
| Data passed a tipping point | The table grew until another plan became "cheaper" on paper | Fix the estimate or the index; never trust that the old plan will return |
| Correlated columns | WHERE country = 'DE' AND city = 'Berlin' estimated as if independent | CREATE STATISTICS on the columns, then ANALYZE |
| Skewed values | One customer has 40% of rows; estimates use an average | A higher statistics target on the column |
| A new or dropped index | Plan switched to or away from an index | Check recent migrations |
4. Fix it so it stays fixed
- Refresh statistics, rerun
EXPLAIN ANALYZE, and confirm the estimate now matches and the plan changed back. - If the plan is still poor, add the index the plan needs or rewrite the query so the selective filter is applied early.
- Keep a record of plans for important queries (for example with
auto_explainlogging slow queries' plans), so next time you can compare before and after instead of guessing.
What interviewers want: a method rather than a guess, estimates versus actual rows as the key signal, statistics as the usual cause after a data change, and the specific fixes (ANALYZE, extended statistics, statistics targets, indexes).
Last updated 16 October 2026
Sources