Statistics and Selectivity Estimation in PostgreSQL Plans #

Every rows= number in a plan is a prediction built from a small, sampled summary of your data, and nearly every bad plan choice traces back to one of those predictions being wrong by an order of magnitude or more.

The cost model prices work, but it can only price the rows it is told about. This page opens the layer underneath: what ANALYZE actually stores, how the planner converts a WHERE clause into a selectivity fraction, where that arithmetic breaks, and which of the four repairs — fresher statistics, finer statistics, an n_distinct override, or extended statistics — fits the break you are looking at. It sits within execution plan fundamentals because every join, scan and aggregate decision downstream inherits these numbers.

When the Planner Relies on Statistics #

The planner consults statistics for every node whose input is a base table with a predicate, a join condition, or a grouping key. There is no path that avoids them: an index scan, a sequential scan and a bitmap scan are all priced from the same row estimate, and the choice between them is decided by that estimate crossing a threshold. The sequential vs index scan crossover, the hash join batch count, and the HashAggregate versus GroupAggregate decision each move when a single estimate moves.

Three inputs feed a scan estimate:

The planner treats all three as ground truth. It has no mechanism for doubting a statistic, and it will not notice at execution time that the estimate was wrong — the plan runs to completion with whatever shape the estimate chose. That is why a divergence between rows= and actual rows= is the most valuable number in any EXPLAIN ANALYZE output.

-- What the planner knows about one column
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
 null_frac | n_distinct |       most_common_vals        |     most_common_freqs      | histogram_bounds | correlation
-----------+------------+-------------------------------+----------------------------+------------------+-------------
         0 |          5 | {shipped,delivered,pending,…} | {0.61,0.33,0.04,0.013,…}   |                  |        0.41
-- n_distinct = 5          → a positive number is an absolute count of distinct values
-- most_common_freqs        → fractions of the sampled rows; together they cover ~100%
-- histogram_bounds empty   → every value fit in the MCV list, so no histogram is needed
-- correlation = 0.41       → physical order only loosely follows value order
From sampled rows to a plan decision ANALYZE reads a sample of 300 times the statistics target rows. It writes null fraction, n_distinct, the most-common-values list, histogram bounds and correlation into pg_statistic. The planner combines those with the current table size to compute selectivity, multiplies it into a row estimate, and the row estimate decides the access path, join method and aggregate strategy. ANALYZE sample 300 × target rows pg_statistic null_frac n_distinct MCV list + frequencies histogram bounds correlation selectivity × reltuples = rows access path join method aggregate strategy one wrong fraction in the middle box moves all three decisions on the right

Reading an Estimate Node by Node #

The diagnostic signal is always the ratio between estimated and actual rows on the lowest node where they diverge. Higher nodes inherit the error and usually amplify it, so the first divergence reading upward from the leaves is where the cause lives.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.region = 'eu-west'
  AND o.warehouse = 'dub-1';
Nested Loop  (cost=1.13..412.60 rows=12 width=16) (actual time=0.05..2841.3 rows=38110 loops=1)
  ->  Index Scan using orders_status_idx on orders o
        (cost=0.56..188.40 rows=12 width=24) (actual time=0.03..611.2 rows=38110 loops=1)
        Index Cond: (status = 'pending'::text)
        Filter: ((region = 'eu-west'::text) AND (warehouse = 'dub-1'::text))
        Rows Removed by Filter: 21890
  ->  Index Only Scan using customers_pkey on customers c
        (cost=0.56..18.60 rows=1 width=8) (actual time=0.05..0.05 rows=1 loops=38110)
        Index Cond: (id = o.customer_id)
        Heap Fetches: 0
Planning Time: 0.9 ms
Execution Time: 2869.4 ms

Breakdown of the fields that matter:

The arithmetic the planner performed looks like this. With status = 'pending' at 4% (from the MCV list), region = 'eu-west' at 1% and warehouse = 'dub-1' at 0.3%, on a 1,000,000-row table: 1,000,000 × 0.04 × 0.01 × 0.003 = 1.2, clamped and rounded upward to a small number. In reality every dub-1 order is in eu-west, so the true combined fraction of those two predicates is 0.3%, not 0.003%. That single assumption — independence — is the cause of more misestimates than stale statistics, and it is what extended statistics for correlated columns exist to repair.

How Selectivity Is Computed #

Each predicate type has its own estimator, and knowing which one ran tells you which statistic to inspect.

Equality on a value in the MCV list. The selectivity is the stored frequency for that value. This is the most accurate estimate the planner makes, which is why a value that should be in the MCV list but is not — because the list is too short — is a common failure.

Equality on a value not in the MCV list. The planner takes the frequency left over after the MCV list and the null fraction, and divides it evenly among the remaining distinct values: (1 − sum(mcv_freqs) − null_frac) / (n_distinct − mcv_count). The accuracy of this path depends entirely on n_distinct, which is the least reliable statistic ANALYZE produces.

