Statistics Basics

PostgreSQL stores column histograms and distinct-value counts in the catalog. The planner multiplies selectivities to guess how many rows each plan node will process on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

SELECT schemaname, tablename, attname, n_distinct, most_common_vals, histogram_bounds
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

When to reach for this: EXPLAIN row estimates diverge from actual rows on filtered scans or joins.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  customer_id bigint NOT NULL
);
 
INSERT INTO orders (status, customer_id)
SELECT
  CASE WHEN i % 50 = 0 THEN 'shipped' ELSE 'pending' END,
  (i % 1000)::bigint
FROM generate_series(1, 100000) AS i;
 
ANALYZE orders;
 
EXPLAIN (ANALYZE)
SELECT count(*) FROM orders WHERE status = 'shipped';
 
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'customer_id');

What this demonstrates:

• most_common_vals for skewed enums
• n_distinct for cardinality hints
• Linking pg_stats content to plan row estimates

Deep Dive

How It Works

• ANALYZE samples rows (default default_statistics_target = 100) and builds per-column stats.
• Equality selectivity uses 1/n_distinct or MCV frequency when the literal appears in most_common_vals.
• Range predicates use histogram_bounds for non-uniform distributions.
• Multiple predicates on independent columns multiply selectivities unless extended stats say otherwise.

Key Catalog Views

SQL Notes

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders (status);

Gotchas

• Assuming uniform distribution - Without MCV/histogram, planner guesses badly on skewed status columns. Fix: Higher statistics target or extended stats.
• Stats on empty tables - Default estimates mislead until first meaningful ANALYZE. Fix: Analyze after initial load thresholds.
• Expression predicates lack stats - WHERE lower(email) = 'x' ignores plain column stats. Fix: Index on expression or extended stats on expression.
• Correlated AND columns - country + postal_code multiply incorrectly. Fix: CREATE STATISTICS ... dependencies.
• Never analyzing after large DELETE - reltuples stale for days. Fix: Manual ANALYZE post bulk DML.

NzA2MjAyfDcyMDFvaWdjfG9pLnNlZGl1Z2Vkb2M=

Alternatives

FAQs

Related

• ANALYZE & Autoanalyze - Refresh timing
• Extended Statistics - Correlated columns
• Bad Plan Case Studies - Estimate drift
• Planner GUCs - Cost knobs

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.