ANALYZE and planner statistics · PostgreSQL

ANALYZE shares a command with VACUUM and an autovacuum daemon with it, but it solves a different problem. Vacuum manages space. Analyze manages the planner’s model of your data, and when that model is wrong the planner makes confident, catastrophic choices.

What it costs to skip

A million-row table. One value, rare, occurs once per thousand rows; common is everything else. An index exists on the column. The table has been loaded but not analysed.

Read-only / Safebefore ANALYZE— Captured on 18.6. reltuples is already correct because CREATE INDEX set it; the per-column distribution is what is missing.

$ psql -U postgres -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM stats_demo WHERE category='rare'"

   ->  Bitmap Heap Scan on stats_demo  (cost=59.17..5640.65 rows=5000 width=0) (actual rows=1000.00 loops=1)
       Recheck Cond: (category = 'rare'::text)
       Heap Blocks: exact=1000
       Buffers: shared hit=1000 read=3

Read-only / Safeafter ANALYZE, same query, same data— Captured on 18.6. The only thing that changed is that statistics now exist.

$ psql -U postgres -c "ANALYZE stats_demo" -c "EXPLAIN (ANALYZE, BUFFERS, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM stats_demo WHERE category='rare'"

   ->  Index Only Scan using stats_demo_cat_idx on stats_demo  (cost=0.42..24.85 rows=1167 width=0) (actual rows=1000.00 loops=1)
       Index Cond: (category = 'rare'::text)
       Heap Fetches: 0
       Buffers: shared hit=4

1003 buffers to 4. The estimate moved from 5,000 to 1,167 against an actual of 1,000, and that was enough to change the plan from a bitmap heap scan touching a thousand pages to an index-only scan touching four.

Without statistics the planner falls back on defaults — for an equality test on a text column, a fixed selectivity guess. It was not being stupid; it had nothing to work with.

What ANALYZE stores

Read-only / Safethe statistics collected for that column— Captured on 18.6 from pg_stats, which presents pg_statistic readably.

$ psql -U postgres -x -c "SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, avg_width FROM pg_stats WHERE tablename='stats_demo' AND attname='category'"

attname           | category
null_frac         | 0
n_distinct        | 2
most_common_vals  | {common,rare}
most_common_freqs | {0.99883336,0.0011666666}
avg_width         | 6

Statistic	What the planner does with it
`null_frac`	Estimates `IS NULL` and `IS NOT NULL`
`n_distinct`	Estimates equality selectivity, and group counts for `GROUP BY`
`most_common_vals` / `_freqs`	Exact selectivity for the listed values
`histogram_bounds`	Range selectivity for values not in the MCV list
`correlation`	How well physical order matches logical order, which decides index scan cost

n_distinct is stored as a negative number when it scales with the table — -1 means every value is unique, -0.5 means distinct values are half the row count. A positive number is an absolute count. Reading -1 as “one distinct value” is a common and expensive misinterpretation.

Where statistics go wrong

Correlated columns

The planner assumes columns are independent. When they are not, it multiplies selectivities that should not be multiplied.

Read-only / Safetwo perfectly correlated columns— Captured on 18.6. a and b are both g%100, so a=5 implies b=5, and 10,000 rows match.

$ psql -U postgres -c "EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM corr WHERE a=5 AND b=5"

 ->  Parallel Seq Scan on corr  (cost=0.00..11690.00 rows=45 width=0) (actual rows=3333.33 loops=3)

45 estimated per worker against 3,333 actual. The planner took 1/100 for a=5, 1/100 for b=5, multiplied them, and got 1/10,000 of the table.

Configuration changethe same query after CREATE STATISTICS— Captured on 18.6. Extended statistics teach the planner that the columns are dependent.

$ psql -U postgres -c "CREATE STATISTICS corr_ab (dependencies, ndistinct) ON a, b FROM corr" -c "ANALYZE corr" -c "EXPLAIN (ANALYZE, COSTS ON, TIMING OFF, SUMMARY OFF) SELECT count(*) FROM corr WHERE a=5 AND b=5"

 ->  Parallel Seq Scan on corr  (cost=0.00..11690.00 rows=3820 width=0) (actual rows=3333.33 loops=3)

45 to 3,820, against an actual of 3,333.

The plan did not change here, because a sequential scan was correct either way. That is worth being precise about: the estimate is what improved, and the estimate is what a join above this node would be planned from. A 74-fold underestimate feeding a join is how you get a nested loop over ten thousand rows instead of a hash join.

Real cases where this bites: city and postcode, country and currency, product and category, any status column paired with a type column.

CREATE STATISTICS orders_geo (dependencies, ndistinct, mcv)
    ON city, postcode FROM orders;
ANALYZE orders;

mcv adds multi-column most-common-value lists, which handles skew that dependencies alone does not.

Sample size

default_statistics_target is 100, meaning ANALYZE samples roughly 300 × 100 = 30,000 rows and keeps up to 100 MCVs and 100 histogram buckets.

