When Statistics Go Stale: Why the Planner Makes Bad Decisions

PostgreSQL's planner is a cost-based optimizer. For any query it considers alternative plans and picks the cheapest, but cheapness is estimated, not measured. Those estimates come from statistics gathered by ANALYZE: the number of distinct values in a column, the most common values and their frequencies, histograms of distribution, and overall table size.

Every join order, every choice between a sequential and an index scan, every decision to use a nested loop or a hash join, rests on these numbers being roughly true.

Statistics describe the data as it was when last analyzed. A bulk load, a large delete, or steady growth can move the real distribution far from the recorded one. When that happens, the planner estimates that a step will touch a few rows when it will actually touch millions, and picks a plan optimized for the wrong scale.

The failure is not a slowdown of a few percent. A nested loop chosen on the belief that its inner side returns ten rows becomes a disaster when it returns ten million. Stale statistics produce plans that are wrong by orders of magnitude.

Even fresh single-column statistics can mislead. The planner assumes columns are independent unless told otherwise, so it multiplies selectivities. When columns are correlated, a city and its postal code, for example, that assumption badly underestimates matching rows. Extended statistics exist to capture these relationships, but they must be created deliberately.

This is a case where the default configuration is silently insufficient for a common data shape.

The signals are read-only. The statistics views record when each table was last analyzed, by autoanalyze or by hand, and how many rows have changed since. A large modification count with an old last-analyze timestamp is a direct indicator that the planner is working from a picture of the data that no longer holds.

Watching this per table lets you see stale statistics forming before they produce a runaway query, rather than diagnosing them after an incident from a slow plan.

Running ANALYZE to refresh statistics is one of the safest actions available. It is read-only with respect to your data, it does not lock the table against normal use, and its effect is simply better estimates. This is why it, alongside regular vacuum, sits in the small set of actions an automated DBA can perform automatically under policy.

Tuning how much detail is collected, or defining extended statistics on correlated columns, changes configuration and is proposed to a human rather than applied silently. Refreshing what already exists is routine; changing what is collected is a decision.