Data Interview: How Would You Use PostgreSQL Extended Statistics to Fix Cardinality Misestimation?

Prompt and scope

A PostgreSQL query is fast with a single-column filter but chooses a bad join order when filtering customer_tier, region, and status together. Diagnose the cardinality error, explain when to use extended statistics, and show how you would validate the gain and its limits.

PostgreSQL primarily collects default statistics per column. When columns are correlated, the planner's independence assumption can multiply selectivities incorrectly. CREATE STATISTICS can collect functional dependencies, most-common-value combinations, or multivariate distinct counts, but it does not replace an index or make every predicate accurate automatically.

What the interviewer evaluates

• Using EXPLAIN (ANALYZE, BUFFERS) to compare estimated and actual rows.
• Explaining why correlated columns break the independence assumption.
• Choosing among dependencies, mcv, and ndistinct from the error shape.
• Knowing that an extended statistics object needs ANALYZE to populate data.
• Validating plan changes with representative workloads rather than adding objects by instinct.
• Stating sampling, maintenance, expression, and cross-table limitations.

Clarifying questions to ask

1. Does the error occur in filtering, joining, or grouping?
2. What are table size, skew, update rate, and default_statistics_target?
3. Are the three columns on one table with stable correlation in the same predicates?
4. Is the problem latency, memory, a bad join algorithm, or resource cost?
5. Are indexes, partitions, and current single-column statistics already appropriate?

30-second answer

I would locate the first major estimated-versus-actual row error and check statistics freshness and data distribution. If same-table columns have stable correlation, I would create the smallest appropriate dependencies, mcv, or ndistinct object, run ANALYZE, and compare estimation error, join method, buffer reads, and tail latency on representative parameters. Extended statistics improve planner knowledge; they do not replace indexes, partitioning, or modeling. Cross-table, time-varying, or under-sampled relationships need continued data and plan governance.

Step-by-step deep dive

1. Locate the estimation error

Compare estimated and actual rows at every node in EXPLAIN (ANALYZE, BUFFERS) and find the first order-of-magnitude divergence. Record predicates, join order, planning and execution time, and buffer hits instead of looking only at total latency.

2. Check single-column statistics and freshness

Confirm that recent ANALYZE covered the table and inspect pg_stats for most-common values, histograms, and null fractions. After a large change, severe skew, or an undersized statistics target, fix sampling and refresh cadence before adding a multivariate object.

3. Choose a statistics type

dependencies describes functional relationships where one column strongly implies another. mcv captures common combinations that dominate selectivity. ndistinct estimates the number of distinct combinations and is useful for grouping or deduplication. Multiple kinds can share one object, but the error and workload should justify each one.

CREATE STATISTICS orders_customer_region_stats
  (dependencies, mcv, ndistinct)
  ON customer_tier, region, status
  FROM orders;

ANALYZE orders;

4. Revalidate the plan

Rerun the query with production-like parameters, cache state, and concurrency. Compare row error at key nodes, join method, memory, temporary files, and p95/p99. A changed plan is not automatically better; verify stable resource use across parameter values.

5. Manage sampling and target size

Extended statistics are sampled, so rare combinations or rapidly changing data may be missed. Measure ANALYZE time, load, and benefit before raising a target for hot columns; do not maximize the global target blindly. Keep the column set small enough that maintenance remains justified.

6. State boundaries and alternatives

Extended statistics describe relationships within one table and do not directly model cross-table correlation or change an access path. Cross-table errors may require a query rewrite, pre-aggregation, partitioning, materialized results, or a model change. Correlation that varies by tenant, season, or state transition needs continuous monitoring.

7. Build regression and cleanup

Keep representative plans and estimation errors in a regression set and rerun them after PostgreSQL upgrades, migrations, and schema changes. Remove an object that serves a deleted query, adds maintenance cost, or produces no measurable improvement, recording the reason. Link query fingerprints, statistics objects, plan changes, and production latency.

High-quality sample answer

I would find the first plan node where estimated and actual rows differ by orders of magnitude and confirm that single-column statistics are fresh. If the three order columns have stable same-table correlation, I would start with the smallest dependencies or mcv object, run ANALYZE, and compare estimation error, join order, buffer reads, and tail latency with representative parameters. If the issue is a grouped or deduplicated combination count, I would evaluate ndistinct.

I would not treat extended statistics as an index replacement or promise to solve cross-table correlation. For rare combinations, changing distributions, or insufficient samples, I would measure a higher target's cost and consider a query rewrite, pre-aggregation, or model change. Plans and estimation errors would enter a regression set so the benefit remains observable.

Common mistakes

• Looking only at total latency → misses the source of error → compare estimated and actual rows node by node.
• Enabling every statistics kind by default → adds maintenance without proof → choose the smallest set justified by the error.
• Skipping ANALYZE → the planner has no new data → include refresh and validation.
• Treating statistics as an index → the query may still scan too much data → separate estimation from access paths.
• Proving value with one parameter → distributions and plans vary → test parameters, concurrency, and regressions.
• Ignoring cross-table correlation → one-table statistics cannot fix the join → rewrite or govern the model.

Follow-up questions and responses

When would you prefer dependencies?

When one column nearly determines another, such as a stable relationship between region and a restricted state. Prove the dependency with data and plan errors first.

How do mcv and ndistinct differ?

mcv focuses on common multi-column combinations and filter selectivity. ndistinct focuses on the number of distinct combinations, useful for grouping, deduplication, or join cardinality.

Does an extended statistics object update automatically?

Its data is collected by ANALYZE, triggered automatically or manually. An object definition existing does not mean its data is fresh.

Why might raising the statistics target still fail?

Sampling may miss rare combinations, and relationships can change over time. Measure estimation error and ANALYZE cost, then consider a model or query strategy.

How do you check for regressions?

Save plans for multiple parameters, compare estimation error, resource use, p95/p99, and temporary files, and rerun after version, data-volume, and schema changes.

When should you remove an extended statistics object?

Remove it when the query disappeared, estimation did not improve, or maintenance cost exceeds value. Keep the before-and-after evidence so objects do not accumulate indefinitely.