Maintenance

ANALYZE and statistics

ANALYZE reads a random sample of each table and stores a compact summary per column: how many NULLs, how many distinct values, the most common values and a histogram. The planner turns these into row estimates.

In plain words

Before it runs a query, the planner has to guess how many rows each step will return: 10 rows suggests an index, 10 million suggests reading the whole table. It cannot count first, so it relies on a summary made earlier by ANALYZE.

ANALYZE does not read the whole table. It picks a random sample of 300 × default_statistics_target rows (30,000 with the default 100), like an opinion poll. From that sample it notes, for every column, the share of NULLs, roughly how many different values there are, the most common values with how often each appears, and a histogram that splits the other values into equal-sized groups.

A bigger target means a bigger poll and more detail, so rare values are estimated better, at the cost of a slower ANALYZE and slightly slower planning. Autovacuum runs ANALYZE automatically after about 10% of a table has changed. Use the slider to see how the target changes the summary and the estimates.

For DBAs and operators

What is stored

Results go to pg_statistic; read them through pg_stats:

SELECT attname, null_frac, avg_width, n_distinct,
       most_common_vals, most_common_freqs,
       histogram_bounds, correlation
  FROM pg_stats
 WHERE schemaname = 'public' AND tablename = 'orders';

null_frac: share of NULLs. avg_width: average bytes.
n_distinct: positive = a count; negative = a fraction of the row count (-1 means unique), used when distinct values grow with the table. It is estimated from the sample with the Haas–Stokes estimator and is often too low for skewed columns; override with ALTER TABLE … ALTER COLUMN … SET (n_distinct = …).
most_common_vals / freqs: up to target values that are clearly more frequent than average.
histogram_bounds: up to target + 1 boundaries splitting the remaining (non-MCV) values into equal-population buckets; used for ranges and for values not in the MCV list.
correlation: how well the physical row order follows the column order (1 = perfectly sorted). It drives the cost of index scans.

The sample is 300 × the largest statistics target among the analyzed columns. The target can be set per column (ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000, max 10000). On PostgreSQL 18.6, ANALYZE VERBOSE city on a 100,000-row table reports:

INFO: "city": scanned 541 of 541 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows

From statistics to estimates

col = const in the MCV list → its frequency × reltuples.
Not in the list → (1 − sum of MCV freqs − null_frac) / (n_distinct − number of MCVs).
Ranges → position in the histogram (plus matching MCVs).
Several conditions are multiplied as if independent. That is the classic misestimate for correlated columns (city and country, model and make).

Extended statistics

CREATE STATISTICS teaches the planner about column groups: dependencies (functional dependencies), ndistinct (for GROUP BY on several columns) and mcv (multi-column most common values). Measured on PostgreSQL 18.6 with a table where the city determines the country:

EXPLAIN ANALYZE SELECT * FROM city
 WHERE country = 'FR' AND city = 'c3_3';
-- without:  rows=294   (actual 2041)
CREATE STATISTICS city_dep (dependencies, ndistinct, mcv)
  ON country, city FROM city;
ANALYZE city;
-- with:     rows=2030  (actual 2041)

Look at them in pg_stats_ext. They are only built by ANALYZE.

Gotchas

Statistics are not carried over by pg_upgrade before PostgreSQL 18; since 18 pg_upgrade keeps most of them (not extended statistics), but running vacuumdb --all --analyze-in-stages --missing-stats-only afterwards is still advised.
Autovacuum never analyzes partitioned parent tables or foreign tables; run ANALYZE on them yourself.
Temporary tables are invisible to autovacuum: ANALYZE them after loading.
Bad plan after a bulk load? Run ANALYZE right after the load instead of waiting for autovacuum.

For developers: inside the source

Where it lives

analyze.c: sampling and the standard per-column statistics.
src/backend/statistics: extended statistics (extended_stats.c, dependencies.c, mvdistinct.c, mcv.c, each with a README) and, new in PostgreSQL 18, relation_stats.c/attribute_stats.c behind pg_restore_relation_stats() and pg_restore_attribute_stats() (what pg_dump and pg_upgrade now use).
selfuncs.c and clausesel.c: turning statistics into selectivities.

Call flow

analyze_rel() → do_analyze_rel(): examine_attribute() for each column calls the type's typanalyze function, by default std_typanalyze(), which picks compute_scalar_stats() (type has = and <), compute_distinct_stats() (only =) or compute_trivial_stats(), and sets minrows = 300 * attstattarget (the comment cites Chaudhuri, Motwani and Narasayya).
acquire_sample_rows(): BlockSampler_Init() picks blocks, a read stream reads them, table_scan_analyze_next_block()/table_scan_analyze_next_tuple() return rows, and Vitter's reservoir (reservoir_get_next_S()) keeps targrows. Inheritance and partition trees go through acquire_inherited_sample_rows(); foreign tables through the FDW's AnalyzeForeignTable callback, which returns an AcquireSampleRowsFunc.
The compute function fills VacAttrStats slots (stakind, stavalues, stanumbers); compute_scalar_stats() estimates n_distinct with the Haas–Stokes Duj1 formula n*d / (n - f1 + f1*n/N).
update_attstats() writes pg_statistic; BuildRelationExtStatistics() builds extended statistics from the same sample; vac_update_relstats() sets reltuples/relpages.

Using the statistics

eqsel() → var_eq_const() uses the MCV list or the "rest divided by remaining distinct" formula. scalarineqsel() combines mcv_selectivity() and ineq_histogram_selectivity(). get_variable_numdistinct() serves GROUP BY and joins. clauselist_selectivity() multiplies clauses, after first applying any matching extended statistics.

Extension points and tests

get_relation_stats_hook and get_index_stats_hook (selfuncs.h) let an extension supply statistics to the planner; a custom data type can set its own typanalyze; a table AM implements scan_analyze_next_block/scan_analyze_next_tuple.
Regression tests: stats_ext.sql (extended statistics, with estimated vs actual row checks) and stats_import.sql (the PostgreSQL 18 restore functions).

Parameters that shape this

default_statistics_target autovacuum_analyze_threshold autovacuum_analyze_scale_factor maintenance_work_mem vacuum_cost_delay