Extended statistics: when `ANALYZE` isn't enough

ANALYZE keeps precise statistics for each column separately. That's enough when your queries' predicates touch a single column or when the columns are truly independent. But there's a very common case where ANALYZE lies to you without you realizing it: when the predicate combines two or more columns that are correlated.

Classic example: an addresses table with columns country and city. If you filter by WHERE country = 'México' AND city = 'CDMX', the planner assumes independence between the two columns and multiplies selectivities:

• country = 'México': let's say 30% of the rows.
• city = 'CDMX': let's say 5% of the rows.
• Combined estimate: 30% × 5% = 1.5% of the rows.

But the reality is that CDMX is in México. The combined predicate actually returns 5% (the same as just city = 'CDMX'). The planner underestimates by a factor of 3x. On large tables that error translates into terrible plans.

PostgreSQL 10 introduced CREATE STATISTICS to solve this: extended statistics that capture correlations between columns. In this capsule you're going to learn the three types (dependencies, ndistinct, mcv), when to use each one, and how to detect the problem before you know you have it.

The problem in code

You're going to reproduce the problem with synthetic data.

DROP TABLE IF EXISTS user_addresses;
CREATE TABLE user_addresses (
    id SERIAL PRIMARY KEY,
    country TEXT NOT NULL,
    city TEXT NOT NULL,
    user_id INTEGER NOT NULL
);

-- Insert correlated data:
-- México → 70% CDMX, 20% Guadalajara, 10% Monterrey
-- USA → 60% NYC, 30% LA, 10% Chicago
-- España → 50% Madrid, 40% Barcelona, 10% Valencia
INSERT INTO user_addresses (country, city, user_id)
SELECT
    country,
    city,
    generate_series
FROM (
    SELECT 'México' AS country, 'CDMX' AS city, generate_series(1, 70000)
    UNION ALL
    SELECT 'México', 'Guadalajara', generate_series(1, 20000)
    UNION ALL
    SELECT 'México', 'Monterrey', generate_series(1, 10000)
    UNION ALL
    SELECT 'USA', 'NYC', generate_series(1, 60000)
    UNION ALL
    SELECT 'USA', 'LA', generate_series(1, 30000)
    UNION ALL
    SELECT 'USA', 'Chicago', generate_series(1, 10000)
    UNION ALL
    SELECT 'España', 'Madrid', generate_series(1, 50000)
    UNION ALL
    SELECT 'España', 'Barcelona', generate_series(1, 40000)
    UNION ALL
    SELECT 'España', 'Valencia', generate_series(1, 10000)
) data;

ANALYZE user_addresses;

A table with 300k rows. Country and city are perfectly correlated (CDMX implies México, NYC implies USA, etc.).

Typical query with non-extended stats:

EXPLAIN ANALYZE
SELECT * FROM user_addresses
WHERE country = 'México' AND city = 'CDMX';

Output:

Seq Scan on user_addresses
  (cost=0.00..6234.00 rows=23333 width=...)
  (actual time=0.012..45.67 rows=70000 loops=1)
  Filter: ((country = 'México'::text) AND (city = 'CDMX'::text))

The planner estimates rows=23333. Reality is rows=70000. It underestimates by 3x.

How did it calculate it? Look at the individual stats:

SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'user_addresses' AND attname IN ('country', 'city');

Output (simplified):

attname | n_distinct | most_common_vals                     | most_common_freqs
country |          3 | {México, USA, España}                | {0.333, 0.333, 0.333}
city    |          9 | {CDMX, NYC, Madrid, ..., Valencia}   | {0.233, 0.200, 0.167, ...}

Applying independence: frequency(México) × frequency(CDMX) = 0.333 × 0.233 = 0.077. Applied to 300k rows: ~23,300 estimated rows. It matches the plan.

But the columns aren't independent. CDMX is always with México (there's no CDMX, USA). The real selectivity of the combined predicate is the same as just city = 'CDMX': 70000 / 300000 = 23.3%. The planner should have estimated 70k, not 23k.

The solution: CREATE STATISTICS

