ANALYZE, Statistics Targets, Extended Statistics, and Planner Cardinality Quality

Learning outcomes

Vacuum keeps tuple storage healthy; ANALYZE keeps the planner's statistical model useful. A query can be perfectly written yet receive a poor plan if row-count estimates are badly wrong. PostgreSQL samples columns, stores distributions in the statistics catalog, and can collect multivariate statistics when columns are correlated. This lesson treats an EXPLAIN estimate as a hypothesis and EXPLAIN ANALYZE as execution evidence—not as interchangeable facts.

1. Create deliberately correlated data

ServiceHub regions and dispatch zones are strongly correlated: a zone name usually belongs to one region. Without multivariate statistics, a planner may estimate region = X AND zone = Y by multiplying independent selectivities, even though the columns are not independent.

DROP TABLE IF EXISTS app.ch09_cases;CREATE TABLE app.ch09_cases (    case_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    region text NOT NULL,    zone text NOT NULL,    priority smallint NOT NULL,    opened_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch09_cases(region, zone, priority)SELECT CASE WHEN g % 2 = 0 THEN 'north' ELSE 'south' END,       CASE         WHEN g % 2 = 0 AND g % 10 < 8 THEN 'north-a'         WHEN g % 2 = 0 THEN 'north-b'         WHEN g % 10 < 8 THEN 'south-a'         ELSE 'south-b'       END,       (g % 5) + 1FROM generate_series(1, 100000) AS g;ANALYZE app.ch09_cases;

2. Inspect what ANALYZE recorded

SELECT last_analyze, analyze_count,       last_autoanalyze, autoanalyze_count,       n_mod_since_analyzeFROM pg_stat_all_tablesWHERE relid = 'app.ch09_cases'::regclass;SELECT attname, null_frac, n_distinct,       most_common_vals, most_common_freqs,       histogram_boundsFROM pg_statsWHERE schemaname = 'app'  AND tablename = 'ch09_cases'  AND attname IN ('region','zone');

pg_stats is a documented, security-filtered view over planner statistics. Exact MCV arrays and histogram boundaries depend on the sample and statistics target. Applications should not parse internal pg_statistic representation as a stable business API.

SHOW default_statistics_target;SELECT a.attname, a.attstattargetFROM pg_attribute AS aWHERE a.attrelid = 'app.ch09_cases'::regclass  AND a.attnum > 0  AND NOT a.attisdroppedORDER BY a.attnum;

An attstattarget of -1 means the column uses the cluster/session default. Per-column overrides should be exceptional and evidence-driven because higher targets increase ANALYZE sampling work and planner-statistics size.

3. Establish the cardinality error before adding a fix

EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM app.ch09_casesWHERE region = 'north'  AND zone = 'north-a';

Read the plan in two layers. The planner's rows=... is its pre-execution estimate. actual rows=... is what the executor observed during this run. A large ratio between them can distort join ordering and access-path decisions later, even if this single-table query remains fast.

4. Per-column statistics targets

Increasing a target asks ANALYZE to collect a more detailed sample/model for that column, at the cost of more ANALYZE work and larger statistics metadata. It does not directly fix cross-column dependence.

ALTER TABLE app.ch09_cases  ALTER COLUMN zone SET STATISTICS 500;ANALYZE app.ch09_cases;SELECT attname, n_distinct,       cardinality(most_common_vals) AS mcv_count,       cardinality(histogram_bounds) AS histogram_countFROM pg_statsWHERE schemaname = 'app'  AND tablename = 'ch09_cases'  AND attname = 'zone';

Use a larger target when a single column has a complex/skewed distribution that is underrepresented. Do not set every column to a very large number by default.

5. Extended statistics for correlation

DROP STATISTICS IF EXISTS app.ch09_cases_region_zone_stats;CREATE STATISTICS app.ch09_cases_region_zone_stats    (dependencies, mcv)ON region, zoneFROM app.ch09_cases;ANALYZE app.ch09_cases;SELECT statistics_name, attnames, kindsFROM pg_stats_extWHERE schemaname = 'app'  AND statistics_name = 'ch09_cases_region_zone_stats';

EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM app.ch09_casesWHERE region = 'north'  AND zone = 'north-a';

The estimate should often move closer to reality because the planner can model the observed relationship between region and zone. Exact estimates are sample-dependent; the lab criterion is improved reasoning, not a hard-coded row number.

Extended statistics are defined for columns/expressions of one table. They do not magically become a universal cross-table correlation model for arbitrary joins. If the estimation error comes from relationships across tables, investigate schema constraints, query shape, join statistics limitations, and representative data rather than adding unrelated single-table statistics objects.

6. Wrong approach: “ANALYZE makes queries fast”

ANALYZE can improve estimates, but it cannot invent a missing index, rewrite a poor predicate, eliminate a data-model mismatch, or make a large result set small. Diagnose the type of problem.

EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM app.ch09_casesWHERE region = 'north' AND zone = 'north-a';CREATE INDEX ch09_cases_region_zone_idx    ON app.ch09_cases(region, zone);ANALYZE app.ch09_cases;EXPLAIN (ANALYZE, BUFFERS)SELECT * FROM app.ch09_casesWHERE region = 'north' AND zone = 'north-a';

Better statistics can make the planner choose a better path; an index changes the available path portfolio. These are complementary mechanisms, not substitutes.

7. Production judgment: fix estimation where it matters

Statistics tuning is justified when cardinality error is material to plan quality and repeatable on representative data. Prefer the smallest intervention that describes the data better: fresh ANALYZE, a targeted column statistics increase, or an extended statistics object for known same-table correlation. Then re-measure estimate error and plan behavior. Do not raise every target or create every possible multivariate combination; both ANALYZE cost and planner metadata grow.

For rapidly changing tables, freshness can matter more than target size. If n_mod_since_analyze rises quickly and last_autoanalyze lags, revisit the table's analyze thresholds or autovacuum capacity before blaming the optimizer. Always separate “estimate improved” from “query became faster” because execution time can be dominated by I/O, locking, cache state, result volume, or missing access paths.

8. Revert and clean up

ALTER TABLE app.ch09_cases  ALTER COLUMN zone SET STATISTICS -1;DROP STATISTICS IF EXISTS app.ch09_cases_region_zone_stats;DROP TABLE app.ch09_cases;

Authoritative references

These mechanisms are version-sensitive. Use the documentation for the PostgreSQL major you actually operate.

• PostgreSQL 18 — Planner Statistics
• PostgreSQL 18 — CREATE STATISTICS
• PostgreSQL 18 — ANALYZE
• PostgreSQL 18 — pg_stats and pg_stats_ext