Overriding n_distinct on Large Tables #

pg_stats says the user_id column of your 900-million-row events table has 184,000 distinct values. You have 11 million users. Every equality lookup on a user who is not in the most-common-values list is estimated at roughly 60 times their real row count, and every GROUP BY user_id is sized for 184,000 groups instead of 11 million.

The statistic is not stale; it is extrapolated from a sample that is too small to see a long tail. This page shows how to recognise the underestimate and replace it with a value you supply — one of the repairs laid out in statistics and selectivity estimation.

Why the Sample Underestimates Distinct Values #

ANALYZE reads a fixed-size sample — 30,000 rows at the default target — and counts the distinct values in it. It then extrapolates to the whole table with a statistical estimator that weighs how many values appeared exactly once in the sample. That estimator is reasonable when values repeat often. On a column with a long tail of rarely repeated values, most of them never appear in the sample at all, and the extrapolation has nothing to scale up.

The planner uses n_distinct in two places where the error hurts:

n_distinct is stored in one of two forms. A positive number is an absolute count. A negative number between -1 and 0 is a fraction of the row count: -0.0122 means one distinct value per 82 rows. ANALYZE stores the fractional form when it concludes the count scales with the table, so always read the sign before comparing.

A small sample of a long tail A wide bar represents 900 million rows with 11 million distinct users. A narrow slice marks the 30 thousand sampled rows. Inside the sample most users appear once or not at all, so the extrapolated distinct count comes out at 184 thousand, about sixty times too low. events: 900,000,000 rows · 11,000,000 distinct user_id the table random sample 30,000 rows sampled n_distinct ≈ 184,000 most users: 0 sightings values the sample never saw cannot be extrapolated — the estimate collapses toward the sample

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*) FROM events
WHERE occurred_at >= now() - interval '1 day'
GROUP BY user_id;
HashAggregate  (cost=… rows=184000 width=16) (actual time=… rows=3920114 loops=1)
  Group Key: user_id
  Planned Partitions: 0  Batches: 33  Memory Usage: 262193kB  Disk Usage: 1822512kB
  -- rows=184000 planned groups vs 3,920,114 actual → hash table sized 21× too small
  -- Planned Partitions: 0 → the planner expected no spill; Batches: 33 → it spilled heavily
  ->  Index Scan using events_occurred_at_idx on events (actual rows=41822031 loops=1)

And the statistic behind it:

SELECT n_distinct, (SELECT reltuples FROM pg_class WHERE relname = 'events') AS reltuples
FROM pg_stats WHERE tablename = 'events' AND attname = 'user_id';
 n_distinct | reltuples
------------+-----------
     184212 | 9.02e+08
-- positive → absolute count; real distinct users ≈ 11,000,000
What a low n_distinct does to a HashAggregate Three fields are annotated. The planned rows of 184 thousand versus 3.9 million actual groups. Planned Partitions zero shows no spill was expected. Batches 33 with disk usage shows the spill happened anyway. rows=184000 … actual rows=3920114 groups sized from n_distinct Planned Partitions: 0 planner expected no spill Batches: 33 Disk Usage: 1822512kB spill discovered during execution

Step-by-Step Resolution #

  1. Measure the true distinct count — on a replica or with an approximation when an exact count(DISTINCT) over the whole table is too heavy:

    -- exact, on a replica
SELECT count(DISTINCT user_id) FROM events;
-- cheaper proxy: distinct count in a large block sample, scaled
SELECT count(DISTINCT user_id) FROM events TABLESAMPLE SYSTEM (1);

    For a column that references another table’s primary key, SELECT count(*) FROM users is often the best available upper bound.

  2. Choose the form. If the number of users grows as events grow, use a fraction so the value stays right as the table grows: 11,000,000 / 900,000,000 ≈ 0.0122, stored as -0.0122. If the number is fixed — countries, currencies, a small enum — use the absolute count.

  3. Set the override.

    ALTER TABLE events ALTER COLUMN user_id SET (n_distinct = -0.0122);

    For a partitioned parent, set the inherited variant as well, since parent-level estimates use their own statistics:

    ALTER TABLE events ALTER COLUMN user_id SET (n_distinct_inherited = -0.0122);

  4. Analyze to apply it. The option is read during ANALYZE and written into pg_statistic:

    ANALYZE events;
SELECT n_distinct FROM pg_stats WHERE tablename = 'events' AND attname = 'user_id';
-- -0.0122

  5. Re-check both estimate types — a non-MCV equality lookup and the GROUP BY:

    EXPLAIN SELECT * FROM events WHERE user_id = 70331;
EXPLAIN SELECT user_id, count(*) FROM events GROUP BY user_id;
Absolute count or fraction of rows A decision starts with the question of whether the number of distinct values grows as the table grows. If yes, such as user or session identifiers, use a negative fraction of rows. If no, such as country codes or categories, use a positive absolute count. Both require an ANALYZE to take effect. do distinct values grow with the table? yes no n_distinct = −0.0122 user_id, session_id, order_id n_distinct = 249 country, currency, category either form is stored as a column option and applied at the next ANALYZE

Before and After #

-- BEFORE: n_distinct = 184212 (sample extrapolation)
HashAggregate  (rows=184000)  (actual rows=3920114)  Batches: 33  Disk Usage: 1822512kB   Execution Time: 48122.7 ms

-- AFTER: SET (n_distinct = -0.0122); ANALYZE events
HashAggregate  (rows=3790000) (actual rows=3920114)  Planned Partitions: 32  Batches: 32   Execution Time: 21406.3 ms

The spill did not disappear — four million groups do not fit in the memory budget — but the planner now plans the partitioning up front instead of discovering it mid-execution, and the cost comparison against a sort-based GroupAggregate is made with a true group count.

Common Pitfalls #

Setting the override without analyzing. The column option alone is inert. Diagnostic signal: pg_attribute.attoptions shows n_distinct=-0.0122 but pg_stats.n_distinct still shows the sampled value. Fix: run ANALYZE on the table.

Using an absolute count for a growing column. A fixed 11000000 is correct today and too small in a year. Diagnostic signal: estimates that were good after the change slowly drift high again as the table grows. Fix: use the negative fractional form for anything that scales with row count.

Overriding only the partitions of a partitioned table. Queries planned against the parent use inherited statistics. Diagnostic signal: partition-level EXPLAIN estimates are right while queries on the parent remain wrong. Fix: set n_distinct_inherited on the parent too and analyze the parent explicitly — autovacuum never analyzes partitioned parents.

Overriding a column whose problem is skew, not count. When a handful of values dominate, the fix is a longer MCV list; n_distinct only governs the tail. Diagnostic signal: the misestimated values are among the most frequent in the column. Fix: raise the statistics target instead.

Frequently Asked Questions #

Should I use a positive or a negative n_distinct override? Use a negative fraction when the number of distinct values grows with the table, such as user_id on an events table. Use a positive absolute count when it is fixed regardless of size, such as a country code.

When does an n_distinct override take effect? At the next ANALYZE. ALTER TABLE … SET (n_distinct = …) only stores the option; the value in pg_statistic is replaced during analyze.

What is n_distinct_inherited for? It overrides the distinct count for statistics gathered across a partitioned table or inheritance tree as a whole, which the planner uses when a query targets the parent.

Up: Statistics and Selectivity Estimation