A query that ran in 50ms for two years now takes 90 seconds. Nothing was deployed and the data volume grew normally. What happened?

The most likely cause: a plan flip

The optimiser's choice is a function of estimated row counts. As the data grows or its distribution shifts, an estimate crosses a threshold and the planner switches strategy — typically from a nested loop with an index scan to something else, or vice versa. The SQL is unchanged; the plan is not.

The classic instance: a nested loop that was correct when the outer side returned five rows becomes catastrophic when it returns five hundred thousand, because the cost is quadratic. Nothing warns you; the plan simply becomes wrong.

Diagnosis, in order

1. Get the plan with actual execution, and compare estimated versus actual rows at every node. A large divergence is the answer, and it points at statistics rather than at the index.

2. Check when statistics were last collected. A bulk load, a large delete, or a change in the distribution of a filtered column can leave them badly stale. ANALYZE is the cheapest possible fix and resolves a meaningful share of these.

3. Look for correlated columns. The planner assumes independence, so filtering on two related columns multiplies selectivities and underestimates severely. Extended statistics fix this in PostgreSQL; other engines have equivalents.

4. Check for parameter sniffing if it is a prepared or parameterised query. A plan cached for one parameter value can be terrible for another — the classic case being a customer ID where one customer has a million rows and the rest have ten.

5. Check whether it is the query at all. Lock contention, connection pool saturation, a bloated table needing vacuum, or a concurrent job saturating I/O all present as a slow query.

The fixes, in preference order

ANALYZE first. Then extended statistics for correlated columns. Then a better index — often a covering or partial one matching the actual predicate. Then a query rewrite if the plan shows something structurally bad, such as a function on an indexed column defeating it.

Planner hints and forced plans last, because they freeze a decision that should adapt as the data changes, and they are forgotten by whoever inherits the query.

What a strong answer adds

Noting the monitoring gap this reveals: nobody knew until it was 1,800× slower. Capturing plan changes for important queries — or at minimum alerting on statement-level latency regressions — turns this from a discovery into a notification. Several engines expose plan history directly.