Understanding Statistics and Query Plan Cache: Why Plans Go Stale

1. How the optimizer actually makes decisions

The query optimizer does not decide based on SQL text, but based on an estimated cost calculation built on statistics about the actual data distribution. For every possible access strategy, for example an index scan versus a full table scan, the optimizer estimates the expected number of rows to read (cardinality estimation) and chooses the strategy with the lowest estimated total cost. This estimate is only as good as the underlying statistics.

This is exactly where a frequently overlooked problem arises: two identical queries on two tables with different data distributions can get completely different, respectively optimal plans. And the same query on the same table can get a different plan after massive data growth, because the underlying statistics have changed, even though nothing changed in the query text itself.

Anyone who wants to understand statistics and query plan cache must therefore keep two separate concepts apart: statistics describe the data distribution and are refreshed periodically. The plan cache stores a once computed execution plan for reuse. Both can go stale independently of each other, and both forms of staleness produce the same symptom: a query that suddenly becomes slower for no apparent reason.

2. What optimizer statistics actually contain

MySQL and PostgreSQL maintain a set of metrics for every table and index: the estimated total row count, the average row size, and above all the cardinality, meaning the number of distinct values per column. A column with only two distinct values (for example a boolean flag) gives the optimizer a completely different cost picture than a column with a million distinct values (for example an email address), even if both share the same data type.

In PostgreSQL, ANALYZE produces these statistics and stores them in the system table pg_stats. MySQL with InnoDB computes comparable values via ANALYZE TABLE and stores them permanently rather than only in memory, provided innodb_stats_persistent is enabled. Without persistent statistics, MySQL would recompute and partly randomly resample after every restart or on certain internal events, which can lead to inconsistent planning decisions between two server restarts.

-- PostgreSQL: inspect the statistics for one column
SELECT
    attname,
    n_distinct,        -- estimated number of distinct values
    correlation,        -- physical vs. logical sort order
    most_common_vals,   -- most common values
    most_common_freqs   -- their relative frequency
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

-- Manually refresh statistics
ANALYZE orders;

-- MySQL: check table statistics
SELECT
    table_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'shop' AND table_name = 'orders';

ANALYZE TABLE orders;

3. Histograms: accuracy beyond simple counts

A single cardinality value is not enough to correctly estimate skewed data distributions. A status column with the values "pending", "shipped" and "cancelled" might only have three distinct values, but if 95 percent of all rows are "shipped", a query with WHERE status = 'cancelled' is significantly more selective than one with WHERE status = 'shipped'. Without a histogram, the optimizer would incorrectly treat both cases the same.

PostgreSQL stores most_common_vals and most_common_freqs for the most frequent values, plus a separate histogram for the remaining distribution. MySQL 8.0 introduced a similar feature with ANALYZE TABLE ... UPDATE HISTOGRAM ON column, which was previously completely missing and made the optimizer fundamentally blind to skewed distributions. Anyone who wants to understand statistics and query plan cache should deliberately create histograms for columns with known skewed distributions, not just run a blanket ANALYZE.

-- MySQL 8.0+: create a histogram for a skewed column
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- Inspect the histogram data
SELECT
    JSON_PRETTY(histogram)
FROM information_schema.column_statistics
WHERE table_name = 'orders' AND column_name = 'status';

-- PostgreSQL: increase the statistics target for a single column
-- (more buckets in the histogram, more precise on skewed distributions)
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

4. How statistics go stale and why it goes unnoticed

Statistics are a snapshot taken at the time of the last collection, not a live calculation. A table that has grown from 10,000 to 10 million rows since the last ANALYZE run still has the old cardinality values stored, until a new analysis runs. In the meantime, the optimizer makes decisions based on a data world that no longer exists, often with dramatic consequences: an index scan that made sense at 10,000 rows can lead to an inefficient plan at 10 million rows, if the optimizer does not know the actual selectivity.

Especially treacherous are bulk imports and batch deletions outside normal application operation, for example a one time data migration script. These operations drastically change the data distribution but in many configurations do not trigger an automatic recalculation, if the thresholds for automatic statistics refresh have not yet been reached. Anyone who suddenly sees bad plans after a large import should first check when ANALYZE last ran.

5. Automatic refresh: autovacuum and InnoDB persistent statistics