Range predicates (<, >, BETWEEN). The planner locates the constant in the histogram bounds and interpolates the fraction of buckets below or above it, then adds the matching MCV frequencies. Each bucket holds an equal share of the non-MCV rows, so resolution is one bucket — 1% at the default target of 100.

LIKE, IN, IS NULL and functions. IN lists are summed as a series of equalities. IS NULL uses null_frac directly. A predicate wrapped in a function, such as lower(email) = $1, has no column statistics at all unless an expression index or expression statistics exist, and falls back to hard-coded defaults — 0.5% for equality, one third for an inequality.

Multiple predicates. Independent selectivities are multiplied. Two predicates on the same column in a range form are recognised and combined as a single range; everything else is multiplied unless an extended statistics object covers the column set.

Which statistic answers which predicate A predicate branches by shape. Equality checks the MCV list first and uses the stored frequency on a hit, otherwise divides leftover frequency by the remaining distinct count. A range predicate interpolates histogram buckets. A function call on a column with no expression statistics falls back to fixed default selectivities. Multiple predicates are multiplied unless extended statistics cover them. WHERE predicate col = constant col < / > constant f(col) = constant in MCV list: stored freq not in list: leftover ÷ n_distinct interpolate histogram buckets no stats: default 0.005 or 0.333 several predicates → fractions multiplied, unless CREATE STATISTICS covers the columns

Memory, I/O and Planning-Time Behaviour #

Statistics have costs of their own, and the repairs trade those costs against estimate accuracy.

ANALYZE I/O. The sample size is 300 × statistics_target rows, taken from a random set of pages. At the default target of 100 that is 30,000 rows; at a target of 1,000 it is 300,000. ANALYZE reads those pages from the table, which on a cold cache means random reads proportional to the sample, not to the table size. A large target on a wide table can make each automatic analyze noticeably heavier.

Catalog size and planning time. The MCV list and the histogram each hold up to statistics_target entries. Wider statistics are detoasted and scanned during planning for every query touching the column. A target of 10,000 on a text column with long values can add measurable milliseconds to planning — a real cost on a statement executed thousands of times per second, and invisible in Execution Time. Compare Planning Time before and after raising a target.

Extended statistics. Each CREATE STATISTICS object adds work to every ANALYZE of the table, and multi-column MCV lists grow combinatorially with the number of columns. Keep objects to the two or three columns that actually appear together in predicates.

Staleness. Statistics are a snapshot. Autovacuum re-analyzes a table when changed rows exceed autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples — by default 50 rows plus 10% of the table. On a 500-million-row table that is 50 million changes before a refresh, which is why stale statistics after bulk loads are a runtime problem as much as a statistics one.

-- How large the statistics are, and how old
SELECT s.attname,
       array_length(s.most_common_vals::text::text[], 1) AS mcv_entries,
       array_length(s.histogram_bounds::text::text[], 1) AS hist_bounds,
       a.attstattarget,
       t.last_analyze, t.last_autoanalyze, t.n_mod_since_analyze
FROM pg_stats s
JOIN pg_attribute a ON a.attrelid = 'orders'::regclass AND a.attname = s.attname
JOIN pg_stat_user_tables t ON t.relname = s.tablename
WHERE s.tablename = 'orders';
-- attstattarget NULL (or -1 before PostgreSQL 17) → column uses default_statistics_target
-- n_mod_since_analyze large relative to reltuples → statistics are stale

Step-by-Step Repair Workflow #

  1. Find the lowest divergent node. Run EXPLAIN (ANALYZE, BUFFERS) and scan from the leaves upward for the first node where actual rows differs from rows by more than 10×. Note every predicate on that node — both Index Cond and Filter.

  2. Check freshness before anything else.

    SELECT relname, n_live_tup, n_mod_since_analyze, last_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'orders';

    If n_mod_since_analyze is a meaningful fraction of n_live_tup, run ANALYZE orders; and re-test. Many “bad estimate” investigations end here.

  3. Test each predicate alone. Estimate the predicates individually to find which one is wrong, or whether each is right and only their combination is wrong:

    EXPLAIN SELECT 1 FROM orders WHERE status = 'pending';
EXPLAIN SELECT 1 FROM orders WHERE warehouse = 'dub-1';
EXPLAIN SELECT 1 FROM orders WHERE region = 'eu-west' AND warehouse = 'dub-1';
-- Compare each rows= with SELECT count(*) for the same predicate.

  4. Classify and fix the single-predicate case. A frequent value missing from most_common_vals, or a histogram too coarse for a narrow range, calls for a finer sample — raising the statistics target for skewed columns. A wildly low n_distinct on a large table calls for overriding n_distinct.

    ALTER TABLE orders ALTER COLUMN warehouse SET STATISTICS 1000;
ANALYZE orders (warehouse);

  5. Classify and fix the combined case. If every predicate is accurate alone but the conjunction is not, the columns are correlated:

    CREATE STATISTICS orders_region_wh (dependencies, mcv)
  ON region, warehouse FROM orders;
