Skip to content

Why did my PostgreSQL query suddenly get slow?

Last updated

View as Markdown

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 with ANALYZE. Before blaming the planner, also rule out bloat, lock contention, a cold cache, and connection pressure.

Plan flips are common, but other conditions can also make a stable query go slow overnight. Rule out these causes before blaming the planner:

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 that improves 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 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.

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_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 is not 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 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, then ANALYZE.
  • 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 1000 and re-analyze.
  • 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; (then ANALYZE), or create extended statistics with CREATE STATISTICS for columns that are correlated.
  • Address bloat: If dead tuples are high, run VACUUM or 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.

  • 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.