n_distinct: a negative value is a ratio

n_distinct is the planner’s estimate of how many distinct values a column contains, recorded per column when the table is analysed. A positive value is a count. A negative value is the negation of a ratio to the number of rows, so minus one means every row holds a different value and minus zero point two means one distinct value for every five rows. One column carries both encodings, and the sign is the only thing that says which you are reading.

Why an estimate is stored as a ratio at all

Analyse reads a sample, not the table, and estimating distinct values from a sample is the hardest of the statistics to get right: a column can look nearly unique in a sample and be heavily repeated overall, or the reverse. Recording a ratio instead of a count lets the estimate scale as the table grows, so a column whose distinct count rises with row count stays approximately correct between analyses instead of freezing at the size the table had last week.

The encoding is chosen by analyse, and the choice can be wrong in a way that persists. A column that will always hold a fixed, small set of values but happens to look like it scales gets a ratio, and its estimate then drifts upward as the table grows. A genuinely scaling column recorded as a fixed count drifts the other way. Both produce row estimates that are wrong by a factor that grows over time with no obvious event to blame, which is a nastier failure than stale statistics because analysing the table does not fix it.

This one statistic is also unusually load-bearing. Group-by cardinality, hash table sizing, the selectivity of an equality predicate on a column with no matching most-common value, and the comparison that decides whether a prepared statement keeps a generic plan all read it.

Reading both encodings at once

Three columns of the same table, analysed together.

CREATE TABLE person (id int, country text, email text);
INSERT INTO person SELECT g, 'c' || (g % 20), 'u' || g || '@example.com' FROM generate_series(1, 100000) g;
ANALYZE person;
SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'person' ORDER BY attname;

CREATE TABLE
INSERT 0 100000
ANALYZE
 attname | n_distinct 
---------+------------
 country |         20
 email   |         -1
 id      |         -1
(3 rows)

Twenty countries recorded as a count, because twenty is small and stable relative to a hundred thousand rows. The two unique columns recorded as minus one, which reads as a strange value until you know it means the ratio is one to one. Nothing in the column name or type distinguishes the two cases.

Overriding it, which is allowed here

This is one of the few statistics you can set by hand, with ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...), and the value survives every subsequent analyse until it is reset. That makes it the right tool for a column whose true cardinality you know and sampling keeps getting wrong, and a poor tool for anything else, since it is a number nobody will re-examine. Where a row estimate is wrong because two columns are correlated rather than because either is misjudged alone, the fix is a different kind of statistics object, and the reasoning is in query plan regression.