Cardinality Misestimates, Extended Statistics, Planner Knobs, and Evidence-Driven Tuning

Learning outcomes

The hardest planner incidents are rarely solved by memorizing node types. ServiceHub has a query where PostgreSQL estimates tens of rows but returns tens of thousands. The plan is internally rational given the estimate; the estimate is wrong because two predicates are strongly correlated. This lesson closes the chapter with an evidence hierarchy: fix information and query/index design before changing the planner's knobs.

1. Create a deliberately correlated ServiceHub dataset

Each service region has a dominant dispatch channel. A query that filters on both columns violates the naive independence assumption because the values are correlated. We keep a small amount of cross-region noise so the relationship is strong but not a perfect functional dependency.

DROP TABLE IF EXISTS app.ch11_cardinality_lab;CREATE TABLE app.ch11_cardinality_lab (    work_order_id bigint PRIMARY KEY,    region text NOT NULL,    channel text NOT NULL,    status text NOT NULL,    technician_id integer NOT NULL,    amount numeric(12,2) NOT NULL);INSERT INTO app.ch11_cardinality_labSELECT g,       CASE g % 4 WHEN 0 THEN 'north' WHEN 1 THEN 'south' WHEN 2 THEN 'east' ELSE 'west' END AS region,       CASE         WHEN g % 20 = 0 THEN 'phone'         WHEN g % 4 = 0 THEN 'mobile'         WHEN g % 4 = 1 THEN 'web'         WHEN g % 4 = 2 THEN 'partner'         ELSE 'batch'       END AS channel,       CASE g % 5 WHEN 0 THEN 'queued' WHEN 1 THEN 'assigned' WHEN 2 THEN 'done' WHEN 3 THEN 'cancelled' ELSE 'waiting' END,       1 + (g % 5000),       ((g % 70000) + 100)::numeric / 100FROM generate_series(1,500000) AS g;CREATE INDEX ch11_cardinality_region_channel_idxON app.ch11_cardinality_lab (region, channel);ANALYZE app.ch11_cardinality_lab;

SELECT region, channel, count(*) AS rowsFROM app.ch11_cardinality_labGROUP BY region, channelORDER BY region, rows DESC;

2. Capture the baseline estimate and actual rows

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT count(*)FROM app.ch11_cardinality_labWHERE region = 'north'  AND channel = 'mobile';

Record the estimated rows at the scan node and its actual rows. The ratio is more informative than a vague statement such as “the plan is wrong.” An estimate can be off without hurting the chosen plan; prioritize errors that change join order, scan type, memory sizing, or other important decisions.

SELECT attname, null_frac, n_distinct,       most_common_vals,       most_common_freqsFROM pg_statsWHERE schemaname = 'app'  AND tablename = 'ch11_cardinality_lab'  AND attname IN ('region','channel');

pg_stats exposes per-column information, but it cannot by itself express that north and mobile occur together much more frequently than independent multiplication predicts.

3. First repair: collect extended statistics

CREATE STATISTICS creates the definition; ANALYZE collects the data. PostgreSQL supports cross-column functional dependencies, most-common-value (MCV) lists, and multivariate n-distinct counts for different estimation problems.

DROP STATISTICS IF EXISTS app.ch11_region_channel_stats;CREATE STATISTICS app.ch11_region_channel_stats (dependencies, mcv)ON region, channelFROM app.ch11_cardinality_lab;ANALYZE app.ch11_cardinality_lab;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT count(*)FROM app.ch11_cardinality_labWHERE region = 'north'  AND channel = 'mobile';

The desired outcome is a better estimate—not a guaranteed different scan node. If the existing index scan was already optimal, the plan shape may remain unchanged while cardinality becomes more realistic. That still matters to larger joins and downstream memory decisions.

SELECT schemaname, statistics_name, attnames, kindsFROM pg_stats_extWHERE schemaname = 'app'  AND statistics_name = 'ch11_region_channel_stats';

