Why did my PostgreSQL query suddenly get slow?
Quick answer: The query did not change; the plan did. As your table grew or its statistics went stale, the planner’s estimate of how many rows your predicate matches crossed a threshold, and it abandoned the index for a sequential scan (a “plan flip”). Confirm with
EXPLAIN (ANALYZE, BUFFERS)comparing estimated vs actual rows, and refresh statistics withANALYZE. Before blaming the planner, also rule out bloat, lock contention, a cold cache, and connection pressure.
What else could it be?
Section titled “What else could it be?”Plan flips are common, but other conditions can also make a stable query go slow overnight. Rule out these causes before blaming the planner:
| Cause | Tell |
|---|---|
| Table or index bloat | High n_dead_tup; autovacuum falling behind; the table’s file keeps growing |
| Lock contention | The query spends its time waiting, not working; check pg_locks / pg_blocking_pids() |
| Cold cache | Slowness right after a restart or failover that improves as the buffer cache warms |
| Connection pressure | Far more active backends than cores; context-switch thrash |
| Checkpoint / I/O spikes | Slowness that pulses on a regular interval |
| Generic prepared-statement plans | Slow only via the app’s prepared statements, fast when you run the SQL by hand |
| Plain data growth | The plan is unchanged and still correct; there is simply more data to read |
If EXPLAIN (ANALYZE, BUFFERS) shows the plan you expect and the row estimates
are close, the problem is probably on this list rather than in the planner.
What actually changed?
Section titled “What actually changed?”When identical SQL goes from fast to slow overnight, the cause is often not the SQL. PostgreSQL’s planner picks a plan based on statistics about your data: roughly, how many rows it thinks each step will touch. Those estimates shift as the data shifts, and the planner may then make a different choice.
For example, a WHERE status = 'pending' query used an index
while “pending” was rare. Your backlog grew, “pending” became 30% of the table,
and the planner correctly concluded that an index scan over 30% of the rows is
slower than just scanning the table, so it flipped to a Seq Scan. Sometimes
that flip is right. When stale statistics cause it, it is wrong.
How do I confirm it is a stats / plan problem?
Section titled “How do I confirm it is a stats / plan problem?”Compare what the planner expected to what actually happened:
EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM orders WHERE status = 'pending';A large gap between estimated and actual rows means the planner is working from inaccurate statistics. Then check when the table was last analyzed:
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tupFROM pg_stat_user_tablesWHERE relname = 'orders';| Signal | What it points to |
|---|---|
| Estimated rows ≪ actual rows | Bad cardinality estimates; start with stale stats, then correlation/skew |
last_autoanalyze far in the past | Autovacuum is not keeping stats fresh on this table |
High n_dead_tup | Table bloat from updates/deletes; vacuum is behind (a separate problem from the plan) |
A big estimate-vs-actual gap does not automatically mean stale statistics. If it
persists right after a manual ANALYZE, the estimate is wrong for another
structural reason:
- Correlated columns. The planner assumes conditions are independent, so
WHERE city = 'Austin' AND state = 'TX'gets multiplied down to a fraction of the real number. Fix with extended statistics:CREATE STATISTICS ... ON city, state FROM addresses, thenANALYZE. - Expressions and functions the planner cannot estimate through, where it falls back to a fixed default selectivity.
- Skewed data the sample missed. Raise the target with
ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000and re-analyze.
How do I fix it?
Section titled “How do I fix it?”- Refresh statistics:
ANALYZE orders;is the fastest test. If the good plan returns, stale stats were the cause. - Keep stats fresh automatically: Check that autovacuum, which also runs autoanalyze, is enabled and tuned for high-write tables; a table taking heavy writes can outrun the default thresholds.
- Help the planner on skewed or correlated columns: Raise the sampling with
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;(thenANALYZE), or create extended statistics withCREATE STATISTICSfor columns that are correlated. - Address bloat: If dead tuples are high, run
VACUUMor fix the autovacuum cadence so scans do not have to process dead rows.
A plan flip is often the right call. If the predicate matches a large share of the table now, a sequential scan is cheaper than millions of index lookups plus heap fetches. The planner may be behaving correctly because the data changed. In that case, change the query (filter harder, paginate, pre-aggregate) or the schema rather than forcing the old plan. Only when the estimates are wrong is the plan itself the bug.
Why does this bite AI-generated code especially?
Section titled “Why does this bite AI-generated code especially?”Your AI coding agent wrote and tested the query against the data that existed then. Whether a predicate is selective enough to use an index depends on the data distribution at runtime (how many rows are “pending” today), which the agent never sees. The query it produced was fine at the scale it saw.
How do I stop it recurring?
Section titled “How do I stop it recurring?”- Watch estimated-vs-actual gaps on your most important queries, not only during an incident.
- Alert on autovacuum falling behind (last_autoanalyze age, dead-tuple ratio).
- For known-skewed columns, set a higher statistics target up front.
Why would a query that was fast for months suddenly get slow?
Usually the planner switched to a different plan. As the table grew or its statistics went stale, the planner’s estimate of how many rows a predicate matches changed, and it stopped choosing the index. The SQL is unchanged; the chosen plan is not.
Does running ANALYZE fix a slow query?
It can, when the cause is stale statistics. ANALYZE refreshes the table statistics the planner uses to estimate row counts. If the planner was guessing wrong because stats were out of date, fresh stats often restore the good plan.
What is a plan flip in PostgreSQL?
A plan flip is when the planner changes its chosen plan for the same query because its cost estimates crossed a threshold, for example abandoning an Index Scan for a Seq Scan once it believes the query matches a large fraction of the table.
How do I confirm stale statistics are the cause?
Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows to actual rows. A large gap means the planner is working from bad estimates. Check pg_stat_user_tables for the last_analyze / last_autoanalyze time on the table.