There is one query-plan regression I have debugged on MySQL and on PostgreSQL, in nearly identical form, more times than any other. The query filters on one or two columns, orders by a timestamp or an id, and takes a small LIMIT. It runs in single-digit milliseconds for months. Then one day, for one customer or one status value, it takes thirty seconds, and nothing about the query changed.
This is the classic ORDER BY ... LIMIT wrong-index regression, and it is not a bug in either optimizer. It is a rational bet that both engines make with incomplete information, and it loses in the same predictable situation. Since it survives a migration in both directions, it deserves a field note of its own.
The shape of the regression
The canonical query looks like this in either dialect.
SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = 42
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;There are two reasonable strategies. Strategy A: use an index on the filter columns, collect every matching row, sort them, return ten. Its cost scales with how many rows match the filter. Strategy B: walk an index on created_at in descending order, check each row against the filter, and stop after ten hits. Its cost scales with how far you must walk before finding ten matches.
Strategy B is brilliant when matches are plentiful and recent: ten hits arrive within the first few dozen index entries, no sort, minimal reads. Strategy B is catastrophic when matches are rare or old: a customer with nine pending orders, or none, forces the scan through the entire index, checking millions of rows to return fewer than ten. Same plan, three orders of magnitude apart, depending on the parameter.
Why LIMIT tempts planners into it
Both optimizers reason about Strategy B the same way: estimate the selectivity of the filter, assume matching rows are spread evenly through the index order, and conclude the scan can stop early. If one percent of rows match, ten hits should arrive within roughly the first thousand entries. LIMIT is what makes the bet available at all; without it, the ordering-index walk has no early exit and loses on cost. With it, the plan's estimated cost is a small fraction of the full scan, and it often beats the filter-then-sort plan on paper.
The uniformity assumption is the failure point, and it fails on correlation. Pending orders cluster at recent timestamps, so a filter on an old or inactive slice means the walk finds nothing until it has covered nearly everything. Per-column statistics cannot see this relationship between the filter columns and the ordering column, in either engine, and the planners also generally reason as if the requested rows exist; when fewer than LIMIT rows match, the early-exit logic never triggers and the worst case is guaranteed. That is why the regression so often surfaces as "the query is only slow for customers with no results."
Diagnosing it in MySQL
The signature in MySQL 8.x: EXPLAIN shows the key column pointing at the index on the ORDER BY column, for example an index on created_at, instead of the index matching your WHERE clause, often with a suspiciously optimistic rows figure. Confirm it with EXPLAIN ANALYZE, which executes the query and shows actual work: an index scan iterator on created_at with an enormous actual row count feeding a filter that discards nearly everything.
MySQL has accumulated specific machinery around this exact pattern. Since 8.0.21 the optimizer_switch flag prefer_ordering_index can be set to off, telling the planner to stop favoring the ordering index for LIMIT queries; it exists because this regression class was common enough to earn a dedicated flag. Per query, an index hint or FORCE INDEX pins the filter index, and the old trick of writing ORDER BY (created_at + 0) makes the ordering expression non-indexable so the walk is never considered. The durable fix is the same one PostgreSQL wants, covered below: a composite index that serves filter and order together.
Diagnosing it in PostgreSQL
The PostgreSQL signature is an Index Scan Backward on the created_at index with a Filter line, and the tell is in EXPLAIN (ANALYZE, BUFFERS): a huge Rows Removed by Filter count, actual time in seconds against an estimated cost that looked tiny, and buffer reads far beyond what ten rows justify. The limit-scaled cost estimate is visible in the plan too; the LIMIT node believes it will consume a small fraction of the scan beneath it.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, status, created_at
FROM orders
WHERE customer_id = 42
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;Two PostgreSQL-specific aggravators are worth knowing. Prepared statements can flip to a generic plan after several executions, so the regression appears only after a connection warms up, which is thoroughly confusing the first time; plan_cache_mode = force_custom_plan is the relevant lever when parameter-sensitive queries meet this pattern. And because the bet depends on selectivity estimates, a statistics refresh, an ANALYZE after bulk changes or an autovacuum-triggered one, can flip a stable query into the bad plan overnight. If a query "regressed with no deploy," check whether its plan flipped, which is where slow query monitoring with history earns its keep.
Fixing it for good in either engine
The fix that actually retires the regression, in both engines, is a composite index shaped as equality columns first, ordering column last: on (customer_id, status, created_at). Now one index serves both halves of the query. The engine descends to the exact filter slice and reads it already ordered by created_at, so the LIMIT stops after ten rows touched, whether ten thousand rows match or zero. There is no bet left to lose: the empty-result case, the killer for the ordering-index walk, becomes the cheapest case of all.
When you cannot add the index immediately, the mitigations differ by engine. On MySQL: FORCE INDEX on the filter index, or the prefer_ordering_index switch as a broader lever. On PostgreSQL, which has no hints in core: rewrite so the planner cannot use the ordering index, either ORDER BY (created_at + 0), or fetch the filtered rows in a MATERIALIZED CTE and order outside it. These are fences, not fixes; they force Strategy A even when Strategy B would have won, so treat them as bridges to the composite index. The query optimizer guide covers why the planner cannot see the correlation that breaks its bet, and extended statistics can improve the filter estimates, though they do not model the filter-to-order correlation itself, which is why the index fix outranks the statistics fix for this pattern.
Keeping it from coming back
This regression recurs because it is parameter-dependent and data-dependent: the query is fine in staging, fine for the demo account, and terrible for the one customer whose data shape breaks the uniformity assumption. Averages hide it; a query family whose mean looks acceptable can conceal a p99 that walks the whole index. The defenses that work are unglamorous: track query families with percentiles rather than means, treat plan flips as deploy-worthy events, and when a new ORDER BY ... LIMIT query ships, ask the one review question that catches this class early: what happens when zero rows match?
How MonPG helps once you are on PostgreSQL
Everything about this regression rewards history, and that is what MonPG provides for PostgreSQL workloads; it is a PostgreSQL-only monitor, so the diagnosis vocabulary matches this article exactly. Query family tracking from pg_stat_statements surfaces the p99-versus-mean gap that flags parameter-sensitive plans, timing history shows the step change when a plan flips after an ANALYZE or a deploy, and plan context lets you see the Index Scan Backward and Rows Removed by Filter evidence for the regressed period rather than reconstructing it from memory during the incident. If you take one tool from this piece, make it the PostgreSQL query plan analyzer: paste the slow plan, and the divergence between estimated and actual rows, the signature of the losing LIMIT bet, is highlighted instead of hunted for.