How large a sample does PostgreSQL's ANALYZE take, and what do default_statistics_target and ALTER TABLE ... SET STATISTICS actually control?
answer
- sample size ignores table size
- three hundred times the target
- largest column target sets the sample
- caps MCV entries and histogram buckets
- session SET versus autovacuum
basics
~20 sANALYZE samples 300 × the statistics target rows: 30,000 at the default target of 100, whatever the table's size. The target also caps each column's most-common-value list and histogram buckets; SET STATISTICS overrides it per column, up to 10000.
solid answer
~50 s`ANALYZE` samples `300 × target` rows in two stages, random blocks and then a reservoir sample of rows inside them, so at the default `default_statistics_target = 100` a ten-million-row and a ten-billion-row table both get about 30,000 rows. The largest target among the analyzed columns sets the sample size. Each column's own target caps how many `most_common_vals` entries and histogram buckets it keeps. `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n` (0 to 10000, where 0 disables collection) overrides the target for one column; since PostgreSQL 17, `SET STATISTICS DEFAULT` removes the override. Every change takes effect at the next `ANALYZE`. A higher target gives skewed columns better MCVs and histograms, at the cost of a longer `ANALYZE`, a bigger `pg_statistic` and slightly slower estimation. `default_statistics_target` can be set per session, but autovacuum workers use the server's value.
code
sql · 12 linesSHOW default_statistics_target; -- 100 unless changed
ALTER TABLE delivery_addresses ALTER COLUMN city SET STATISTICS 1000;
ANALYZE delivery_addresses; -- now samples up to 300,000 rows
SELECT attname, cardinality(most_common_freqs) AS mcv_entries
FROM pg_stats
WHERE tablename = 'delivery_addresses'
AND attname IN ('city', 'postal_code');
-- PostgreSQL 17 and later: drop the per-column override again
ALTER TABLE delivery_addresses ALTER COLUMN city SET STATISTICS DEFAULT;go deeper
Know that ANALYZE works from a sample, that the default statistics target is 100, and that it can be raised for a single column.
Explain the 300 x target sample size, that the largest column target sets it, and that the target caps MCV entries and histogram buckets.
Show you raise targets per column for skewed or flipping cases, know that autovacuum ignores a session SET, and know what a bigger target cannot fix.
Weigh the cost of a cluster-wide target change against targeted per-column settings, and own where those overrides are recorded and reviewed.
## A sample sized by the target, not by the table `ANALYZE` does not read a fixed percentage of a table. Each column asks for **300 × its statistics target** rows, and the table is sampled once, for the largest of those requests among the columns being analyzed. With the default `default_statistics_target = 100` that is **30,000 rows**, whether the table holds one million rows or one billion. The factor 300 comes from a result on histogram sampling that is cited in `analyze.c`. The sample needed for a given histogram accuracy grows with the number of buckets but only logarithmically with the table size, so scaling the sample to the table buys almost nothing. The sample is taken in two stages: 1. pick up to that many **random blocks** of the table (all of them if the table is smaller); 2. read those blocks and keep a uniform **reservoir sample** of the live rows found in them. Because the sample is random, the statistics change slightly on every run even when the data does not. Now and then a borderline plan flips after a routine `ANALYZE`, and the documented remedy is to collect more statistics. ## What the target caps The same number also limits what is stored for each column: the **maximum number of entries in `most_common_vals`** and the **maximum number of histogram buckets** behind `histogram_bounds`. | Target | Rows sampled (if it is the table's largest) | Max MCV entries | Max histogram buckets | |---|---|---|---| | 0 | not counted | no statistics for that column | none | | 100 (default) | 30,000 | 100 | 100 | | 1,000 | 300,000 | 1,000 | 1,000 | | 10,000 (maximum) | 3,000,000 | 10,000 | 10,000 | The MCV list is often shorter than the cap, or missing. `ANALYZE` keeps only values that are significantly more common than the values left out, so a unique column has no MCV list at all. ## Where a column's target comes from 1. A per-column value set with `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n`, from 0 to 10000, stored in `pg_attribute.attstattarget`. 2. Otherwise `default_statistics_target` (default 100, allowed range 1 to 10000), as set in the process that runs the `ANALYZE`. `SET STATISTICS DEFAULT`, added in PostgreSQL 17, removes a per-column value; the older spelling `SET STATISTICS -1` still works. An extended statistics object has its own target, set with `ALTER STATISTICS ... SET STATISTICS`; without one it uses the largest target among its columns, and only then the default. All of these act at the **next** `ANALYZE`. Changing a target leaves the current statistics untouched. `default_statistics_target` can be changed by any user for their own session, and that is a trap. A DBA who runs `SET default_statistics_target = 1000` and then `ANALYZE` gets rich statistics. The next auto-analyze, though, runs in an autovacuum worker that uses the server's configured value and rewrites them at 100. Make durable changes per column with `SET STATISTICS`, or server-wide in `postgresql.conf` or with `ALTER SYSTEM`. ## When to raise it - **Skewed columns.** A frequent value that missed the MCV cut is estimated with the average leftover share. On a `delivery_addresses` table that is typical of `city` or `postal_code`. - **Range predicates on irregular distributions.** There, 100 buckets are too coarse. - **Plans that flip** after routine analyzes because an estimate sits close to a cost crossover. The opposite move exists too. For wide columns that never appear in `WHERE`, `GROUP BY` or `ORDER BY`, such as a free-text delivery note, `SET STATISTICS 0` stops `ANALYZE` from collecting anything, which saves work and catalog space because the planner has no use for those statistics. Raise it per column first. A server-wide increase multiplies `ANALYZE` time and the size of `pg_statistic` for every table, and it makes estimation slightly slower for queries on columns with the bigger arrays. ## What it cannot fix - **Correlation between columns.** Per-column statistics, however detailed, are combined as if the columns were independent. That needs `CREATE STATISTICS`. - **Distinct counts on huge tables.** Even 3,000,000 rows is a small fraction of a multi-billion-row table, and `n_distinct` extrapolates badly from a sample. The fix there is the `n_distinct` per-column option. - **Stale statistics.** Detailed statistics computed last month still describe last month's data.
- A DBA runs SET default_statistics_target = 1000 in psql and then ANALYZE delivery_addresses. Why are the richer PostgreSQL statistics gone a day later?The `SET` lasted only for that session. The next auto-analyze ran in an autovacuum worker that uses the server's configured target, 100 by default, and rewrote the column statistics with the shorter lists. To make it stick, set `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS` on the columns that need it. Raising the target server-wide also works, but it makes every table's `ANALYZE` more expensive.
- Why can a higher statistics target stop a PostgreSQL query plan from flipping after each routine ANALYZE?Each `ANALYZE` draws a new random sample, so the MCV frequencies and histogram edges move a little every time. When an estimate sits near the point where two plans cost the same, that noise is enough to switch the plan. A larger sample makes the statistics less variable, which is the remedy the `ANALYZE` reference gives.
An opinion poll of a few thousand voters is about as precise for a whole country as for one city, because precision depends on the sample size, not the population. ANALYZE works the same way, and like a poll it is weakest at answering how many different answers exist.
- ANALYZE samples a fixed percentage, so bigger tables get bigger samples
- default_statistics_target is the number of rows ANALYZE reads
- SET STATISTICS recomputes the column's statistics immediately
- A session-level default_statistics_target also changes what autovacuum collects
- Maxing the target at 10000 everywhere is free and always better
Assumes PostgreSQL 18; ALTER TABLE ... SET STATISTICS DEFAULT exists since 17, and earlier releases reset a column with SET STATISTICS -1.