Statistics, ANALYZE, and how PostgreSQL estimates queries Intermediate

Problem

Table tenant_demo has 100,000 rows. The columns tenant_id (1-100) and region (west/east) are not independent: the data is generated so tenants 1-50 are always west and tenants 51-100 always east. The query:

SELECT * FROM tenant_demo WHERE tenant_id = 42 AND region = 'west';

In reality this matches exactly 1,000 rows (all of tenant 42’s data). But the planner estimates:

Seq Scan on tenant_demo  (cost=0.00..2236.00 rows=489 width=26) (actual time=0.008..2.167 rows=1000 loops=1)
  Filter: ((tenant_id = 42) AND (region = 'west'::text))
Execution Time: 2.206 ms

rows=489 but actual rows=1000: less than half.

Actual                  1000  ████████████████████████████████████████
Default (independent)    489  ███████████████████▌
After CREATE STATISTICS 1060  ██████████████████████████████████████████▍

For this simple query it does not matter (still a Seq Scan, the plan does not change). But if tenant_demo is one side of a large join, a 2x error here can make the planner pick the wrong join algorithm above it (see Nested loop, hash join, and merge join).

Core concepts

The planner does not count the table for every query. It looks up pre-collected statistics from the most recent ANALYZE and guesses the number of matching rows.

Statistic (per column)	Used to
most_common_vals + frequencies (MCV)	Estimate col = x for common values
histogram_bounds	Estimate ranges <, >, BETWEEN
n_distinct	Estimate col = x for rare values, GROUP BY
correlation	Choose Index Scan or Bitmap (does physical order match logical order?)

By default, statistics are stored per individual column. For A AND B, the planner assumes independence and multiplies the two selectivities. The assumption holds most of the time and fails when two columns are correlated, like tenant_id and region.

Default numbers to remember

Parameter	Default	Meaning
default_statistics_target	100	At most 100 MCVs and 100 histogram buckets per column
Rows sampled	about 300 × target (i.e. 30,000 rows)	The column with the largest target determines the sample size
autovacuum_analyze_threshold	50	Minimum number of changed rows
autovacuum_analyze_scale_factor	0.1	Plus 10% of the table size
autovacuum_naptime	1 minute	How often autovacuum checks each database

A 100,000-row table: auto-analyze runs once about 50 + 0.1 × 100,000 = 10,050 rows have changed since the last analyze. A 100-million-row table needs 10 million changed rows, so statistics can stay stale for a long time. For large tables, lower autovacuum_analyze_scale_factor per table.

TEMP tables are never auto-analyzed: autovacuum cannot access temporary tables. Every demo in this series calls ANALYZE manually for that reason. After a bulk load, ANALYZE right away. PostgreSQL 18 keeps table statistics across pg_upgrade, but not extended statistics; PG17 and earlier need ANALYZE after an upgrade.

How it works

Why 489 and not 1000?

Condition	Estimated selectivity
tenant_id = 42	Matches 1/100 of values → ~1%
region = 'west'	Matches about 50% of the data
Both (multiplied)	1% × 50% × 100,000 ≈ 500 (the planner computes 489, close to this formula)

This is the “independence assumption” calculation: it treats any tenant as having a 50% chance of landing in each region.

In reality, tenant 42 is always west; there is no 50-50 chance at all. All 1,000 of tenant 42’s rows match both conditions, not half of them. The gap between 489 and 1,000 is the price of a wrong independence assumption.

Multiplying per-column selectivity yields 489; extended statistics bring the estimate to 1060

Enlarge diagram

tenant_demo → tenant_id = 421%

◀ Prev▶ PlayNext ▶↺ RestartStep 1/4

Default statistics are stored per column. tenant_id = 42 matches 1 of 100 values, so the planner estimates about 1% of 100,000 rows.

Fixing it with CREATE STATISTICS

CREATE STATISTICS tenant_demo_tenant_region_stats (dependencies, mcv)
ON tenant_id, region FROM tenant_demo;
ANALYZE tenant_demo;

CREATE STATISTICS does not collect data by itself; it only declares “watch the correlation between these two columns”. The following ANALYZE does the actual computation. Run the exact same query again:

Seq Scan on tenant_demo  (cost=0.00..2236.00 rows=1060 width=26) (actual time=0.012..2.030 rows=1000 loops=1)
  Filter: ((tenant_id = 42) AND (region = 'west'::text))
Execution Time: 2.056 ms

489 → 1060, against an actual 1000. From an estimate of only 49% of reality to 106%: an error in the opposite direction, but much smaller.

(dependencies, mcv) asks PostgreSQL to record both the degree of dependency between the two columns (dependencies) and the common value combinations (mcv), enough to recognize that “knowing tenant_id nearly determines region”.

Confirm the statistics exist and which kinds are stored:

         statistics_name         | kinds
---------------------------------+-------
 tenant_demo_tenant_region_stats | {f,m}

f is functional dependencies, m is MCV, matching the two kinds declared.