PostgreSQL solves automatic refresh through the autovacuum daemon, which automatically triggers an ANALYZE once a configurable threshold of changed rows is exceeded. The relevant parameters autovacuum_analyze_threshold and autovacuum_analyze_scale_factor together determine after how many changed rows a re analysis is triggered. On very large tables, the percentage based threshold (default 10 percent) can mean that millions of rows must change before a re analysis even starts.

MySQL by default refreshes statistics automatically on certain internal events, for example after significant table size changes, but the behavior is less transparently configurable than in PostgreSQL. In both systems the same rule applies: for very large, rapidly growing tables it is sensible to deliberately lower the automatic thresholds or to add a manual, scheduled ANALYZE after known bulk operations to the maintenance plan.

-- PostgreSQL: lower autovacuum thresholds for a large, volatile table
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,   -- instead of default 0.1
    autovacuum_analyze_threshold = 500
);

-- Check the current autovacuum status
SELECT
    relname,
    last_autoanalyze,
    n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

6. Query plan cache: when the plan itself goes stale

Beyond stale statistics, a second, independent problem exists: the query plan cache itself. PostgreSQL caches the once computed execution plan for reuse on prepared statements, to save planning cost on repeated execution. The problem: a plan that was optimal for the first passed parameter value stays cached, even if a later parameter value would require a completely different access strategy.

MySQL had a global query cache up through version 5.7 that cached entire result sets (not just plans), but it was completely removed in 8.0 due to massive scaling problems under write load. The relevant modern mechanism in MySQL instead is the internal optimizer plan cache per prepared statement within a session, conceptually similar to PostgreSQL's behavior but with different lifetime and invalidation rules.

7. Bind variable peeking and parameter sniffing

The phenomenon where a cached plan was optimal for one particular parameter value but catastrophic for another is called parameter sniffing (in Oracle terminology also bind variable peeking). Starting from the sixth execution of a prepared statement, PostgreSQL automatically decides whether a generic, parameter independent plan or a specific, custom fitted plan is cheaper, by comparing the estimated cost of both variants.

Despite this heuristic, parameter sniffing can still occur, especially with strongly skewed data distributions where no single generic plan works well for all parameter values. The pragmatic workaround is to deliberately avoid PREPARE for known problem cases and instead work with directly embedded literals, or in PostgreSQL to explicitly set plan_cache_mode = force_custom_plan for the affected session.

-- PostgreSQL: control plan cache behavior per session
SET plan_cache_mode = force_custom_plan;  -- always plan fresh, no generic plan
-- Alternative: force_generic_plan (always reuse the same plan)
-- Default: auto (heuristic after the 6th execution)

-- Check the current setting
SHOW plan_cache_mode;

8. Diagnosing stale statistics and plans

The most reliable way to detect stale statistics is comparing the row count estimated by the optimizer against the row count actually returned in the explain output. PostgreSQL's EXPLAIN ANALYZE shows both values side by side: rows=X (estimated) and actual rows=Y (actual). A large deviation, say a factor of 10 or more, is a clear signal for stale or insufficiently granular statistics.

In MySQL, EXPLAIN ANALYZE has offered a similar feature since version 8.0.18, with estimated versus actual costs and row counts per plan step. Anyone who wants to understand statistics and query plan cache and check this systematically should make this comparison a fixed part of every performance analysis, instead of only looking at the pure execution plan without actual values.

9. MySQL and PostgreSQL compared

Both systems solve statistics and plan caching similarly, but with different mechanisms and different configurability.

Despite different implementations, the same principle applies to both systems: statistics must match the actual data distribution, and a cached plan must be regularly validated against current reality, otherwise the optimizer's decision silently drifts away from the optimal solution.

10. Summary

Understanding statistics and query plan cache means not treating the query optimizer as a black box, but knowing its decision basis: estimated cardinality, histograms for skewed distributions, and a once computed plan cached for reuse. Both building blocks can go stale independently as data volumes grow or value distributions shift, and both produce the same symptom: an unchanged query that becomes slower for no apparent reason.

Regular ANALYZE, sensibly configured automatic thresholds, and deliberately comparing estimated versus actual row count in the explain output are the most effective tools to catch this drift early. Anyone who has internalized these relationships diagnoses seemingly random performance regressions significantly faster, because they know exactly where to look first.

11. FAQ: Understanding Statistics and Query Plan Cache