Statistics and the misestimate

What the planner knows about your data

Before you can fix a bad estimate, you need to know what the planner is reading. Every estimate the planner makes (lesson 02) comes from one place: the pg_statistic catalog, readable through the pg_stats view. ANALYZE (run manually, or automatically by autovacuum — lesson 04) samples each table and stores three things per column. The databases track’s statistics chapter covers the sampling internals; here we read these as the levers a SQL author pulls:

• n_distinct — how many distinct values the column has. Drives “how many rows match one value?” → roughly row_count / n_distinct.
• Most Common Values (most_common_vals + most_common_freqs) — the top values and their exact frequencies. For status = 'placed' the planner doesn’t guess; it reads the stored frequency. This is why skewed columns estimate well for the common values.
• Histogram (histogram_bounds) — buckets of equal row-count for everything not in the MCV list, used for range predicates like total > 100.

-- Inspect what the planner believes about a column:
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

default_statistics_target (default 100) controls how many MCV entries and histogram buckets are kept. Raise it on a column with many distinct values or heavy skew (ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;) to trade a slower ANALYZE for sharper estimates.

The correlated-columns trap

Here’s the math that breaks. By default the planner estimates a multi-column filter by assuming the columns are independent: it multiplies their individual selectivities. For WHERE city = 'Paris' AND country = 'France':

estimate = P(city='Paris') × P(country='France') × row_count

If 1% of rows are Paris and 2% are France, the planner predicts 0.01 × 0.02 = 0.0002 → 0.02% of rows. But city and country are functionally dependent — every Paris row is a France row. The real selectivity is just 1%, fifty times higher. The planner under-estimates by ~50×, expects a handful of rows, picks a nested loop, and detonates when reality delivers 480,000.

This is the most common senior-level misestimate in production, and it is invisible until a join is built on top of it. The independence assumption is fine for genuinely unrelated columns; it’s catastrophic for correlated ones (city/country, brand/model, zip/state, status/shipped_at).

The fix is extended statistics — you tell Postgres these columns travel together:

CREATE STATISTICS orders_city_country (dependencies, ndistinct, mcv)
  ON city, country FROM orders;
ANALYZE orders;   -- extended stats only populate on the next ANALYZE

Three kinds, each fixing a different miss:

• dependencies — captures functional dependency (Paris ⇒ France), fixing the multiplied-selectivity under-estimate above.
• ndistinct — captures the combined distinct count of a column group, fixing GROUP BY a, b estimates (independence over-counts groups).
• mcv — a multi-column most-common-values list for skewed combinations.

All three target the same root: the planner was reasoning about columns in isolation. Without dependencies, you get the nested-loop explosion; without ndistinct, GROUP BY over-predicts groups and wastes memory on a too-large hash table; without mcv, rare-combination queries get a generic estimate that can be off in either direction.

After CREATE STATISTICS + ANALYZE, the same query re-plans: estimate 480,000, actual 480,000, planner picks a hash join, 22 s → 60 ms. No query rewrite, no index, no hint — you corrected the estimate.