Viewing the statistics the planner uses

-- Per-column statistics
SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'tenant_demo';

-- Declared extended statistics (pg_stats_ext shows the collected content)
SELECT stxname, stxkeys, stxkind FROM pg_statistic_ext;

-- Most recent analyze
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'tenant_demo';

Comparing n_mod_since_analyze with the threshold 50 + 0.1 × reltuples tells you whether auto-analyze is about to run or is overdue.

Kinds of extended statistics

Kind	Fixes	Example
dependencies	Column A determines column B	zip → city; applies only to = conditions with constants and IN
ndistinct	GROUP BY a, b misestimates the number of groups	GROUP BY city, state
mcv	Common value combinations, also supports range conditions	The pair (country, currency)
expressions (PG14 and later)	Conditions on expressions	WHERE date_trunc('day', ts) = ...

Extended statistics only improve estimates within one table. Misestimates on join conditions between two tables are not fixed by CREATE STATISTICS.

Diagnostic procedure when you suspect a bad estimate

1. Run EXPLAIN (ANALYZE, BUFFERS) and compare rows= (estimate) with actual rows= at the lowest node with a large error; an error at a lower node propagates up to its parents.
2. If only a single-column condition is off, first try ANALYZE again (a bulk load may not have been auto-analyzed yet) or raise ALTER TABLE ... ALTER COLUMN ... SET STATISTICS for that column alone.
3. If the error appears exactly when multiple conditions are combined, as above, that signals correlation between columns: use CREATE STATISTICS.
4. Re-measure with EXPLAIN ANALYZE after each change, one factor at a time; do not raise the target and add extended statistics together and then not know which one helped.

Production checklist

1. Track n_mod_since_analyze and last_autoanalyze for large tables; for tables where auto-analyze lags, lower autovacuum_analyze_scale_factor for that table.
2. After a bulk load, import, or pg_upgrade (PG17 and earlier): run ANALYZE manually right away.
3. Investigate bad estimates starting from the lowest node where rows diverges from actual rows.
4. One column off: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 then ANALYZE, for that column only.
5. Several conditions off together: CREATE STATISTICS for exactly the correlated column pair, then ANALYZE.
6. Change one factor at a time and re-measure with EXPLAIN (ANALYZE, BUFFERS).
7. TEMP tables: always ANALYZE manually.

Real-world example

Real-world example

An events table has country and currency columns: nearly every row with country = 'VN' has currency = 'VND'.

A dashboard filtering on both columns for a regional report can make the planner misjudge the number of rows to process, leading to the wrong join algorithm or the wrong hash table size above it.

Before suspecting indexes or hardware, check rows against actual rows at the node filtering those two columns. If it is off by several times, CREATE STATISTICS (dependencies, mcv) ON country, currency FROM events is usually the targeted fix, far cheaper than adding an index or rewriting the query.

Trade-offs

Pros

• Lets the planner estimate without counting the whole table exactly every time it plans
• CREATE STATISTICS fixes the root cause (missed correlation) instead of forcing a plan by hand
• Can be applied selectively to exactly the suspect column pair, without enabling it for the whole table or database

Cons

• Statistics are always estimates from a sample, not exact counts; even after the improvement, 1060 is not exactly 1000
• CREATE STATISTICS does not collect automatically; forget ANALYZE after creating it and the object exists but is useless
• Raising default_statistics_target for every column/table slows ANALYZE and uses more catalog space, when extended statistics on one column pair may be all you need

Common mistakes

Common pitfall

Raising default_statistics_target database-wide as soon as one query has a bad estimate. In the example above, the problem is not “statistics samples too coarse”; it is missing information about the correlation between two specific columns, which raising the target for each column cannot solve (each column is still analyzed independently).

Identify the kind of cause first; two different diseases, two different cures:

Cause	Cure
Missing detail	Raise the target for one column
Missing correlation	Extended statistics

Summary

Key takeaways

• The planner estimates row counts from stored statistics (MCV, histogram, ndistinct) from the most recent ANALYZE, not by counting exactly for each query
• By default, multiple conditions are treated as independent and their probabilities multiplied, which is wrong when the columns really are correlated
• In the demo: the independence assumption gave an estimate of 489 against an actual 1000 (only 49%); after CREATE STATISTICS (dependencies, mcv) the estimate became 1060 (106%, much closer)
• Diagnose by comparing rows with actual rows at the lowest node that is far off, rather than vaguely guessing “stale statistics”
• Defaults: target 100, a sample of about 30,000 rows, auto-analyze once changes exceed 50 + 10% of the table size; TEMP tables are never auto-analyzed
• ANALYZE table updates statistics for the planner; EXPLAIN ANALYZE query runs the query for real and reports runtime measurements: same word, completely different purposes

References

• PostgreSQL 17 — Statistics Used by the Planner
• PostgreSQL 17 — ANALYZE
• PostgreSQL 17 — CREATE STATISTICS