Extended Statistics for Correlated Columns #

Your plan shows rows=14 over a node that returns 41,000 rows, and each of its predicates is estimated correctly when you test it alone. That combination — accurate parts, wrong whole — means the planner multiplied the selectivities of columns that are not independent, and no amount of ANALYZE or statistics-target tuning will fix it.

The general mechanics of how estimates are formed are in statistics and selectivity estimation. This page covers the one repair designed for this failure: CREATE STATISTICS.

The Independence Assumption #

For a node with several AND-ed predicates, the planner computes each selectivity from per-column statistics and multiplies them. That is correct only when knowing one column tells you nothing about the other. Real schemas are full of columns that violate it:

With city = 'Lyon' at 0.4% and country = 'FR' at 6%, the planner estimates 0.024% of rows. The truth is 0.4%, because every Lyon row is already a French row. The estimate is low by a factor of 1 / 0.06 ≈ 17. Add a third correlated column and the factors compound.

The planner uses an extended statistics object only when it covers at least two of the columns referenced by clauses on the same table, and only after ANALYZE has populated it. There are three kinds, each consulted for a different predicate shape:

Kind Stores Used for
dependencies degree to which one column determines another equality and IN clauses
ndistinct distinct counts for column combinations GROUP BY, DISTINCT, hash-aggregate sizing
mcv most common value combinations with frequencies equality, ranges, IN, IS NULL on skewed combinations
Multiplying correlated selectivities On the left, the planner multiplies 0.4 percent for city by 6 percent for country and estimates 0.024 percent. On the right, a functional dependency tells it city determines country, so the combined selectivity is the city selectivity alone, 0.4 percent, which matches reality. per-column statistics only city: 0.4% country: 6% × = 0.024% → rows=240 17× too low with dependencies (city ⇒ country) city: 0.4% country: implied = 0.4% → rows=4,000 matches the actual count 1,000,000-row table; the dependency degree scales how much of the second fraction is dropped

Annotated EXPLAIN Evidence #

The symptom is a scan whose conjunction is underestimated while each of its conjuncts, tested alone, is not:

EXPLAIN (ANALYZE)
SELECT * FROM addresses WHERE city = 'Lyon' AND country = 'FR';
Index Scan using addresses_city_idx on addresses
    (cost=0.43..96.12 rows=240 width=88) (actual time=0.04..18.7 rows=4012 loops=1)
  Index Cond: (city = 'Lyon'::text)
  Filter: (country = 'FR'::text)          -- filter removes nothing …
  Rows Removed by Filter: 0               -- … so country adds no selectivity
-- rows=240 vs actual 4012: underestimated by the country fraction

Rows Removed by Filter: 0 is the tell. The second predicate eliminated no rows the first had not already eliminated, yet the planner charged it a 94% reduction. When the node feeds a join, that error decides the join method: 240 rows invites a nested loop; 4,000 rows usually does not.

For aggregates the symptom appears in the group estimate instead:

HashAggregate  (cost=… rows=1840000 width=24) (actual rows=31200 loops=1)
  Group Key: country, city
  Batches: 5  Memory Usage: 65577kB  Disk Usage: 184320kB
-- planner multiplied n_distinct(country)=46 by n_distinct(city)=40000
-- the true number of (country, city) pairs is ~31,200

Here the overestimate of groups inflates the expected hash table, which can push the planner to sort instead, or — as shown — to spill a hash aggregate that would have fit in memory. That is the HashAggregate spill pattern arriving from a statistics cause.

Where the independence error shows Three plan lines are annotated. The scan estimate of 240 rows against 4012 actual. The Filter on country removes nothing, so country added no selectivity. The HashAggregate group estimate multiplies distinct counts. rows=240 … actual rows=4012 conjunction underestimated 17× Filter … Rows Removed by Filter: 0 second predicate removed nothing HashAggregate rows=1840000 / 31200 group count multiplied, not measured accurate predicates alone, wrong together → CREATE STATISTICS

Step-by-Step Resolution #

  1. Prove each predicate is accurate alone. If a single predicate is misestimated, fix that first; extended statistics build on per-column statistics and cannot correct them.

    EXPLAIN SELECT 1 FROM addresses WHERE city = 'Lyon';      -- expect ≈ 4000
EXPLAIN SELECT 1 FROM addresses WHERE country = 'FR';     -- expect ≈ 60000
SELECT count(*) FROM addresses WHERE city = 'Lyon' AND country = 'FR';

  2. Measure the dependency. A quick check is how many distinct values of the dependent column exist per value of the determining one:

    SELECT avg(c) AS countries_per_city
