Indexes & Query Performance
Selectivity, Cardinality, and Statistics
Cardinality estimates predict row counts at each plan node; selectivity is the retained fraction. Planners derive them from sampled summaries and assumptions, so skew, correlation, cross-column dependence, parameters, and stale data can compound into wrong plan choices.
- Cardinality propagates through the plan.
- Distinct counts and frequencies model equality.
- Histograms model ranges.
- Correlation has multiple meanings.
- Multicolumn statistics repair selected dependencies.
- Statistics age with the data.
1,000,000 orders. status=RETURNED: 20,000 (2%). customer_id=7: 300,000 (30%).
Independence estimate: 1,000,000 × 0.02 × 0.30 = 6,000 rows.
Observed returns for customer 7 after a recall: 18,000 rows.
The 3× miss can change join build side, memory grant, scan choice, and spill risk.CREATE STATISTICS orders_customer_status_stats
(dependencies, mcv, ndistinct)
ON customer_id, status FROM orders;
ANALYZE orders;
SELECT attname, n_distinct, most_common_vals, histogram_bounds, correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders';| Statistic | Helps estimate | Blind spot |
|---|---|---|
n_distinct | Equality/group counts | Individual skew |
| MCV + frequencies | Hot equality values | Long tail |
| Histogram bounds | Range fraction | Within-bucket spikes |
| Physical correlation | Ordered index fetch locality | Predicate dependence |
| Extended dependencies/MCV | Cross-column combinations | Untracked columns/expressions |
Selectivity does not by itself determine index usefulness. Ten thousand physically adjacent rows can be cheaper than one thousand scattered heap fetches; coverage, clustering, cache, row width, and limit/order behavior belong in the cost model.