Planner Statistics and Bad Estimates

Watch the planner badly misjudge two correlated columns, then fix it with one CREATE STATISTICS object — and measure exactly how much better the estimate gets

Every plan choice this whole certificate is built on rests on one thing: the planner's estimate of how many rows a condition will match. Get that estimate right, and the planner reliably picks a good plan. Get it badly wrong, and even a technically-correct plan choice can turn out disastrous, because it was chosen for a workload that does not actually exist.\n\nThe classic way this goes wrong is two correlated columns filtered together. By default, PostgreSQL's statistics assume every column is independent of every other one, and multiplies their individual selectivities together to estimate a combined filter's selectivity. When two columns are actually tightly linked — like a city and the zip code that always goes with it — that independence assumption is simply false, and the resulting estimate can be off by a large, measurable factor. This lab reproduces exactly that gap, then closes it with one statistics object.

Reading the Planner's Own Data

SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'addresses';

n_distinct is the planner's estimate of how many distinct values a column has (a small positive number is an absolute count; a negative fraction means "distinct values scale with table size"). most_common_vals lists the specific values ANALYZE found frequently enough to track individually, which is exactly what a filter like city = 'Springfield' relies on for an accurate estimate.

Why Two Independently-Accurate Columns Still Produce a Bad Combined Estimate

Both city and zip_code can have perfectly accurate individual statistics, and the combined estimate for city = 'Springfield' AND zip_code = '01000' can still be badly wrong — because the default estimate multiplies each column's individual selectivity together, which is only correct if the columns are actually independent. A city and its own zip code are the opposite of independent: knowing one almost entirely determines the other.

Telling the Planner About the Correlation

CREATE STATISTICS addr_stat (dependencies) ON city, zip_code FROM addresses;
ANALYZE addresses;

This builds extended statistics specifically capturing how much knowing one column's value narrows down the other — exactly the relationship the default per-column statistics cannot represent at all.

Raising Detail for One Skewed Column

ALTER TABLE addresses ALTER COLUMN city SET STATISTICS 500;

This is a different fix for a different problem: not correlation between columns, but a single column whose value distribution is too detailed for the default sampling (100 buckets) to capture accurately — common for a column with many values where a handful are extremely frequent and the rest are rare.

The Independence Assumption

PostgreSQL's default per-column statistics estimate a combined multi-condition filter by multiplying each condition's individual selectivity together, which is mathematically correct only when the columns involved are statistically independent. Real-world correlated columns — a city and its zip code, a country and its currency — violate this assumption constantly, and the resulting estimate error scales with how strong the real correlation actually is.

CREATE STATISTICS (dependencies)

Builds extended statistics that specifically measure functional dependency between two or more columns — how much knowing one column's value narrows down the plausible values of another. Once built and refreshed with ANALYZE, the planner consults these extended statistics for filters spanning the same columns, instead of falling back to the independence assumption alone.

🔎 Read the Planner's Own Statistics

Check pg_stats for both columns before doing anything else.

psql -U postgres -d beer_db -c "SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'addresses' AND attname IN ('city','zip_code');"

student@lab:~$ psql -U postgres -d beer_db -c "SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'addresses' AND attname IN ('city','zip_code');" SET attname | n_distinct | most_common_vals ----------+------------+------------------------------------------------------ city | 5 | {Franklin,Springfield,Greenville,Fairview,Riverside} zip_code | 5 | {04000,01000,05000,03000,02000} (2 rows)

❌ Watch the Combined Estimate Fail

Filter on both correlated columns together and compare the planner's estimate against the real count.

psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM addresses WHERE city = 'Springfield' AND zip_code = '01000';"

psql -U postgres -d beer_db -c "SELECT count(*) FROM addresses WHERE city = 'Springfield' AND zip_code = '01000';"

student@lab:~$ psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM addresses WHERE city = 'Springfield' AND zip_code = '01000';" SET QUERY PLAN ------------------------------------------------------------------------- Seq Scan on addresses (cost=0.00..2110.00 rows=3955 width=20) Filter: ((city = 'Springfield'::text) AND (zip_code = '01000'::text)) (2 rows) student@lab:~$ psql -U postgres -d beer_db -c "SELECT count(*) FROM addresses WHERE city = 'Springfield' AND zip_code = '01000';" SET count ------- 20000 (1 row)

🔧 Tell the Planner About the Correlation

Create extended statistics capturing the dependency between the two columns, then refresh them.

psql -U postgres -d beer_db -c "CREATE STATISTICS addr_stat (dependencies) ON city, zip_code FROM addresses;"

psql -U postgres -d beer_db -c "ANALYZE addresses;"

student@lab:~$ psql -U postgres -d beer_db -c "CREATE STATISTICS addr_stat (dependencies) ON city, zip_code FROM addresses;" SET CREATE STATISTICS student@lab:~$ psql -U postgres -d beer_db -c "ANALYZE addresses;" SET ANALYZE

✅ Measure the Improvement

Run the exact same EXPLAIN again and compare the new estimate against the real count of 20,000.

psql -U postgres -d beer_db -c "EXPLAIN SELECT * FROM addresses WHERE city = 'Springfield' AND zip_code = '01000';"

🎯 Raise Detail for a Single Skewed Column

Use a different tool for a different problem: increase the statistics target for one column whose distribution needs finer sampling.

psql -U postgres -d beer_db -c "ALTER TABLE addresses ALTER COLUMN city SET STATISTICS 500;"

psql -U postgres -d beer_db -c "SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = 'addresses'::regclass AND attname = 'city';"

student@lab:~$ psql -U postgres -d beer_db -c "ALTER TABLE addresses ALTER COLUMN city SET STATISTICS 500;" SET ALTER TABLE student@lab:~$ psql -U postgres -d beer_db -c "SELECT attname, attstattarget FROM pg_attribute WHERE attrelid = 'addresses'::regclass AND attname = 'city';" SET attname | attstattarget ---------+--------------- city | 500 (1 row)

Lab 3.1.2 complete. A real bad estimate, measured, explained, and fixed:\n\n\n pg_stats read : ✅ both columns individually accurate\n Bad combined estimate shown : ✅ 3,955 estimated vs 20,000 actual\n CREATE STATISTICS applied : ✅ dependency captured, ANALYZE refreshed\n Improvement measured : ✅ 19,773 estimated — within ~1% of exact\n

Enable JavaScript to run the live terminal and track your progress.