Planner statistics and misestimates

conceptThe concept

ANALYZE samples about 300 times default_statistics_target (100) rows per table and stores, per column, the fraction of NULLs, the number of distinct values, the most common values with their frequencies, a histogram of the rest, and the physical correlation. The planner multiplies selectivities to estimate rows for each predicate.

Multiplying assumes columns are independent. city = 'London' AND country = 'UK' is estimated as P(London) times P(UK), far too low, because every London row is in the UK. CREATE STATISTICS ... (dependencies) teaches the planner functional dependencies; (ndistinct) fixes GROUP BY estimates on column combinations; (mcv) stores common value combinations.

Autovacuum runs ANALYZE after about 10% of a table changes (autovacuum_analyze_scale_factor). After a bulk load, run ANALYZE yourself. For skewed columns, ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 keeps more common values.

whyWhy it matters

An estimate off by 100x changes join algorithms and join order. Fixing statistics fixes the plan for every query that uses those columns, without rewriting any of them.

Use caseReal-world scenario

A marketing segment query filters by city and country together and the planner badly underestimates the rows, choosing a nested loop. One CREATE STATISTICS later, the estimate is exact.

Try itExamples

Run and edit these in the interactive lesson.

\x
SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';
\x

EXPLAIN ANALYZE
SELECT * FROM customers WHERE city = 'London' AND country = 'UK';

BEGIN;
CREATE STATISTICS customers_city_country (dependencies) ON city, country FROM customers;
ANALYZE customers;
EXPLAIN SELECT * FROM customers WHERE city = 'London' AND country = 'UK';
ROLLBACK;

The course database includes pgm.estimate(query), which returns the planner's row estimate. It is handy for comparing estimates without reading whole plans.

SELECT pgm.estimate($$SELECT * FROM customers WHERE city = 'London' AND country = 'UK'$$) AS estimated,
       (SELECT count(*) FROM customers WHERE city = 'London' AND country = 'UK') AS actual;

internalsUnder the hood

Statistics live in pg_statistic (readable via the pg_stats view) and extended statistics in pg_statistic_ext_data. Since 18, pg_upgrade carries statistics over to the new cluster, so the first hours after an upgrade no longer run on empty statistics.

Avoid / preferDo this, not that

ALTER SYSTEM SET default_statistics_target = 10000;

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;

-- bulk load 50 million rows, then run reports immediately

COPY big_table FROM ...;
ANALYZE big_table;

PitfallsCommon mistakes

• Forgetting to ANALYZE after a bulk load or a big DELETE, then debugging the "mysterious" bad plan.
• Raising default_statistics_target globally to 10000. ANALYZE and planning both get slower; raise it per column where skew matters.
• Creating extended statistics and not running ANALYZE afterwards; they stay empty until then.

LimitsWhen not to use it

Do not create extended statistics for every column pair. Add them where EXPLAIN ANALYZE shows estimates off by an order of magnitude on correlated filters.

SecuritySecurity notes

pg_stats shows most common values and histograms, which are samples of real data, but only for columns the current role can read. The underlying pg_statistic catalog is superuser-only.

ChallengeTry it yourself

SELECT pgm.estimate('SELECT * FROM orders WHERE customer_id = 1') AS estimated,
       (SELECT count(*) FROM orders WHERE customer_id = 1) AS actual;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Statistics used by the planner
• CREATE STATISTICS