CREATE STATISTICS stat_country_city (dependencies, ndistinct, mcv)
ON country, city
FROM user_addresses;

ANALYZE user_addresses;

This creates a statistics object in pg_statistic_ext that captures the correlation between the columns. The argument (dependencies, ndistinct, mcv) declares the three types of information we're going to compute over the pair of columns.

Re-run the query:

EXPLAIN ANALYZE
SELECT * FROM user_addresses
WHERE country = 'México' AND city = 'CDMX';

Output:

Seq Scan on user_addresses
  (cost=0.00..6234.00 rows=70000 width=...)
  (actual time=0.012..38.45 rows=70000 loops=1)

rows=70000 (estimated) matches actual rows=70000. Correct estimate. For a simple query like this one, the plan doesn't change (Seq Scan either way), but for more complex queries (joins, sortings, aggregations) the correct estimate changes plan decisions in cascade.

The three types of extended statistics

CREATE STATISTICS supports three types. You can request them separately or combined.

dependencies — functional dependencies

Captures correlations of the type "the value of A determines the value of B." E.g.: city → country (the city determines the country; CDMX is always México).

Without dependencies: the planner assumes independence and multiplies selectivities. It underestimates. With dependencies: the planner recognizes that city "implies" country and adjusts the combined selectivity.

When to use: queries with an AND between correlated columns with a clear functional dependency.

CREATE STATISTICS stat_city_country (dependencies)
ON city, country FROM user_addresses;

ndistinct — number of distinct combinations

Captures how many unique combinations exist between the columns. E.g.: if you have country, city, the distinct combinations are 9 (3 countries × some cities each, not 3×9=27).

Without ndistinct: the planner assumes n_distinct(country) × n_distinct(city) = 27 for GROUP BY country, city. It overestimates. With ndistinct: the planner knows there are 9 distinct groups. It estimates correctly.

When to use: queries with GROUP BY or DISTINCT over multiple correlated columns.

CREATE STATISTICS stat_country_city_distinct (ndistinct)
ON country, city FROM user_addresses;

mcv — combined most common values

Captures the most frequent combinations of values. This is the most powerful but also the most expensive. It allows precise estimates for predicates with specific values.

Without mcv: the planner doesn't know that (México, CDMX) is the most frequent combination. With mcv: the planner knows the top combinations and their exact frequencies.

When to use: when you already have dependencies and ndistinct but the estimates are still off. It's the last resort because it consumes more space and ANALYZE takes longer.

CREATE STATISTICS stat_country_city_mcv (mcv)
ON country, city FROM user_addresses;

Combined (recommended in most cases)

CREATE STATISTICS stat_full (dependencies, ndistinct, mcv)
ON country, city FROM user_addresses;

More overhead in ANALYZE but the greatest precision. For large tables with critical queries it's worth it. For medium tables, all three types together are rarely a problem.

Inspecting extended statistics

-- List all extended statistics
SELECT
    schemaname,
    tablename,
    statistics_name,
    attnames,
    kinds
FROM pg_stats_ext
WHERE tablename = 'user_addresses';

Output:

schemaname | tablename       | statistics_name        | attnames        | kinds
public     | user_addresses  | stat_country_city      | {country, city} | {d,f,m}

The three types are reported with codes:

• d = dependencies
• f = ndistinct (from "functional dependencies in n-ary form", historical)
• m = mcv

To see the computed values (which dependencies it detected, which MCVs it stored):

SELECT * FROM pg_stats_ext_exprs WHERE statistics_name = 'stat_country_city';

Or the more complete view (PostgreSQL 12+):

SELECT statistics_name, dependencies
FROM pg_stats_ext WHERE statistics_name = 'stat_country_city';

How to detect candidates for extended statistics

You're not going to put CREATE STATISTICS on every pair of columns — that would be overkill. The pattern is reactive: you detect the symptom, identify the correlated pair, create the stats.

Symptom: massively low estimate in queries with AND

When a query with WHERE A = ... AND B = ... has rows= estimated much lower than actual rows=, and both predicates alone would give reasonable estimates, suspect correlation.

