# 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 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:

| 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, improving 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?

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 is driven by *stale* statistics, it is wrong.

## How do I confirm it is a stats / plan problem?

Compare what the planner expected to what actually happened:

```sql
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:

```sql
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE 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 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 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.

## 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 are not 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 is 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 →](https://app.dbgorilla.com/signup)

## 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 is not.

**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`.

## Related

- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- [How to identify slow queries in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [What pg_stat_statements tells you (and what it does not)](https://www.dbgorilla.com/learn/postgres/what-pg-stat-statements-tells-you/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)