DBAtlas · Database operations atlas

Create extended statistics for correlated columns (CREATE STATISTICS)

PostgreSQLMaintenance

Command

-- city and district are correlated: the planner assumes independence and underestimates rows
CREATE STATISTICS customer_city_district_stx (dependencies, ndistinct, mcv) ON city, district FROM customer;
ANALYZE customer;

-- Collected statistics
SELECT statistics_name, attnames, kinds, n_distinct, dependencies
FROM pg_stats_ext
WHERE tablename = 'customer';

-- Verify: estimated rows should now be close to actual rows
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM customer WHERE city = 'Ankara' AND district = 'Cankaya';

What it does

Teaches the planner about cross-column correlation (functional dependencies, combined distinct counts, joint most-common values); fixes row estimates for multi-column WHERE and GROUP BY conditions.

When to use it

When EXPLAIN ANALYZE shows estimated rows far below actual for multi-column filters (e.g. 12 vs. 570) and a wrong join method/index is chosen as a result.

Caution

Takes effect only after ANALYZE. When the number of column combinations is far above the statistics target (default 100), the mcv list cannot cover most of them, and mcv may then fail to fix an estimate that dependencies would fix. Verify with EXPLAIN; if needed drop mcv from the definition or raise the target with ALTER STATISTICS customer_city_district_stx SET STATISTICS 1000;. dependencies is used only for equality/IN conditions, ndistinct for GROUP BY estimates. Every definition lengthens ANALYZE; add it only to column groups with a proven misestimate.

Open in DBAtlas →

Related