4. Statistics targets: more detail has a cost

Raising a column statistics target can enlarge samples and MCV/histogram detail. That consumes more ANALYZE time and catalog space and can increase planning work. Raise it selectively when a concrete estimation error justifies the cost.

SELECT attname, attstattargetFROM pg_attributeWHERE attrelid = 'app.ch11_cardinality_lab'::regclass  AND attname IN ('region','channel');ALTER TABLE app.ch11_cardinality_labALTER COLUMN channel SET STATISTICS 500;ANALYZE app.ch11_cardinality_lab;

ALTER TABLE app.ch11_cardinality_labALTER COLUMN channel SET STATISTICS -1;ANALYZE app.ch11_cardinality_lab;

A target of -1 means use the server default statistics target. Do not copy “1000” into every column; high targets on unimportant columns waste maintenance and planning resources.

5. Extended statistics have boundaries

Functional dependency statistics help selected equality/constant patterns and do not solve every correlation problem. They are not a generic model of arbitrary expressions, range predicates, cross-table join correlation, or incompatible combinations. MCV lists are often more useful when specific value combinations recur disproportionately.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT count(*)FROM app.ch11_cardinality_labWHERE technician_id BETWEEN 100 AND 2000  AND amount > 500;

If this query is misestimated, region/channel statistics are irrelevant. Fix the information that corresponds to the failing predicate. Query tuning is a diagnosis, not a ritual of adding every statistics type.

6. Query and index design come before planner knobs

Statistics can be accurate yet the workload can still need a better index or a more sargable query. Conversely, an index can exist but look unattractive because cardinality estimates make the planner expect too many heap accesses. Diagnose both dimensions.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)SELECT work_order_id, technician_idFROM app.ch11_cardinality_labWHERE region = 'north'  AND channel = 'mobile'ORDER BY work_order_idLIMIT 500;

Do not add an index solely because this one plan performs a Sort. Consider call frequency, write rate, index overlap, ordering value, visibility, and whether a composite index would serve multiple important query shapes.

7. Cost and enable parameters are diagnostic probes

BEGIN;SET LOCAL enable_bitmapscan = off;SET LOCAL random_page_cost = 1.1;EXPLAIN (ANALYZE, BUFFERS, SETTINGS, TIMING OFF)SELECT work_order_id, technician_idFROM app.ch11_cardinality_labWHERE region = 'north'  AND channel = 'mobile'ORDER BY work_order_idLIMIT 500;ROLLBACK;

This experiment answers “what alternative would PostgreSQL consider if bitmap scans were discouraged and random I/O were modeled as cheaper?” It does not prove those GUCs should become permanent. SETTINGS keeps the copied plan honest by recording nondefault planner settings.

8. Compare before/after with a written evidence table

A successful cardinality repair may change no execution time in this isolated query yet prevent a catastrophic join choice when the same predicate appears inside a larger statement. Preserve both the local result and the broader mechanism.

9. Production tuning runbook

For a real slow-query incident, capture query ID/text and parameters; verify PostgreSQL version and plan-affecting settings; gather EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) only where safe; locate the earliest material estimate divergence; check statistics freshness and column/cross-column distributions; then test statistics, query, and index repairs. Only after that should you alter cost assumptions or enable flags—and normally first with SET LOCAL.

DROP STATISTICS IF EXISTS app.ch11_region_channel_stats;DROP TABLE IF EXISTS app.ch11_cardinality_lab;

Authoritative references

Planner and executor behavior is version-sensitive and workload-sensitive. Verify the exact PostgreSQL major, statistics state, server settings, indexes, and data distribution before generalizing any plan shown in this lesson.

• PostgreSQL 18 — Statistics Used by the Planner
• PostgreSQL 18 — CREATE STATISTICS
• PostgreSQL 18 — pg_stats
• PostgreSQL 18 — pg_stats_ext
• PostgreSQL 18 — Query Planning Configuration
• PostgreSQL 18 — Using EXPLAIN