Skip to main content

Why did my PostgreSQL query suddenly get slow?

Quick answer: The query didn't 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 with ANALYZE. Before you blame the planner, also rule out bloat, lock contention, a cold cache, and connection pressure.

What else could it be?​

Plan flips are the usual suspect, and the rest of this article focuses on them. But before you conclude the planner is at fault, rule out the other things that make a stable query go slow overnight:

CauseTell
Table or index bloatHigh n_dead_tup; autovacuum falling behind; the table's file keeps growing
Lock contentionThe query spends its time waiting, not working — check pg_locks / pg_blocking_pids()
Cold cacheSlowness right after a restart or failover, improving as the buffer cache warms
Connection pressureFar more active backends than cores; context-switch thrash
Checkpoint / I/O spikesSlowness that pulses on a regular interval
Generic prepared-statement plansSlow only via the app's prepared statements, fast when you run the SQL by hand
Plain data growthThe plan is unchanged and still correct — there's 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?​

When identical SQL goes from fast to slow overnight, the cause is almost never 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 at some point the planner makes a different, worse choice.

The classic version: a WHERE status = 'pending' query used an index happily 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 it's driven by stale statistics, it's wrong.

How do I confirm it's 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 guessing from bad numbers. Then check when the table was last analyzed:

SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
SignalWhat it points to
Estimated rows ≪ actual rowsBad cardinality estimates — start with stale stats, then correlation/skew
last_autoanalyze far in the pastAutovacuum isn't keeping stats fresh on this table
High n_dead_tupTable bloat from updates/deletes — vacuum is behind (a separate problem from the plan)

A big estimate-vs-actual gap doesn't automatically mean stale statistics. If it persists right after a manual ANALYZE, the estimate is wrong for a structural reason instead:

  • 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, then ANALYZE.
  • Expressions and functions the planner can't 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 1000 and re-analyze.

How do I fix it?​

  • Refresh statistics: ANALYZE orders; — the fastest test. If the good plan returns, stale stats were the cause.
  • Keep stats fresh automatically: make sure 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; (then ANALYZE), or create extended statistics with CREATE STATISTICS for columns that are correlated.
  • Address bloat: if dead tuples are high, VACUUM (or fix the autovacuum cadence) so scans aren't wading through dead rows.

One caveat before you fight the planner: a plan flip is often the right call. If the predicate genuinely matches a large share of the table now, a sequential scan really is cheaper than millions of index lookups plus heap fetches — the planner did its job and the data changed underneath you. That's a signal to change the query (filter harder, paginate, pre-aggregate) or the schema, not to force the old plan. Only when the estimates are wrong is the plan itself the bug.

Why does this bite AI-generated code especially?​

Your AI coding agent (Claude Code, Cursor) wrote and tested the query against the data that existed then. Whether a predicate is selective enough to use an index is a property of the data distribution at runtime — how many rows are "pending" today — which the agent never sees. The query it produced was genuinely fine at the scale it saw. The plan flip is a production event triggered by data it had no visibility into.

How do I stop it recurring?​

  • Watch estimated-vs-actual gaps on your most important queries, not just when something is on fire.
  • Alert on autovacuum falling behind (last_autoanalyze age, dead-tuple ratio).
  • For known-skewed columns, set a higher statistics target up front.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the live plan and statistics for the query — the estimated-vs-actual gap, the last-analyze time, the dead-tuple counts — so it can tell you the plan flipped and why, instead of staring at unchanged SQL. It surfaces and explains; it does not modify production. Get started free →

FAQ​

Why would a fast query suddenly get slow? The planner switched plans. As the table grew or its statistics went stale, its row-count estimate changed and it stopped using the index. The SQL is unchanged; the plan isn't.

Does running ANALYZE fix a slow query? When the cause is stale statistics, often yes — ANALYZE refreshes the numbers the planner estimates from, which can restore the good plan.

What is a plan flip? When the planner changes its chosen plan for the same query because cost estimates crossed a threshold — e.g. dropping an Index Scan for a Seq Scan once it thinks the query matches a large share of the table.

How do I confirm stale statistics are the cause? Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated to actual rows; a big gap implicates the estimates. Check last_analyze / last_autoanalyze in pg_stat_user_tables.