Planner statistics in PostgreSQL: statistics targets, correlated columns and misestimates

What it is

The documentation describes the planner's inputs: reltuples and relpages in pg_class for table size, and pg_statistic (readable through the pg_stats view) with each column's most common values and their frequencies, a histogram of the remaining values, the null fraction and the number of distinct values. ANALYZE, manual or through autovacuum, samples the table to fill these in. The number of most-common-value and histogram entries per column is bounded by default_statistics_target (100 by default) or a per-column ALTER TABLE ... ALTER COLUMN ... SET STATISTICS. For several conditions in one WHERE clause the planner multiplies selectivities, which the documentation states assumes the conditions are independent.

Why it matters

A plan is chosen from estimates, not from data. When an estimate is off by orders of magnitude the planner picks a nested loop where a hash join was needed, or an index scan that touches half the table, and the query is slow although every index exists. EXPLAIN ANALYZE shows the mismatch as estimated rows against actual rows at the node where it starts.

How to apply

• Check freshness first: last_analyze and last_autoanalyze in pg_stat_user_tables. A table just bulk-loaded often has no statistics at all.
• For a skewed column (status codes, a few huge tenants among many small ones) whose estimate is wrong for the rare values, raise the target on that column only, for example SET STATISTICS 1000, then ANALYZE; the documentation names the cost as more space in pg_statistic and slightly more planning time.
• For conditions on correlated columns (city and postal code, order date and shipment date) create extended statistics: CREATE STATISTICS s (dependencies, ndistinct, mcv) ON a, b FROM t, then ANALYZE. Functional dependencies apply only to equality conditions and IN lists with constants, mcv lists capture common combinations, ndistinct improves GROUP BY estimates.
• For a filter on an expression (lower(email), date_trunc('day', ts)) create expression statistics with CREATE STATISTICS ON (expr) FROM t, which the documentation describes as giving benefits similar to an expression index without the overhead of index maintenance.
• Re-run EXPLAIN ANALYZE after each change and keep it only if estimates moved towards actuals.

Pitfalls

Raising default_statistics_target globally costs pg_statistic space and estimation time on every column for little gain; target the columns that need it. pg_upgrade transfers most statistics only from PostgreSQL 18 on and never the extended ones, so an upgrade needs a fresh ANALYZE. A prepared statement may run a generic plan whose estimates ignore the actual parameter value. A sample cannot follow a distribution that changes by the hour.