FROM (SELECT city, count(DISTINCT country) AS c FROM addresses GROUP BY city) s;
-- ≈ 1.0 → city determines country; dependencies will help

  3. Create the object with the kinds that match your predicates. For equality filters, dependencies; for grouping, ndistinct; for skewed combinations or ranges, mcv.

    CREATE STATISTICS addresses_geo (dependencies, ndistinct, mcv)
  ON country, city FROM addresses;

    Since PostgreSQL 14 the column list can also contain expressions, such as ON (lower(email)), tenant_id, which gives the planner statistics for an expression without an index on it.

  4. Populate it. The object is empty until the next analyze:

    ANALYZE addresses;

  5. Inspect what was stored.

    SELECT statistics_name, dependencies, n_distinct
FROM pg_stats_ext WHERE statistics_name = 'addresses_geo';
     statistics_name |          dependencies                  |     n_distinct
-----------------+----------------------------------------+------------------
 addresses_geo   | {"2 => 1": 1.000000, "1 => 2": 0.0012} | {"1, 2": 31207}
-- "2 => 1": 1.0  → attribute 2 (city) fully determines attribute 1 (country)
-- "1 => 2": 0.0012 → country says almost nothing about city
-- n_distinct {"1, 2"}: 31207 combinations, not 46 × 40000

  6. Re-run the original query under EXPLAIN (ANALYZE) and confirm both the estimate and — if it was the point — the plan shape.

Which statistics kind answers which clause Three source boxes on the left, dependencies, ndistinct and mcv, each connect to a target on the right. Dependencies serve equality and IN filters. Ndistinct serves GROUP BY and DISTINCT group counts. MCV serves skewed combinations, ranges and IS NULL tests. dependencies ndistinct mcv WHERE a = x AND b = y · IN lists GROUP BY a, b · DISTINCT a, b skewed pairs · ranges · IS NULL

Before and After #

-- BEFORE: independence assumed
Nested Loop  (rows=240) (actual rows=4012 loops=1)                         Execution Time: 912.4 ms
  ->  Index Scan using addresses_city_idx  (rows=240) (actual rows=4012)

-- AFTER: CREATE STATISTICS addresses_geo (dependencies, ndistinct, mcv); ANALYZE
Hash Join    (rows=4003) (actual rows=4012 loops=1)                        Execution Time: 61.8 ms
  ->  Index Scan using addresses_city_idx  (rows=4003) (actual rows=4012)

The scan itself did not change; its estimate did, and the join above it chose a different algorithm as a consequence.

Common Pitfalls #

Creating the object and forgetting to analyze. A new statistics object has no data, so the planner ignores it. Diagnostic signal: pg_stats_ext returns no row for the object name, or its columns are null. Fix: run ANALYZE on the table immediately after CREATE STATISTICS, and include both statements in the same migration.

Expecting dependencies to help a range predicate. Functional dependencies are only applied to equality and IN clauses. Diagnostic signal: WHERE country = 'FR' AND postal_code BETWEEN '69000' AND '69999' still underestimated after adding dependencies. Fix: add the mcv kind, which handles ranges over the stored value combinations.

Covering too many columns in one object. Multi-column MCV lists and ndistinct combinations grow quickly, and every ANALYZE pays to build them. Diagnostic signal: autovacuum analyze duration on the table rising sharply after the change. Fix: create separate two-column objects for the pairs that actually appear together in predicates.

Wrapping the column in a function. WHERE lower(city) = 'lyon' does not match statistics on city. Diagnostic signal: the default 0.5% equality estimate appearing regardless of value. Fix: create the object on the expression, ON (lower(city)), country, or query the bare column.

Frequently Asked Questions #

Which kind of extended statistics should I create? Use dependencies for equality filters where one column determines another, ndistinct for GROUP BY and DISTINCT on column combinations, and mcv when the combinations are skewed or predicates include ranges and IN lists. When unsure, create all three and drop the ones the plan does not need.

Do extended statistics work across tables in a join? No. An object covers columns of one table or expressions over them. It improves the estimate of the scan feeding a join, which often fixes the join method indirectly, but it cannot describe how values in one table relate to another.

Why did CREATE STATISTICS not change my estimate? The object is empty until the table is analyzed. Beyond that, dependencies apply only to equality and IN, and no kind applies when the predicate wraps the column in a function the object does not cover as an expression.

Up: Statistics and Selectivity Estimation