How to fix a slow query after a schema migration (plan regression)?

A query that ran in 50 milliseconds now takes 42 seconds. Nothing about the traffic moved. That combination, one statement much slower with everything else unchanged, narrows the cause down hard, and it points at the schema rather than the load.

Why does one query get slower after a migration?#

Because the planner picks a strategy from the schema and the statistics, and a migration changes both.

The planner is choosing between access paths: scan the whole table, or use an index to jump to the rows that match. That choice is cheap to get right and catastrophic to get wrong. On a table of 28 million rows, an index lookup touches a few pages and a sequential scan touches all of them. The difference between the two is not a percentage, it is three orders of magnitude, which is why these incidents look like a total outage rather than a slowdown.

A migration flips that choice in several ways. It can add a column to a WHERE clause that has no index. It can drop or rename an index the planner was relying on. It can change a column's type so an existing index no longer applies. It can add enough rows that the statistics go stale. All four end in the same place: a plan that was fine yesterday is a table scan today.

How do you read the plan for a regression?#

Run EXPLAIN (ANALYZE, BUFFERS) on the slow statement. ANALYZE executes it and reports real timings rather than estimates, and BUFFERS shows how much data it actually touched.

-- ANALYZE runs the statement, so do this on a replica if the-- query is expensive. BUFFERS is what turns "slow" into "read-- 1.2 million pages", which is the number that ends the debate.EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM usersWHERE region = 'us-east' AND status = 'active'ORDER BY last_login; 

Seq Scan on users  (cost=0.00..1841204.00 rows=1 width=612)                   (actual time=41903.221..41903.223 rows=1 loops=1)  Filter: ((region = 'us-east'::text) AND (status = 'active'::text))  Rows Removed by Filter: 28,004,113  Buffers: shared read=1,204,388Execution Time: 41903.245 ms 

Three things in that output are the diagnosis. Seq Scan is the access path, when this query used to use an index. Rows Removed by Filter: 28,004,113 says the database read 28 million rows to return one, which is the definition of a missing index. Buffers: shared read in the millions confirms the pages came from disk rather than cache, so every one of them cost real I/O.

The healthy version of the same plan is unambiguous by comparison.

Index Scan using idx_users_region_status on users                   (cost=0.43..8.45 rows=1 width=612)                   (actual time=0.021..0.023 rows=1 loops=1)  Index Cond: ((region = 'us-east') AND (status = 'active'))  Buffers: shared hit=4Execution Time: 12.104 ms 

How do you find the migration that caused it?#

Work backwards from the statement to the schema change, not forwards from the deploy list.

-- Which statements regressed, ranked by total time. Compare-- mean_exec_time against what you remember; a 1000x jump in-- one row and normal numbers everywhere else is the shape.SELECT calls,       round(mean_exec_time::numeric, 2)         AS mean_ms,       round(total_exec_time::numeric / 1000, 1) AS total_s,       left(query, 60)                           AS statementFROM pg_stat_statementsORDER BY total_exec_time DESCLIMIT 5; 

Then check whether the columns that statement filters on are indexed at all.

-- Every index on the table, with its definition. If the WHERE-- clause columns do not appear as a leading prefix of any of-- these, the planner had no index to choose.SELECT indexname, indexdefFROM pg_indexesWHERE tablename = 'users'; 

Composite index order matters here and is a common near-miss. An index on (status, region) does not serve a query filtering on region alone, because the leading column is not constrained. An index on (region, status, last_login) serves filters on region, on region plus status, and supplies the sort for ORDER BY last_login. Same three columns, completely different usefulness.

How do you fix a plan regression safely?#

Create the index without blocking writes, then verify the planner adopted it.

# CONCURRENTLY does not take a write lock, so the table stays# usable while it builds. It is slower, cannot run inside a# transaction block, and can leave an INVALID index if it fails.psql -U postgres -d userdb -c "  CREATE INDEX CONCURRENTLY idx_users_region_status  ON users(region, status, last_login);" # If a CONCURRENTLY build fails it leaves an invalid index behind# that the planner will not use. Check before assuming success.psql -U postgres -d userdb -c "  SELECT indexrelid::regclass AS index, indisvalid  FROM pg_index WHERE indrelid = 'users'::regclass;" 

Building the index is not the fix. The planner adopting it is the fix, and those are different events. Re-run EXPLAIN ANALYZE and confirm the access path changed to Index Scan. If it did not, statistics are usually stale, and ANALYZE users; gives the planner current row counts to cost against.

Then close the loop in the migration process, because this recurs. A review rule that any migration adding a WHERE clause column must ship a matching index catches it before deploy, and an alert on pg_stat_statements mean execution time catches the ones that get through.

What does a real plan regression look like?#

Sherlocks AI investigated one on a user-service cluster on 29 March 2026. A Flyway migration had added a filter column with no supporting index, and the affected query went from 50 ms to 42 seconds against a table of roughly 28 million rows.

The visible failure was nowhere near the database. HikariCP pools on all three application servers pinned at 50 of 50 connections, and users got 2,891 HTTP 503s during the morning peak. The paging alert was a connection pool alert, the dashboard everyone opened was an application dashboard, and the cause was a schema change from a deploy that had already been declared successful. Creating the composite index on (region, status, last_login) returned the query to about 12 ms and the pools drained on their own within two minutes. The full writeup is in the connection pool exhaustion investigation.

That gap between where it hurt and where it broke is the normal case, not the exception, which is why connection pool exhaustion is worth reading alongside this. An unbounded query can also exhaust memory rather than time, as in the OOMKilled KYC query investigation, where 22.36 million rows were pulled into a 2.5 GB container. More worked examples are on the database examples page, and the planner's own documentation is in Using EXPLAIN.

Slow query after migration FAQ#

Why did one query get much slower when nothing else changed? Because the planner switched access paths. A missing index on a newly filtered column turns an index lookup into a full table scan, which is orders of magnitude, not percentages.

What in EXPLAIN output tells me it is a missing index? A Seq Scan with a large Rows Removed by Filter underneath it. Reading millions of rows to return a handful is the signature.

Can I create the index without downtime? Yes, with CREATE INDEX CONCURRENTLY. It avoids the write lock, takes longer, and cannot run inside a transaction block.

I built the index and the query is still slow. Why? Either the planner has stale statistics, so run ANALYZE, or the index column order does not match the filter. A composite index only serves queries constraining its leading columns.

Does this only happen right after a deploy? No. Statistics drift and table growth can flip a plan weeks later, which is why the correlation to look for is in the schema history, not only the last deploy.

Should I use a statement timeout to protect against this? As a blast-radius control, yes. It turns a 42-second query into a fast failure, which protects the pool, but it does not fix the plan.