Postgres ANALYZE: How the Planner Gets Its Estimates and Why They Go Stale

Postgres uses a cost-based query planner. For any given query, the planner enumerates possible execution plans, estimates the cost of each, and picks the cheapest. The cost estimates depend almost entirely on a single source of truth: the statistics collected by ANALYZE and stored in pg_statistic. When those statistics are wrong, every cost estimate the planner produces is wrong, and the chosen plan is often wrong with it. The result is queries that are 100x slower than they need to be, sequential scans where an index would serve, and nested loops with millions of rows on the outer side. Almost every bad query plan we have ever debugged traced back to stale or insufficient statistics, and the fix was a one-line ANALYZE or a configuration adjustment to make autovacuum analyze more aggressive.

What ANALYZE actually stores

For each column in each table, ANALYZE samples a configurable number of rows (default 300 times default_statistics_target, so 30000 rows at the default target of 100) and computes summary statistics: the number of distinct values, the fraction of nulls, the most common values and their frequencies, and a histogram of the value distribution. These statistics are stored in pg_statistic and consulted by the planner during every query plan.

The most common values list (MCV) is the most consequential single piece of statistical information. For a column with a small number of dominant values, the MCV captures most of the distribution and the planner can estimate selectivity for equality predicates against MCV values directly. For a column with a long tail, the histogram captures the rest of the distribution and the planner estimates selectivity for range predicates from the histogram boundaries. The fraction of nulls is used for IS NULL and IS NOT NULL predicates. The n_distinct value is used for GROUP BY and DISTINCT cost estimates.

What is not in pg_statistic is correlations between columns. By default, Postgres assumes that predicates on different columns are independent, so the selectivity of WHERE a = 1 AND b = 2 is estimated as selectivity(a=1) times selectivity(b=2). For correlated columns, this assumption is dramatically wrong. The fix, available since Postgres 10, is the extended statistics mechanism: CREATE STATISTICS on (col1, col2) tells Postgres to compute and store multi-column statistics that capture correlation. This is one of the most underused features in production Postgres.

When statistics go stale

Statistics go stale when the underlying data distribution changes. The four most common cases:

Bulk loads. A bulk import of millions of rows changes the data distribution dramatically. The MCV and histogram both shift, and autovacuum analyze does not catch the change for a while because the default threshold is 10 percent of the table changed. For a table that was 1 million rows and grew to 10 million, the 10 percent threshold is now 1 million rows of additional change, which can take a long time. The fix is to run ANALYZE manually after any bulk load, before letting any production queries run against the loaded table.

New indexes. A new index does not automatically have statistics, and queries that would use the index may not because the planner cannot estimate its selectivity. After CREATE INDEX, the index has minimal statistics until ANALYZE runs. The fix is to run ANALYZE on the indexed columns after creating an index, particularly if the index is on an expression rather than a simple column.

Correlated columns. When the data is loaded in a way that produces correlation between columns (timestamps and entity IDs created together, status and updated_at, type and resource_id) the planner's independence assumption produces wildly wrong estimates. The fix is CREATE STATISTICS on the correlated columns and a subsequent ANALYZE.

Extreme skew. When one or a few values account for most of the data, the MCV captures the situation but the histogram for the rest is sparse. The planner may overestimate the selectivity of rare values because it has limited histogram resolution. The fix is to raise default_statistics_target for the affected columns via ALTER TABLE ALTER COLUMN SET STATISTICS, then ANALYZE.

The diagnostic loop

The diagnostic for stale statistics starts with EXPLAIN ANALYZE on the slow query. The relevant signal is the ratio between estimated rows and actual rows. If the planner estimates 10 rows and the actual is 10000, the planner has been misled by stale or insufficient statistics. If the ratio is reversed (estimated 10000, actual 10) the consequence is usually less dramatic because the planner will have chosen a plan that handles the larger number, and the smaller actual number will just be processed faster than expected.

The pattern of estimate-vs-actual mismatch usually points at which statistics are wrong. A bad estimate on a single-column predicate means that column's statistics are stale or insufficient. A bad estimate on a multi-column predicate where each individual column would be estimated reasonably points at column correlation that needs extended statistics. A bad estimate on a join row count points at the join selectivity model running on bad column statistics for the join columns.