That is enough for most columns and not enough for a column with high cardinality and heavy skew — one where a few values dominate but there are thousands of distinct values, so the important ones fall outside the top 100.

-- per column, which is almost always the right scope
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

Raising it globally makes every ANALYZE slower and every plan marginally more expensive to produce. Raise it on the column that needs it, having identified that column from a plan whose estimate is wrong.

Diagnosing a bad plan: estimated against actual

EXPLAIN (ANALYZE, BUFFERS) prints both, and the ratio between them is the diagnosis.

->  Seq Scan on orders  (cost=... rows=45 ...) (actual rows=3333 loops=3)
                              ^^^^^^^^                ^^^^^^^^^^^
                              planner's estimate      reality

Within an order of magnitude. The statistics are fine. If the plan is still bad, the problem is elsewhere — cost settings, missing index, or the query itself.
Off by 10× to 100× on a single-column predicate. Stale statistics or an inadequate sample. ANALYZE first; then SET STATISTICS on that column.
Off by 10× or more only when two predicates are combined. Correlated columns. CREATE STATISTICS.
Off at the top of the plan but accurate at the leaves. The per-table estimates are right and the join selectivity is wrong. Usually correlation across tables, which extended statistics do not cover; the fix is often to restructure the query.

Read the plan from the bottom up and find the first node where estimate and actual diverge. Everything above it is downstream of that error, and fixing the top of the plan when the error is at the bottom is the most common wasted afternoon in query tuning.

When autoanalyze is not enough

Autoanalyze fires at autovacuum_analyze_threshold (50) plus autovacuum_analyze_scale_factor (0.1) times live tuples, counting inserts, updates and deletes. It handles the ordinary case. Four situations where it will not:

Immediately after a bulk load or restore. Nothing has run yet. ANALYZE explicitly — and note that pg_restore does not do it for you.

After a schema change. ALTER TABLE … ADD COLUMN with a default, or a type change, invalidates what was known about that column.

On a large, append-only table with a time column. 10% of a billion rows is a hundred million inserts before autoanalyze fires. Meanwhile every query filtering on created_at > now() - interval '1 hour' is asking about a range beyond the last histogram bucket, where the planner estimates almost nothing matches. This is the single most common statistics failure in production, and it is invisible until a plan flips.

ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01);

On a partitioned table. Statistics on the parent are not maintained by autovacuum in the way per-partition statistics are. Queries planned against the parent may need ANALYZE parent_table run explicitly, and this is a well-known source of surprise on partitioned schemas.

n_distinct on a large table, and the case for overriding it

ANALYZE estimates n_distinct from a sample, and estimating the number of distinct values in a population from a sample is a genuinely hard statistical problem. The known failure is systematic: it tends to underestimate, and it gets worse as the table grows, because a fixed 30,000-row sample sees a shrinking fraction of a growing table.

The consequence is a planner that thinks a column has 50,000 distinct values when it has 5,000,000. Equality selectivity is estimated as 1/n_distinct, so it expects each value to match a hundred times more rows than it does, and it chooses a sequential scan or a hash join where an index scan or a nested loop would win.

The symptom is a query that was fine and got slower as the table grew, with no schema or query change — the plan flipped when the estimate drifted far enough.

PostgreSQL lets you override the estimate:

-- absolute count
ALTER TABLE events ALTER COLUMN session_id SET (n_distinct = 5000000);

-- or as a negative fraction of the table, which scales as it grows
ALTER TABLE events ALTER COLUMN session_id SET (n_distinct = -0.3);
ANALYZE events;

The negative form is usually the better choice, because it keeps tracking as the table grows rather than becoming stale at a fixed value.

Verify before overriding, and verify with the real number:

SELECT n_distinct FROM pg_stats
 WHERE tablename = 'events' AND attname = 'session_id';

SELECT count(DISTINCT session_id) FROM events;   -- expensive, do it once

This is a setting to reach for when you have a specific wrong estimate and the true value in hand. Setting it from a guess replaces the planner’s bad estimate with yours, which is not obviously an improvement.

What to take from this

Statistics changed one measured query from 1003 buffers to 4. They are not optional after a load.
n_distinct negative means “scales with the table”. -1 is unique, not one.
The planner assumes column independence. CREATE STATISTICS is the fix, and it fixes the estimate even when the plan is unchanged.
Diagnose from estimate against actual, bottom up, at the first diverging node.
Autoanalyze misses bulk loads, schema changes, large append-only tables and partitioned parents. Handle those explicitly.

Cross-course references

Observability for Production Sysadmins — Part LIX (Database observability) covers exporting last_analyze and n_mod_since_analyze, which are what turn a stale-statistics incident into a threshold.
Git, CI/CD & GitOps — Part CXIV (Deployment markers) covers annotating a bulk load on the same timeline as the plan regression it causes.