In PostgreSQL, how does the planner turn a column's pg_stats row (null_frac, n_distinct, most_common_vals, most_common_freqs) into a row estimate for an equality filter?
answer
- selectivity times table rows
- an MCV hit uses its frequency
- a miss splits the leftover share
- subtract null_frac as well
- negative n_distinct is a ratio
basics
~20 sIf the constant is in most_common_vals, selectivity is its most_common_freqs entry. Otherwise the planner takes 1 minus the MCV frequencies minus null_frac, divides by the remaining distinct values (n_distinct minus the MCV count), and multiplies by the table's estimated rows.
solid answer
~40 sFor `col = constant` the planner first looks the constant up in `most_common_vals`. On a hit, the selectivity is the matching `most_common_freqs` entry. On a miss, it assumes the rest of the column is spread evenly: `(1 - sum(most_common_freqs) - null_frac) / (n_distinct - number of MCVs)`, never more than the least common MCV's frequency. The row estimate is that selectivity times the table's row count, `pg_class.reltuples` scaled to its current size. Take a 20,000,000-row `delivery_addresses` table with `null_frac` 0.01, `n_distinct` 1204 and four MCV cities summing to 0.24, Leeds at 0.03. Leeds is estimated at 600,000 rows, and a city off the list at `0.75 / 1200 × 20,000,000 = 12,500`. A negative `n_distinct` is a ratio of rows (-0.4 means 40% distinct). Range filters use `histogram_bounds` instead.
code
console · 10 linesapp=> \x on
Expanded display is on.
app=> SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
app-> FROM pg_stats
app-> WHERE tablename = 'delivery_addresses' AND attname = 'city';
-[ RECORD 1 ]-----+-------------------------------------
null_frac | 0.01
n_distinct | 1204
most_common_vals | {London,Manchester,Birmingham,Leeds}
most_common_freqs | {0.12,0.05,0.04,0.03}go deeper
Know that pg_stats holds per-column statistics and that the most-common-value list gives the frequency of common values directly.
Work the MCV hit and the non-MCV formula by hand, including null_frac, and explain what a negative n_distinct means.
Use pg_stats to explain a specific misestimate: a value just off the MCV list, a stale histogram end, or a correlated second filter. Say which fix fits each.
Decide when per-column estimates are good enough and when a team needs extended statistics or overrides, and how those choices are reviewed as data drifts.
## The pg_stats columns behind a row estimate PostgreSQL's planner estimates how many rows a `WHERE` clause keeps by computing a **selectivity**, the fraction of rows that match, and multiplying it by the table's estimated row count: `pg_class.reltuples`, scaled to the table's current physical size. For a single column, the selectivity comes from the row `ANALYZE` left in `pg_statistic`, which you read through `pg_stats`: | Column | Meaning | Used for | |---|---|---| | `null_frac` | fraction of entries that are null | subtracted from non-null estimates | | `n_distinct` | distinct non-null values; negative means a ratio to rows | spreading the non-MCV share | | `most_common_vals` / `most_common_freqs` | values clearly commoner than the rest, with their frequencies | exact hits for `=`, and part of range estimates | | `histogram_bounds` | edges of roughly equal-population buckets, MCVs excluded | range predicates such as `<`, `>=`, `BETWEEN` | | `correlation` | physical order against sort order, from -1 to +1 | index-scan cost, not row counts | ## Equality on a most-common value For `city = 'Leeds'` the planner's equality estimator looks the constant up in `most_common_vals`. If it is there, the selectivity **is** the matching `most_common_freqs` entry. With Leeds at 0.03 on a 20,000,000-row `delivery_addresses` table, the estimate is 0.03 × 20,000,000 = **600,000 rows**. The histogram is not consulted for equality at all. ## Equality on any other value When the constant is not in the list, as with `city = 'Truro'`, the planner knows only that it is not one of the common values. It assumes the non-null, non-MCV part of the column is spread evenly over the remaining distinct values: 1. Start from 1. 2. Subtract the sum of `most_common_freqs`, here 0.24 for four cities. 3. Subtract `null_frac`, here 0.01, which leaves 0.75. 4. Divide by the number of *other* distinct values, `n_distinct` minus the MCV count: 1204 - 4 = 1200. That gives 0.000625. 5. Multiply by the row count: 0.000625 × 20,000,000 = **12,500 rows**. A cross-check follows. The result may not exceed the frequency of the least common MCV, because a value that missed the list cannot be commoner than one that made it. With no statistics at all the planner falls back to built-in defaults: 0.5% for an equality, which on this table would be 100,000 rows for any city. ## Reading the sign of n_distinct - **Positive**: an absolute count. `ANALYZE` judged that the column has a fixed set of values, such as cities or status codes. - **Negative**: minus the ratio of distinct values to rows. -1 means unique, and -0.4 means 40% of rows carry distinct values, a count that grows with the table. To get a number, multiply the absolute value by the current row estimate. ## Ranges use the histogram For `created_at >= '2026-09-01'` the planner adds two parts. The MCVs that satisfy the predicate contribute their frequencies directly. The rest of the population is described by `histogram_bounds`. The planner counts the buckets wholly inside the range, interpolates linearly inside the bucket where the constant falls, divides by the number of buckets, and scales the result by the non-MCV, non-null fraction: ``` selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction ``` When the comparison reaches the first or last histogram bound and a B-tree index exists on the column, the planner probes the index for the real current minimum or maximum. That softens the classic problem of new rows lying beyond a histogram that was built before they arrived. ## Where these estimates go wrong - **Values that just missed the MCV list.** A city common enough to matter, but outside the list, gets the average leftover share. Raising the column's statistics target lengthens the list. - **Stale statistics** after large changes, until auto-analyze or a manual `ANALYZE` runs. - **Several columns at once.** Each clause's selectivity comes from its own row and the results are multiplied. That fails for correlated pairs such as `city` and `postal_code` unless extended statistics exist. - **Correlation is not a row estimate.** A value near +1 or -1 makes a plain index scan cheaper, because heap pages are visited nearly in order. Near 0, each fetched row may be a random page read, so bitmap and sequential scans win more often.
- How does the PostgreSQL planner estimate created_at >= '2026-09-01' on delivery_addresses from pg_stats?It sums the frequencies of MCVs that satisfy the predicate. Then it locates the constant in `histogram_bounds`, counts the whole buckets above it, interpolates linearly within the boundary bucket and divides by the bucket count. That histogram fraction is scaled by the non-MCV, non-null share and added to the MCV part. If the constant reaches the histogram's last bound and a B-tree index exists, the planner probes the index for the actual maximum.
- What does pg_stats.correlation change in PostgreSQL's choice between an index scan and other plans?It changes cost, not row counts. For a plain index scan, the planner interpolates heap I/O cost between all-random and nearly sequential using the square of `correlation`. A column near +1 or -1, such as an append-only `created_at`, makes index range scans cheap. Near 0, each row may need its own random page, so a bitmap heap scan, which reads pages in physical order, or a sequential scan is favoured.
- When a PostgreSQL column has no statistics at all, what does the planner assume for city = 'Leeds'?It uses built-in defaults: 0.5% of rows for an equality, a third of the rows for an open inequality, and 200 distinct values when a distinct count is needed. These are guesses that ignore the real data, which is why a never-analyzed table, or a temporary table nobody analyzed, so often gets a poor plan.
- Equality estimates are read from the histogram buckets
- A value missing from the MCV list is estimated as one row
- Rows for any value are simply total rows divided by n_distinct
- A negative n_distinct means ANALYZE failed to compute it
- pg_stats.correlation adjusts the estimated number of matching rows