ANALYZE orders;

  6. Verify the plan, not only the estimate. Re-run the original EXPLAIN (ANALYZE, BUFFERS). The estimate should now be within a small factor of the actual count, and — more importantly — the plan shape should have changed if the old shape was a consequence of the error:

    Hash Join  (cost=2210.4..9841.7 rows=36940 width=16) (actual time=41.2..212.8 rows=38110 loops=1)
  ->  Bitmap Heap Scan on orders o  (rows=36940) (actual rows=38110 loops=1)
Execution Time: 219.6 ms
-- estimate within 4%, nested loop replaced by hash join, 13× faster

  7. Record why. A per-column target or a statistics object is invisible in the query text. Put the reason in a migration comment so the next person does not remove it during a cleanup.

Match the symptom to the repair Four rows pair a symptom with a fix. Many modified rows since the last analyze is fixed by running ANALYZE. A frequent value missing from the MCV list or a coarse histogram is fixed by a higher per-column statistics target. A distinct count far below reality on a large table is fixed by an n_distinct override. Accurate single predicates whose combination is wrong is fixed by CREATE STATISTICS. symptom in pg_stats / EXPLAIN repair n_mod_since_analyze is large ANALYZE the table hot value missing from MCV list SET STATISTICS on that column n_distinct far below the real count SET (n_distinct = …) override each predicate right, combination wrong CREATE STATISTICS on the column set

Common Pitfalls #

Blaming statistics when the predicate is not estimable. A comparison against a function result, a CASE expression, or a column cast to another type has no matching statistics, so no amount of ANALYZE helps. Diagnostic signal: an estimate of exactly 0.5% or one third of the table, regardless of the value. Fix: create an expression index or CREATE STATISTICS on the expression, or rewrite the predicate against the bare column.

Raising the global target to fix one column. default_statistics_target = 1000 fixes the column you cared about and slows every ANALYZE and plan on the server. Diagnostic signal: autovacuum analyze workers running far longer after a configuration change, and planning time creeping up across unrelated queries. Fix: set the target per column and leave the global default alone.

Stale estimates on a freshly loaded partition. A new partition starts with no statistics, and autovacuum may not analyze it before the first reports hit it. Parent-level statistics on a partitioned table are never collected by autovacuum at all. Diagnostic signal: last_analyze and last_autoanalyze both null for the partition or the parent. Fix: run ANALYZE explicitly as the last step of every load job, including on the partitioned parent.

Trusting the MCV list on a column whose skew changes daily. The list reflects the distribution when ANALYZE ran. A status column where today’s batch is all pending looks, to yesterday’s statistics, like a rare value. Diagnostic signal: good plans in the morning after the nightly analyze, bad ones by the afternoon. Fix: analyze after the batch rather than on a fixed schedule, or lower the per-table autovacuum_analyze_scale_factor.

Assuming the planner uses statistics for parameters in a generic plan. A generic plan cannot look up $1 in the MCV list, so it uses an average. Diagnostic signal: accurate estimates in psql with literals, wrong ones in the application. Fix: this is the prepared statement plan pinning problem, not a statistics one — compare custom and generic plans before touching statistics.

Reading n_distinct without its sign. A negative n_distinct is a fraction of the row count, not a count: -1 means every row is distinct, -0.2 means one distinct value per five rows. Diagnostic signal: a surprisingly small number that is actually negative. Fix: multiply the absolute value by reltuples before comparing it with count(DISTINCT …).

Frequently Asked Questions #

Where does the rows= estimate in EXPLAIN come from? #

It is the table’s estimated row count multiplied by the selectivity of every predicate on the node. Selectivity comes from pg_statistic: the most-common-values list for frequent values, the histogram for ranges and remaining values, the null fraction, and n_distinct for equality on values outside the MCV list. When several predicates apply, their selectivities are multiplied unless extended statistics say otherwise.

Why are my row estimates wrong right after ANALYZE? #

ANALYZE samples 300 rows per unit of statistics target, so with the default target of 100 it reads 30,000 rows regardless of table size. Rare values can be missed entirely, n_distinct is extrapolated from the sample and is often underestimated on large tables, and predicates on correlated columns are still multiplied as if independent. Fresh statistics are not the same as sufficient statistics.

Should I raise default_statistics_target globally? #

Usually not. A higher global target makes every ANALYZE slower, enlarges pg_statistic, and increases planning time for every query that touches those columns. Raise the target per column with ALTER TABLE … ALTER COLUMN … SET STATISTICS on the handful of skewed columns whose estimates you have measured to be wrong.

What do extended statistics fix that per-column statistics cannot? #

Per-column statistics describe each column alone, so the planner multiplies selectivities as though columns were independent. CREATE STATISTICS records functional dependencies, combined distinct counts, and multi-column most-common values, which correct estimates for filters and GROUP BY on columns whose values move together, such as city and postal code.

Up: Execution Plan Fundamentals