Diagnose PostgreSQL cardinality estimation errors

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

A planner can choose a reasonable algorithm for the wrong number of rows. If it expects a handful of matches and receives half the table, a nested loop or repeated lookup may become much more expensive than expected. The first useful question is therefore often not “Which index is missing?” but “Where did the row-count model first diverge from reality?”

Work from the bottom of the plan upward. A large error near a base-table scan can propagate through joins and aggregates. Fixing the final sort does not address a mistaken assumption that fed millions of unexpected rows into it.

Construct skew that has a known cause

This PostgreSQL 18 lab requires a disposable database and permission to create and analyze a table. It deliberately gives one customer almost all rows and many other customers one row each.

CREATE TABLE estimate_event (
  id bigint PRIMARY KEY,
  customer_id integer NOT NULL,
  state text NOT NULL
);
INSERT INTO estimate_event
SELECT g,
       CASE WHEN g <= 90000 THEN 1 ELSE g END,
       CASE WHEN g <= 90000 THEN 'open' ELSE 'closed' END
FROM generate_series(1,100000) AS s(g);
CREATE INDEX estimate_event_customer ON estimate_event(customer_id);
ANALYZE estimate_event;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM estimate_event WHERE customer_id = 1;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM estimate_event WHERE customer_id = 99999;

Both predicates use the same column and operator. Their selectivities are very different by construction. PostgreSQL's statistics can represent common values, so a different estimate or access path for these literals is not instability by itself. It can be the correct response to skew.

The fixture contains exactly ninety thousand rows for customer 1, but the plan's estimate need not equal that number. Analyze uses sampling, and planner statistics are a model rather than an exact count of every value. On PostgreSQL 18.6 this lab estimated 89,993 rows for customer 1 against 90,000 actual and chose a sequential scan; for customer 99999 it chose an index scan.

Inspect the model, not just its output

SELECT attname, null_frac, n_distinct,
       most_common_vals, most_common_freqs,
       histogram_bounds, correlation
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'estimate_event'
ORDER BY attname;

The most-common-value list is relevant to skewed equality predicates. Histograms summarize the distribution of values outside that list and can matter for ranges. n_distinct helps describe the number of distinct values. The correlation field concerns the relationship between column order and physical row order; it is not a measure of dependence between two separate columns.

That last distinction matters. A table can have customer and state strongly related while neither column's physical-order correlation answers the planner's multi-column selectivity question.

Reference diagnosis: locate the first estimate error, inspect the relevant distribution, and select an experiment that isolates its cause.

Separate stale data from insufficient detail

After a substantial distribution change, run ANALYZE in the lab and compare the same plan. If fresh statistics fix the estimate, investigate why production statistics lagged: bulk loads, per-table settings, partition behavior, or a workload changing faster than analyze runs.

Increasing a column's statistics target can represent more distribution detail at a cost in analysis and planning resources:

ALTER TABLE estimate_event
ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE estimate_event;

Five hundred is a demonstration value, not a universal recommendation. Test whether it changes the troublesome estimate enough to affect the plan. Increasing every column's target without evidence can consume resources without solving a structural problem.

Now inspect a correlated predicate:

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM estimate_event
WHERE customer_id = 1 AND state = 'open';

In this fixture, every row for customer 1 is open. Estimating the conditions independently can understate the combination: on PostgreSQL 18.6 the planner estimated 81,000 rows where 90,000 came back. More detailed single-column statistics cannot fully express every relationship between the columns. Extended statistics may be appropriate, but choose the statistic type from the query shape rather than adding one indiscriminately.

Check expressions and parameters

A query using lower(email), a date extraction, or a cast may not use the same statistics as a predicate on the underlying column. Before rewriting the data model, inspect the exact expression in the plan and whether expression statistics or an expression index fit the access pattern. Keep function volatility and semantic equivalence in mind; a faster expression that changes timezone behavior is not an equivalent query.

Prepared statements introduce another distinction. A generic plan cannot specialize itself to one concrete customer value in the same way as a custom plan. Compare the application's actual prepared execution to literal SQL. A good estimate for the literal does not disprove a parameter-sensitive planning problem.

Joins deserve separate scrutiny. A base scan can be well estimated while the join cardinality is wrong because the relationship between tables is poorly represented or because a missing uniqueness constraint leaves the planner without useful information. Do not claim that extended statistics on one table solve arbitrary cross-table dependence.

Write a diagnosis that can be disproved

A useful investigation records the query, parameter values, actual and estimated row counts at the first divergent node, statistics freshness, and the change applied. Repeat after one intervention. If refreshing statistics and changing the target simultaneously improves the plan, you have not isolated which change mattered.

Avoid using a forced scan method as the final diagnosis. It may demonstrate that another plan is faster for one parameter, but it does not repair the estimate or prove that the forced method is safe for all parameters. The goal is a model that chooses well across the workload, with explicit exceptions only when the evidence supports them.

Sources and review scope

• Planner statistics
• pg_stats view
• ANALYZE
• CREATE STATISTICS