-- Predicate alone
EXPLAIN ANALYZE SELECT * FROM user_addresses WHERE country = 'México';
-- rows=99000 actual rows=100000 ✓ ok

-- Predicate alone
EXPLAIN ANALYZE SELECT * FROM user_addresses WHERE city = 'CDMX';
-- rows=70000 actual rows=70000 ✓ ok

-- Combined
EXPLAIN ANALYZE SELECT * FROM user_addresses WHERE country = 'México' AND city = 'CDMX';
-- rows=23333 actual rows=70000 ✗ 3x underestimation

That's the pattern. The two columns are correlated.

Symptom: massively high estimate in GROUP BY

EXPLAIN ANALYZE
SELECT country, city, COUNT(*) FROM user_addresses
GROUP BY country, city;
-- rows=27 (estimated: 3 × 9)
-- actual rows=9 (real)

The planner overestimates the number of groups. If the GROUP BY is later used in a JOIN, the chosen plan can be very bad. Extended statistics with ndistinct fix this.

Mental pattern to identify candidates

Ask yourself: "does column A tell me something about column B?". If the answer is yes, they're probably correlated. Common cases:

• Geographic: country, state, city, zip_code. Each one determines the next.
• Temporal-status: status and related timestamps (shipped_status and shipped_at, paid_status and paid_at).
• Category-price: category and price_range (premium categories have high prices).
• User-tier: subscription_plan and account_features (premium plans have different features).

Traps and common mistakes

1. Creating extended statistics without running ANALYZE afterward.

CREATE STATISTICS only creates the empty object. The stats are computed in the next ANALYZE (manual or automatic). If you create and don't analyze, the planner doesn't have the new information.

CREATE STATISTICS my_stat ON col_a, col_b FROM my_table;
ANALYZE my_table;  -- Without this, the CREATE has no effect

2. Creating extended statistics for non-correlated columns.

If the columns are truly independent, CREATE STATISTICS adds nothing and just adds overhead to ANALYZE. Before creating, verify with real data that there's correlation.

3. Using CREATE STATISTICS when the problem is something else.

If the estimate is bad because n_distinct is miscalculated on an individual column, the solution is ALTER COLUMN ... SET STATISTICS 1000 and ANALYZE, not CREATE STATISTICS. Diagnose first, then apply the solution.

4. Creating many extended statistics "preventively".

Each stats object adds work to ANALYZE. Creating 50 statistics objects on a table because "they might help" makes ANALYZE and autovacuum drastically slower. Create reactively when you detect a concrete problem, generally 3-5 per table at most.

5. Forgetting that CREATE STATISTICS requires PostgreSQL 10+.

If you're on PostgreSQL 9.6 or earlier, this feature doesn't exist. And for mcv you need PostgreSQL 12+. Check the version:

SELECT version();

6. Thinking that CREATE STATISTICS with mcv replaces dependencies.

mcv gives exact estimates for specific combinations (the top frequent ones). dependencies helps the planner for non-top combinations. Combining them gives better coverage. The general recommendation is to use all three types together.

Exercise: detect correlation and apply CREATE STATISTICS

You're going to have a schema with two pairs of columns. Your job is to identify which pair is correlated, validate the underestimation, create the appropriate stats, and measure the improvement.

Setup:

DROP TABLE IF EXISTS products;
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    category TEXT NOT NULL,
    brand TEXT NOT NULL,
    price_tier TEXT NOT NULL,
    in_stock BOOLEAN NOT NULL
);

-- Data: category-brand correlated, price_tier-in_stock NOT
-- Brand "Apple" only appears in category "Electronics"
-- Brand "Nike" only appears in category "Sports"
-- Brand "Generic" appears in both
INSERT INTO products (category, brand, price_tier, in_stock)
SELECT
    CASE WHEN brand_pick IN ('Apple', 'Samsung') THEN 'Electronics'
         WHEN brand_pick IN ('Nike', 'Adidas') THEN 'Sports'
         ELSE (ARRAY['Electronics', 'Sports', 'Books'])[floor(random() * 3)::INT + 1]
    END,
    brand_pick,
    (ARRAY['low', 'mid', 'high'])[floor(random() * 3)::INT + 1],
    (random() < 0.7)
