Extended statistics: fixing correlated-column estimate failures

A WHERE clause filters on country='US' AND region='CA' AND status='shipped'. The planner predicts 500 matching rows — three independent probabilities multiplied together. There are actually 50,000. A Nested Loop runs 100× too many times. The columns are correlated, not independent. CREATE STATISTICS fixes this in two SQL lines.

Why the independence assumption fails

The planner’s default multi-column selectivity model:

P(country='US' AND region='CA' AND status='shipped')
  = P(country='US') × P(region='CA') × P(status='shipped')
  = 0.50 × 0.05 × 0.20
  = 0.005 (0.5%)

But if country determines region (every CA order is in US — a functional dependency), the actual selectivity is:

P(region='CA') × P(status='shipped') = 0.05 × 0.20 = 1.0%

On 100M rows: the planner estimates 500,000 rows; reality is 1,000,000 — a 2× error that can tip Nested Loop into catastrophic territory. With tighter correlations the error can be 1000×.

The three kinds of extended statistics

CREATE STATISTICS (available since PG 10) supports three complementary kinds:

dependencies

Stores functional dependency factors between column pairs. A factor close to 1.0 means “column A almost always determines column B”. When the planner sees WHERE country='US' AND region='CA', it checks the dependency between country and region — if country determines region with factor 0.95, the planner knows not to multiply P(country) × P(region); instead it estimates based on the more selective of the two.

CREATE STATISTICS stx_orders_geo (dependencies)
  ON country, region, status FROM orders;
ANALYZE orders;

ndistinct

Without this, the planner estimates the number of distinct (country, region) combinations as n_distinct(country) × n_distinct(region), which wildly overestimates when the columns are correlated. ndistinct stores the actual combination count.

Useful for queries like:

SELECT country, region, COUNT(*) FROM orders GROUP BY country, region;

Without ndistinct, the planner may allocate a Hash Aggregate expecting millions of groups when there are only hundreds.

mcv (multi-column most common values)

Stores the most frequent tuples of the column combination directly — analogous to the per-column MCV list, but for pairs or triples. For WHERE country='US' AND status='shipped', if (US, shipped) is a common combination, the planner reads its exact frequency from the MCV list rather than multiplying marginal probabilities.

Available since PG 12 and often the highest-value kind for OLTP query patterns.

Expression statistics (PG 14+)

PG 14 added statistics on expressions:

CREATE STATISTICS stx_orders_lower_email ON LOWER(email) FROM users;
ANALYZE users;

This lets the planner accurately estimate selectivity for WHERE LOWER(email) = 'x' even without a functional index on LOWER(email).

When and how to apply CREATE STATISTICS

The workflow:

1. Run EXPLAIN ANALYZE on the slow query. Find nodes where rows (estimate) differs from actual rows by more than 10×.
2. Identify the WHERE clause columns on that node. Check if they are naturally correlated: zip and city, country and region, product and category.
3. Create the statistics object:

-- Most common pattern: dependencies + mcv together
CREATE STATISTICS stx_orders_country_region_status
  (dependencies, mcv)
  ON country, region, status FROM orders;

-- For GROUP BY accuracy:
CREATE STATISTICS stx_orders_ndistinct
  (ndistinct)
  ON country, region FROM orders;

-- Run ANALYZE to populate:
ANALYZE orders;

4. Re-run EXPLAIN ANALYZE. Verify that rows tracks actual rows within 2× on the affected nodes.

Storage cost: small — kilobytes per statistics object. Planning improvement on the affected queries: 100–1000×.