The pg_stat_user_tables view tracks last_analyze and last_autoanalyze timestamps. A table that has been heavily modified but not analyzed in months is a candidate for manual ANALYZE. The pg_stats view exposes the current statistics for each column: most_common_vals, most_common_freqs, histogram_bounds, n_distinct, null_frac. Reading pg_stats for a column where the planner estimate seems wrong often shows immediately what is missing.

Autovacuum analyze tuning

The default autovacuum analyze threshold is 10 percent of the table changed plus 50 rows. For small tables this is reasonable; for tables with millions of rows it can be too conservative. The fix is per-table autovacuum_analyze_scale_factor settings: ALTER TABLE big_table SET (autovacuum_analyze_scale_factor = 0.02) brings the threshold down to 2 percent, which for a 10 million row table is 200000 changes rather than 1 million. Tables with very different write patterns (append-mostly vs heavily updated vs heavily deleted) deserve different scale factors. The audit pattern is to query pg_stat_user_tables for tables where last_autoanalyze is more than a few days old despite significant n_tup_ins or n_tup_upd or n_tup_del activity since.

The autovacuum_analyze_cost_limit and cost_delay parameters control how aggressively autovacuum analyze can run. The default values are conservative for spinning disk and are typically too low for NVMe-based systems. Raising autovacuum_vacuum_cost_limit from 200 to 2000 or higher on modern hardware lets analyze and vacuum keep up with write load that would otherwise leave them perpetually behind. This is one of the most impactful single configuration changes for write-heavy Postgres.

Extended statistics

CREATE STATISTICS is the mechanism for telling Postgres about column correlations that affect query plans. Three kinds of extended statistics are supported: ndistinct (number of distinct combinations of the listed columns), dependencies (functional dependencies between columns), and mcv (multi-column most common values). For a typical use case where two columns are correlated and the planner is producing bad estimates for predicates on both, CREATE STATISTICS (dependencies, mcv) on (col1, col2) FROM table_name followed by ANALYZE table_name fixes the estimates.

The cost is small: extended statistics add a small amount to ANALYZE runtime and a small amount to planning time. The benefit is large when the columns are genuinely correlated. The discovery pattern is to look at queries with bad estimates on multi-column predicates and try CREATE STATISTICS; if the estimates improve dramatically, the statistics are doing their job.

What ANALYZE does not do

ANALYZE does not change query plans currently in plan cache. If you ran ANALYZE to fix a plan and the application is still slow, the application may be using prepared statements with cached plans. Reconnecting or running DISCARD ALL clears plan cache on the affected connection. Connection-pooled applications need to drain and refresh connections for the new plans to take effect.

ANALYZE does not change the planner's mind about the cost of operations. It changes the estimates of how many rows operations will process. If the planner is consistently picking the wrong operation type (sequential scan instead of index scan, hash join instead of nested loop) and the row estimates are correct, the problem is somewhere else: random_page_cost too high for SSD storage, effective_cache_size too low for the actual available memory, or work_mem too low forcing operations to spill to disk.

ANALYZE does not capture statistics for non-leaf rows of partitioned tables. For partitioned tables, each partition has its own statistics, and the parent table aggregate statistics are computed separately. Partitioned tables need ANALYZE at both the partition level and the parent level for correct planning.

Our use across the four products

Our four products run on SQLite, which has a different statistics mechanism (ANALYZE in SQLite stores summary information in the sqlite_stat tables) but the same essential point: the planner relies on statistics, and the statistics need to be current. We run ANALYZE on schema migrations and after any bulk data operations. When we migrate to Postgres in the future, the same discipline transfers with somewhat more configuration depth available: per-table autovacuum tuning, extended statistics for correlated columns, and explicit ANALYZE in migrations that change data distribution.

The deeper observation is that the query planner is one of the great engineering achievements of the relational database era, but its quality depends entirely on the quality of its input statistics. Most teams treat ANALYZE as something autovacuum handles in the background, which is correct most of the time but wrong in the specific cases that produce production query performance incidents. Knowing when to run ANALYZE manually, when to tune autovacuum thresholds, and when to add extended statistics is one of the durable skills that distinguishes operators who can keep Postgres running well under load from those who cannot.