FROM (
    SELECT (ARRAY['Apple', 'Samsung', 'Nike', 'Adidas', 'Generic'])[floor(random() * 5)::INT + 1] AS brand_pick
    FROM generate_series(1, 100000)
) data;

ANALYZE products;

Step 1: verify there's correlation with validation queries.

-- How many Apple products in Electronics? (should be all the Apple ones)
SELECT COUNT(*) FROM products WHERE category = 'Electronics' AND brand = 'Apple';

-- How many Apple in total?
SELECT COUNT(*) FROM products WHERE brand = 'Apple';

-- How many Electronics in total?
SELECT COUNT(*) FROM products WHERE category = 'Electronics';

Step 2: run EXPLAIN ANALYZE over the combined predicate and observe the underestimation.

EXPLAIN ANALYZE
SELECT * FROM products WHERE category = 'Electronics' AND brand = 'Apple';

Question: rows= estimated vs actual rows=? By how much does it underestimate?

Step 3: check (price_tier, in_stock) — suspect they are NOT correlated.

EXPLAIN ANALYZE
SELECT * FROM products WHERE price_tier = 'high' AND in_stock = TRUE;

Question: is the estimate good or bad?

Step 4: create extended statistics ONLY where there's correlation.

CREATE STATISTICS stat_category_brand (dependencies, ndistinct, mcv)
ON category, brand FROM products;

ANALYZE products;

Step 5: validate the improvement.

EXPLAIN ANALYZE
SELECT * FROM products WHERE category = 'Electronics' AND brand = 'Apple';

Question: did the estimate improve? By how much?

Apple in Electronics: ~20000
Apple total: ~20000  (all)
Electronics total: ~30000-40000

Seq Scan on products
  (cost=0.00..2234.00 rows=4000 width=...)
  (actual time=0.012..23.45 rows=20000 loops=1)

Seq Scan on products
  (cost=0.00..2234.00 rows=23000 width=...)
  (actual time=0.012..18.32 rows=23234 loops=1)

Seq Scan on products
  (cost=0.00..2234.00 rows=20000 width=...)
  (actual time=0.012..21.23 rows=20000 loops=1)

Summary and next step

What you learned:

• ANALYZE keeps per-individual-column stats; it doesn't capture correlations between columns.
• When two columns are correlated, combined predicates (A AND B) usually underestimate dramatically.
• CREATE STATISTICS (PostgreSQL 10+) solves this with three types: dependencies, ndistinct, mcv.
• Correlation symptom: combined estimate much lower than actual rows, while the individual estimates are correct.
• Apply it reactively, where you detect the problem. Not preventively over all pairs.
• After CREATE STATISTICS, always ANALYZE so the planner receives the new computations.

Before moving on, you should be able to:

• Identify potentially correlated pairs of columns in your schema.
• Validate the correlation with EXPLAIN ANALYZE (estimated vs actual with a combined predicate).
• Create extended statistics with all three types when you detect the problem.
• Inspect the existing extended stats with pg_stats_ext.

In the next capsule we're going to change topics within the same module. So far you worked with statistics (information the planner consumes). Now you're going to get into PostgreSQL's other operational problem: bloat, physical space that UPDATE and DELETE leave occupied but useless. You're going to learn the MVCC model (Multi-Version Concurrency Control), why UPDATE doesn't modify rows in-place, and how accumulated bloat degrades performance silently.

Resources

1. PostgreSQL Docs — CREATE STATISTICS — complete official reference.
2. PostgreSQL Docs — Multivariate Statistics — explanation of the three types.
3. Tomas Vondra — "Multi-Column Statistics" — the feature's main author, talks with cases.
4. pganalyze — Improving query performance with extended statistics — a modern tutorial with examples.
5. depesz — Waiting for PostgreSQL 12: MCV stats — analysis of the MCV feature.
6. Citus Data — When to use extended statistics — a practical guide with typical cases.

Capsule 04 of 08 — Module 7 — Database Performance & Query Tuning Guide