Statistics and Selectivity: Why the Planner Guesses Wrong

The one thing to understand first

The optimizer chooses plans by estimating how many rows each operation produces. Those estimates come entirely from statistics gathered by ANALYZE into the pg_statistic catalog (human-readable via pg_stats). Bad statistics → bad estimates → bad plans. Almost every "mysteriously slow query" traces back here.

*The planner is only as smart as its newest ANALYZE, and it assumes your columns are independent until you tell it otherwise.* Those two facts — stale stats and the independence assumption — explain the overwhelming majority of catastrophically wrong row estimates you will ever debug.

What ANALYZE collects

For each column, ANALYZE samples rows (default 300 × statistics_target) and computes:

• null_frac — fraction of NULLs.
• n_distinct — number of distinct values (or a negative ratio for scaling).
• most_common_vals / most_common_freqs — the MCV list and frequencies, for skewed equality.
• histogram_bounds — equi-depth buckets for range estimation.
• correlation — how closely physical row order matches the column's sort order (drives index-scan cost).

SELECT attname, n_distinct, null_frac,
       array_length(most_common_vals,1) AS n_mcv, correlation
FROM   pg_stats
WHERE  tablename = 'orders';

How a single-column estimate is made

For WHERE status = 'shipped': if 'shipped' is in the MCV list, the planner uses its exact frequency. If not, it assumes the value is one of the remaining distinct values, uniformly distributed over (1 - sum(MCV freqs)). For WHERE amount > 100 it interpolates within the histogram. These are good when the column is well-described and poor when it is skewed beyond the MCV list size.

The multi-column trap

The planner assumes columns are independent by default. For WHERE city = 'Paris' AND country = 'France' it multiplies the two selectivities — but those columns are correlated (Paris implies France), so it badly under-estimates. The result is often a nested loop chosen for an expected handful of rows that turns into millions.

Extended statistics fix correlation

Tell PostgreSQL the columns are related with CREATE STATISTICS (src/backend/statistics/):

CREATE STATISTICS s_geo (dependencies, ndistinct, mcv)
  ON city, country FROM addresses;
ANALYZE addresses;

• dependencies — functional dependencies (city → country).
• ndistinct — true number of distinct combinations.
• mcv — most common combinations and their frequencies.

This single feature resolves a large class of join-order and row-count disasters on real-world schemas.

Raising resolution

For a high-cardinality skewed column, increase its statistics target so the MCV list and histogram are larger and the sample bigger:

ALTER TABLE events ALTER COLUMN tenant_id SET STATISTICS 1000;
ANALYZE events;

Layer 3 — Watch it happen on your own database

-- The independence trap, made visible
EXPLAIN SELECT * FROM addresses WHERE city = 'Paris' AND country = 'France';
--   rows=… (planner multiplies the two selectivities -> far too low)

CREATE STATISTICS s_geo (dependencies, ndistinct, mcv)
  ON city, country FROM addresses;
ANALYZE addresses;

EXPLAIN SELECT * FROM addresses WHERE city = 'Paris' AND country = 'France';
--   rows=… now close to actual, because Postgres knows city -> country

Run the first EXPLAIN, note the wildly low row estimate, create the extended statistics, then run it again. The estimate jumps to a realistic number because the planner stops treating city and country as independent. Pair this with EXPLAIN ANALYZE and you can pinpoint exactly which column's stats are lying by finding the largest estimate-vs-actual gap.

Layer 4 — The levers this hands you

• Extended statistics (CREATE STATISTICS with dependencies / ndistinct / mcv) fix correlated-column under-estimates — the single highest-leverage fix on real schemas.
• Per-column resolution: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 enlarges the MCV list and histogram for skewed, high-cardinality columns.
• Autoanalyze keeps stats fresh, but bulk loads outrun it — run ANALYZE explicitly after large data changes.
• Expression indexes get their own stats — an index on lower(email) lets ANALYZE describe that expression.
• Partitioned tables need stats on partitions and benefit from analyzing the parent.

Layer 5 — What an Oracle DBA should expect vs what they get

Both engines live and die by statistics, but the tooling and defaults differ:

• Extended statistics ≈ column groups. Oracle solves the correlation problem with column-group statistics (often auto-created from workload); PostgreSQL requires you to declare CREATE STATISTICS explicitly per column combination.
• No automatic histograms on "popular" columns. Oracle's DBMS_STATS can auto-decide where histograms are needed; in Postgres you raise statistics_target yourself where skew hurts.
• **ANALYZE samples, like DBMS_STATS with estimate_percent.** Both sample rather than full-scan by default; the Postgres sample size scales with statistics_target (300 × target rows).
• No optimizer hints to paper over bad stats. Where an Oracle DBA might add a CARDINALITY hint, the Postgres fix is always to correct the statistics themselves.

Key takeaway

ANALYZE fills pg_statistic with null fractions, n_distinct, MCV lists, histograms, and physical correlation; the planner turns those into selectivities and row estimates. The two classic failures are stale stats (run ANALYZE after bulk changes) and the independence assumption on correlated columns (fix with CREATE STATISTICS). Tuning statistics fixes the cause; rewriting the query only treats the symptom.