Cardinality Misestimates & Plan Regressions - Correlated Columns, Extended Statistics, Generic Plans & the Slow-Overnight Runbook
Why estimates go wrong: independence assumption with correlated and anti-correlated columns (runnable q-errors and multiplicative error growth, Leis et al. VLDB 2015); real PG 17 CREATE STATISTICS dependencies/ndistinct fixing estimates; stale stats and out-of-range values; custom vs generic plans for skewed prepared statements (real plans + runnable heuristic sim), parameter sniffing contrast; auto_explain/pg_stat_statements; step-labeled slow-overnight runbook; pg_hint_plan and plan-pinning trade-offs.
- 1Gist
- 2Maps
- 3Q&A
- 4Sandbox
Voice readout needs Web Speech Synthesis in this browser.
Question ladder
L1
What is the independence assumption?
Answer
For a = x AND b = y the planner multiplies the two selectivities as if the columns were unrelated.
L2
What is q-error?
Answer
max(estimate / actual, actual / estimate), the standard measure of how wrong an estimate is.
L3
Why do errors grow with joins?
Answer
Each join multiplies estimates, so per-input errors multiply too: a 4x error per input can reach 1,024x over five inputs.
L4
What does CREATE STATISTICS (dependencies, ndistinct) fix?
Answer
Equality predicates on functionally dependent columns, and group counts for column combinations.
L5
When does PostgreSQL switch to a generic plan?
Answer
After five custom executions, if the generic plan's cost is not worse than the average custom plan cost.
L6
What does auto_explain give you?
Answer
The plan actually used during the slow execution, with actual rows if log_analyze is on.
L7
When is plan pinning acceptable?
Answer
As a documented, owned, temporary mitigation for a critical query, or when the data shape is known to be stable.
Failure modes
Correlated predicates underestimated
Multiplying selectivities of related columns gave 100 rows for a 1000-row result, which can push a join into a nested loop.
Generic plan meets the whale tenant
A forced generic plan estimated 41 rows for a tenant that returned 100,000.
Stale statistics after a bulk load
New values sit above the histogram's top bound, so range predicates on fresh data are estimated far too low.
Misconceptions
Bad plans come from a bad cost model.
Leis et al. showed cardinality errors, not cost formulas, are the dominant cause.
Re-running the query by hand reproduces the problem.
An ad hoc query with literals gets a custom plan; the application path may be using a generic one.
Extended statistics work as soon as they are created.
The statistics object is empty until the next ANALYZE.
Interviewer traps
Raising default_statistics_target globally to fix correlation.
Per-column statistics cannot express cross-column relationships, and ANALYZE and planning slow down everywhere.
Setting enable_nestloop = off in the config.
It breaks unrelated queries and never fixes the estimate.
Design scenario
Same prompt for every reader.
Requirements
Detect the regression within minutes, capture the slow plan, and fix it without slowing small tenants.
Failure assumptions
- The driver uses server-side prepared statements.
- One tenant owns half the rows.
- Nightly bulk imports add a day of data.
Constraints
- PostgreSQL 17 with pg_stat_statements and auto_explain available.
- No pg_hint_plan in production.
Prompt
A multi-tenant ticketing API slows down for one large tenant every few days, with no deploy. Design detection, diagnosis and a durable fix.
API
Which statements are prepared, and can the whale tenant take a different code path?
Data
Which statistics, statistics targets and extended statistics cover tenant_id and the correlated columns?
Architecture
How do pg_stat_statements, auto_explain sampling and alerts fit together, and where do you set plan_cache_mode?
Overview
Optimizers rarely fail because the cost model is wrong; they fail because the row estimates fed into it are wrong. Leis et al. ("How Good Are Query Optimizers, Really?", VLDB 2015) showed on real-world data that cardinality errors, not cost-model details, are the dominant cause of bad plans, and that the errors grow multiplicatively with every join. The usual culprits are predictable: correlated columns (the planner multiplies selectivities as if they were independent), stale statistics (yesterday's histogram does not know today's values), skew hidden behind a parameter (a generic plan for a prepared statement chosen without seeing the value, also called parameter sniffing in SQL Server), and opaque expressions the planner cannot estimate. This page shows each failure in real PostgreSQL output, the fixes (ANALYZE, CREATE STATISTICS, per-column statistics targets, plan_cache_mode, rewrites), the tools for seeing it in production (auto_explain, pg_stat_statements), the last-resort escape hatches (pg_hint_plan, plan pinning), and a runbook for "this query got slow overnight".
Where estimates come from
ANALYZE samples up to 300 x default_statistics_target rows per table (30,000 by default) and stores, per column, the null fraction, the number of distinct values, the most common values (MCVs) with their frequencies, a histogram of the remaining values, and the physical correlation. For a predicate the planner looks up the column's statistics; for several predicates combined with AND it multiplies their selectivities. The statistics fundamentals live on the existing selectivity/statistics page; here we care about where that machinery breaks.
Decisions
- 1
Step 1: ANALYZE samples rows
- nextStep 2: per-column stats - null_frac, n_distinct, MCVs, histogram, correlation
- 2
Step 2: per-column stats - null_frac, n_distinct, MCVs, histogram, correlation
- nextStep 3: planner estimates each predicate's selectivity
- 3
Step 3: planner estimates each predicate's selectivity
- nextStep 4: several predicates?
- ?
Step 4: several predicates?
- defaultStep 5a: multiply selectivities - independence assumption
- extended statistics existStep 5b: use dependencies, ndistinct or multivariate MCVs
- 5
Step 5a: multiply selectivities - independence assumption
- nextStep 6: rows estimate feeds join sizes, join order, algorithms
- 6
Step 5b: use dependencies, ndistinct or multivariate MCVs
- nextStep 6: rows estimate feeds join sizes, join order, algorithms
- 7
Step 6: rows estimate feeds join sizes, join order, algorithms
- nextStep 7: errors compound through every join above
- 8
Step 7: errors compound through every join above
Lesson map
Cardinality Misestimates & Plan Regressions - Correlated Columns, Extended Statistics, Generic Plans & the Slow-Overnight Runbook
Why estimates go wrong: independence assumption with correlated and anti-correlated columns (runnable q-errors and multiplicative error growth, Leis et al. VLDB 2015); real PG 17 CREATE STATISTICS dependencies/ndistinct fixing estimates; stale stats and out-of-range values; custom vs generic plans for skewed prepared statements (real plans + runnable heuristic sim), parameter sniffing contrast; auto_explain/pg_stat_statements; step-labeled slow-overnight runbook; pg_hint_plan and plan-pinning trade-offs.
Architecture. Architecture
Select a node to see why it exists, or an edge to see the protocol, direction, effect, and consequence.
Mermaid export
flowchart TB a["Step 1: ANALYZE samples rows"] b["Step 2: per-column stats - null_frac, n_distinct, MCVs, histogram, correlation"] c["Step 3: planner estimates each predicate's selectivity"] d["Step 4: several predicates?"] e["Step 5a: multiply selectivities - independence assumption"] f["Step 5b: use dependencies, ndistinct or multivariate MCVs"] g["Step 6: rows estimate feeds join sizes, join order, algorithms"] h["Step 7: errors compound through every join above"] a -->|continues| b b -->|continues| c c -->|continues| d d -->|default| e d -->|extended statistics exist| f e -->|continues| g f -->|continues| g g -->|continues| h
Plan stability options compared
| Approach | Where | Pros | Cons |
|---|---|---|---|
| Fix statistics | Any engine | Fixes root cause, keeps adaptivity | Needs diagnosis; some correlations hard to capture |
| Rewrite the query | Any engine | Durable, reviewable in code | Requires understanding the plan; can be ugly |
| Hints (pg_hint_plan, MySQL optimizer hints, Oracle hints) | Extension or built-in | Immediate, surgical | Frozen against future data; hints silently ignored if names change |
| Plan forcing (SQL Server Query Store, Oracle SQL Plan Management, Aurora PostgreSQL QPM) | Engine feature | Captures a known-good plan, reversible | Vendor-specific; forced plans can become wrong as data grows |
Optimization fences (MATERIALIZED CTE, OFFSET 0 subquery) | PostgreSQL | No extension needed | Blocks useful optimizations too; obscure to readers |
Global setting changes (enable_nestloop = off) | Config | Quick | Breaks unrelated queries; never a real fix |
Fix the estimate or pin the plan?
Prefer
Fix the estimate
Give the planner correct row counts and let it choose.
- CREATE STATISTICS moved rows=100 to rows=980 against 1000 actual.
- The GROUP BY estimate dropped from 1,000 groups to the true 100.
- The planner keeps adapting as data grows.
Alternative
Pin the plan
Force a join order or method with hints or a fence.
- Immediate and surgical for one critical query.
- Frozen against tomorrow's data distribution.
- Hints are silently ignored when names change.
Where estimates come from and where they break
Diagram 1 condensed.
- 1
Sample
ANALYZE samples rows and stores per-column statistics. - 2
Estimate each predicate
Selectivity comes from MCVs, histograms and n_distinct. - 3
Combine predicates
By default selectivities are multiplied; extended statistics correct for dependencies. - 4
Feed the plan
The row estimate drives join sizes, join order and algorithms. - 5
Compound the error
Errors multiply through every join above.
Failure 1: correlated columns (and the fix)
City determines state; zip code determines city; country = 'JP' AND currency = 'JPY' are nearly the same predicate. Multiplying their selectivities underestimates the result, sometimes by orders of magnitude. The runnable model first shows the arithmetic, including the less obvious anti-correlation case where two predicates never hold together but the planner expects hundreds of rows.
"""Why cardinality estimates go wrong: the independence assumption, and how errors compound.
1) Correlated predicates: estimate = N * P(a) * P(b), but when a implies b the truth is N * P(a).
2) Error propagation: each join multiplies estimates, so per-predicate q-errors multiply too
(the core finding of Leis et al., "How Good Are Query Optimizers, Really?", VLDB 2015).
Data sizes are example values; the generator is deterministic.
"""
from collections import Counter
N = 100_000
rows = [{"city": f"city_{g % 100}", "state": f"state_{(g % 100) // 10}", "plan": "pro" if g % 3 == 0 else "free",
"tier": "gold" if g % 4 == 0 else "basic"}
for g in range(1, N + 1)]
def p(col, val):
return sum(1 for r in rows if r[col] == val) / N
def q_error(est, actual):
est, actual = max(est, 1), max(actual, 1)
return max(est / actual, actual / est)
for preds in ([("city", "city_42"), ("state", "state_4")], # city -> state: fully dependent
[("city", "city_42"), ("plan", "pro")], # genuinely independent columns
[("city", "city_42"), ("tier", "gold")]): # hidden anti-correlation: never both
est = N
for c, v in preds: est *= p(c, v) # independence assumption
actual = sum(1 for r in rows if all(r[c] == v for c, v in preds))
label = " AND ".join(f"{c}='{v}'" for c, v in preds)
print(f"{label:35s} est={est:7.0f} actual={actual:6d} q-error={q_error(est, actual):.1f}")
# Functional-dependency degree, what CREATE STATISTICS (dependencies) measures:
# fraction of rows where knowing city pins down state.
by_city = Counter((r["city"], r["state"]) for r in rows)
dominant = Counter()
for (city, state), n in by_city.items(): dominant[city] = max(dominant[city], n)
degree = sum(dominant.values()) / N
print(f"dependency degree city => state = {degree:.3f} (PostgreSQL stored 1.000000 in the SQL demo)")
# With the dependency, PostgreSQL's formula: P(a AND b) = P(a) * (degree + (1 - degree) * P(b))
pa, pb = p("city", "city_42"), p("state", "state_4")
print(f"dependency-aware estimate = {N * pa * (degree + (1 - degree) * pb):.0f}")
# Error compounding: a 5-way join where each base estimate is off by a factor f
for f in (2, 4, 10):
print(f"per-input q-error {f:>2} -> worst-case 5-input join q-error up to {f ** 5:,}")Output:
city='city_42' AND state='state_4' est= 100 actual= 1000 q-error=10.0
city='city_42' AND plan='pro' est= 333 actual= 334 q-error=1.0
city='city_42' AND tier='gold' est= 250 actual= 0 q-error=250.0
dependency degree city => state = 1.000 (PostgreSQL stored 1.000000 in the SQL demo)
dependency-aware estimate = 1000
per-input q-error 2 -> worst-case 5-input join q-error up to 32
per-input q-error 4 -> worst-case 5-input join q-error up to 1,024
per-input q-error 10 -> worst-case 5-input join q-error up to 100,000The last lines are the important one for multi-join queries: a modest per-input error, multiplied through five joins, can become a factor of a thousand or more at the top of the plan, which is exactly where the planner decides join algorithms for the largest inputs.
Now the real thing in PostgreSQL 17, including the fix with extended statistics and a prepared-statement experiment covered in the next section:
-- Page 4: correlated columns fool the independence assumption; CREATE STATISTICS fixes it.
-- Then: a prepared statement on a skewed column, custom plan vs generic plan.
\pset footer off
SET client_min_messages = warning;
DROP SCHEMA IF EXISTS p4 CASCADE;
CREATE SCHEMA p4;
SET search_path = p4;
SET max_parallel_workers_per_gather = 0;
-- city fully determines state (100 cities, 10 states): perfectly correlated columns
CREATE TABLE addresses AS
SELECT g AS id, 'city_' || (g % 100) AS city, 'state_' || (g % 100) / 10 AS state
FROM generate_series(1, 100000) g;
ANALYZE addresses;
-- 1) Planner multiplies selectivities: P(city) * P(state) = 1/100 * 1/10 -> ~100 rows; truth is 1000
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM addresses WHERE city = 'city_42' AND state = 'state_4';
EXPLAIN SELECT city, state, count(*) FROM addresses GROUP BY city, state; -- group estimate without extended stats
-- 2) Teach the planner the functional dependency city -> state, re-ANALYZE, re-run
CREATE STATISTICS addr_city_state (dependencies, ndistinct) ON city, state FROM addresses;
ANALYZE addresses;
SELECT stxname, stxkind, dependencies FROM pg_stats_ext JOIN pg_statistic_ext ON stxname = statistics_name
WHERE statistics_name = 'addr_city_state';
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM addresses WHERE city = 'city_42' AND state = 'state_4';
-- GROUP BY estimate now uses the ndistinct statistic (true answer: 100 groups)
EXPLAIN SELECT city, state, count(*) FROM addresses GROUP BY city, state;
-- 3) Skew + prepared statements: tenant 1 owns half the rows, every other tenant is tiny
CREATE TABLE tickets AS
SELECT g AS id, CASE WHEN g <= 100000 THEN 1 ELSE 2 + g % 5000 END AS tenant_id, 'body' AS body
FROM generate_series(1, 200000) g;
CREATE INDEX tickets_tenant_idx ON tickets (tenant_id);
ANALYZE tickets;
PREPARE by_tenant(int) AS SELECT max(length(body)) FROM tickets WHERE tenant_id = $1;
SET plan_cache_mode = force_custom_plan; -- re-plan with the actual parameter value each time
EXPLAIN (COSTS OFF) EXECUTE by_tenant(1);
EXPLAIN (COSTS OFF) EXECUTE by_tenant(42);
SET plan_cache_mode = force_generic_plan; -- one plan for every value, chosen without seeing $1
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) EXECUTE by_tenant(1); -- generic plan meets the whale tenant
RESET plan_cache_mode;Real output (PostgreSQL 17.11, local sandbox, psql; setup DDL echoes omitted):
SET max_parallel_workers_per_gather = 0;
ANALYZE addresses;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM addresses WHERE city = 'city_42' AND state = 'state_4';
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on addresses (cost=0.00..2140.00 rows=100 width=19) (actual rows=1000 loops=1)
Filter: ((city = 'city_42'::text) AND (state = 'state_4'::text))
Rows Removed by Filter: 99000
EXPLAIN SELECT city, state, count(*) FROM addresses GROUP BY city, state;
QUERY PLAN
------------------------------------------------------------------------
HashAggregate (cost=2390.00..2400.00 rows=1000 width=23)
Group Key: city, state
-> Seq Scan on addresses (cost=0.00..1640.00 rows=100000 width=15)
CREATE STATISTICS addr_city_state (dependencies, ndistinct) ON city, state FROM addresses;
ANALYZE addresses;
SELECT stxname, stxkind, dependencies FROM pg_stats_ext JOIN pg_statistic_ext ON stxname = statistics_name
WHERE statistics_name = 'addr_city_state';
stxname | stxkind | dependencies
-----------------+---------+----------------------
addr_city_state | {d,f} | {"2 => 3": 1.000000}
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM addresses WHERE city = 'city_42' AND state = 'state_4';
QUERY PLAN
------------------------------------------------------------------------------------------
Seq Scan on addresses (cost=0.00..2140.00 rows=980 width=19) (actual rows=1000 loops=1)
Filter: ((city = 'city_42'::text) AND (state = 'state_4'::text))
Rows Removed by Filter: 99000
EXPLAIN SELECT city, state, count(*) FROM addresses GROUP BY city, state;
QUERY PLAN
------------------------------------------------------------------------
HashAggregate (cost=2390.00..2391.00 rows=100 width=23)
Group Key: city, state
-> Seq Scan on addresses (cost=0.00..1640.00 rows=100000 width=15)
PREPARE by_tenant(int) AS SELECT max(length(body)) FROM tickets WHERE tenant_id = $1;
SET plan_cache_mode = force_custom_plan;
EXPLAIN (COSTS OFF) EXECUTE by_tenant(1);
QUERY PLAN
------------------------------------------------------
Aggregate
-> Index Scan using tickets_tenant_idx on tickets
Index Cond: (tenant_id = 1)
EXPLAIN (COSTS OFF) EXECUTE by_tenant(42);
QUERY PLAN
------------------------------------------------------
Aggregate
-> Index Scan using tickets_tenant_idx on tickets
Index Cond: (tenant_id = 42)
SET plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF) EXECUTE by_tenant(1);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=44.25..44.26 rows=1 width=4) (actual rows=1 loops=1)
-> Index Scan using tickets_tenant_idx on tickets (cost=0.29..44.04 rows=41 width=5) (actual rows=100000 loops=1)
Index Cond: (tenant_id = $1)
RESET plan_cache_mode;What happened:
- Independence assumption:
rows=100estimated vsactual rows=1000forcity = 'city_42' AND state = 'state_4', a 10x underestimate. On a single-table scan that is harmless; feed it into a join and the planner may pick a nested loop for what is really a 10x larger input. - GROUP BY estimate: without extended stats,
GROUP BY city, stateis estimated at 1,000 groups (distinct cities x distinct states, capped), while the truth is 100. CREATE STATISTICS ... (dependencies, ndistinct)plusANALYZEstores a functional dependency of degree 1.000 between the columns ("2 => 3": 1.000000means attribute 2, city, determines attribute 3, state). The same query now estimatesrows=980and the GROUP BY estimates 100 groups.
| Extended statistic kind | Fixes | Example |
|---|---|---|
dependencies | Equality predicates on functionally dependent columns | city = ? AND state = ?, zip = ? AND city = ? |
ndistinct | Group counts for GROUP BY / DISTINCT on column combinations | GROUP BY country, region |
mcv (multivariate MCV list) | Common value combinations, also non-equality and anti-correlation | plan = 'free' AND country = 'US' |
| Expression statistics (PG 14+) | Estimates for expressions | CREATE STATISTICS s ON (lower(email)) FROM users |
Failure 2: stale statistics and out-of-range values
Autovacuum runs ANALYZE after autovacuum_analyze_scale_factor (default 10%) plus a threshold of the table has changed. On a 500-million-row table that is 50 million rows of drift before stats refresh, and append-only time-series tables suffer the most: today's timestamps are above the histogram's top bound. PostgreSQL mitigates out-of-range estimates by peeking at the actual index endpoint (get_actual_variable_range), but range predicates like created_at > now() - interval '1 hour' on a freshly bulk-loaded table can still be estimated far off. Fixes:
- Run
ANALYZEexplicitly after bulk loads, partition attaches and big deletes, as part of the job, not as an afterthought. - Lower the per-table scale factor for hot, huge tables:
ALTER TABLE events SET (autovacuum_analyze_scale_factor = 0.01). - Raise the statistics target for skewed columns:
ALTER TABLE orders ALTER COLUMN tenant_id SET STATISTICS 1000(more MCVs and histogram buckets, at the cost of a slower ANALYZE and slightly slower planning). - After
pg_upgrade, statistics are not carried over (before PG 18): runvacuumdb --analyze-in-stagesimmediately, or every plan starts from defaults.
Failure 3: parameters, prepared statements and generic plans
With a prepared statement (which most drivers and ORMs use under the hood, including server-side prepares in JDBC after a few executions and in many connection poolers' protocol paths), PostgreSQL plans the first five executions with the actual parameter values (custom plans). From the sixth on, it computes a generic plan whose estimates ignore the value (they use averages like 1 / n_distinct) and switches to it permanently if its cost is not worse than the average custom plan cost (which includes planning overhead). The real output above shows the danger: for the whale tenant (100,000 of 200,000 rows) a custom plan is costed knowing it will return half the table (in this demo it still picks the index scan, because tenant 1's rows are stored contiguously); the forced generic plan estimates rows=41 without seeing the value, and its index scan actually returns 100,000 rows.
The simulation below reproduces the shape of the heuristic, including how a single expensive custom plan raises the average and makes the generic plan look attractive.
// Simulating PostgreSQL's prepared-statement plan cache choice (plan_cache_mode = auto):
// the first 5 executions get custom plans (planned with the real parameter); from the 6th on,
// a generic plan is used if its estimated cost is not worse than the average custom-plan cost
// (PostgreSQL also adds an estimated planning-cost term for custom plans; included here simply).
// Costs and latencies are example values loosely shaped like the p4 SQL demo (tenant 1 = 100k rows).
// Access method matches the real PG 17.11 demo: Index Scan for both small and whale tenants
// (tenant 1's rows are physically clustered, so the custom plan still picks the index).
type Plan = "index scan";
const rowsForTenant = (t: number): number => (t === 1 ? 100_000 : 20);
const avgRowsPerTenant = 41; // what the generic plan assumes for "$1" (n_distinct-based average)
function planFor(_rows: number): Plan { return "index scan"; }
function estCost(_plan: Plan, rows: number): number {
return 0.3 + rows * 1.1; // per-row index I/O (example)
}
function latencyMs(plan: Plan, actualRows: number): number { return +(estCost(plan, actualRows) / 50).toFixed(2); }
const PLANNING_COST = 5; // example planning overhead charged to every custom plan
let customCosts: number[] = [];
let generic: Plan | null = null;
function execute(tenant: number, call: number): string {
const actual = rowsForTenant(tenant);
if (generic === null) {
const p = planFor(actual); // custom plan sees the real value
customCosts.push(estCost(p, actual) + PLANNING_COST);
if (customCosts.length >= 5) {
const g = planFor(avgRowsPerTenant);
const avgCustom = customCosts.reduce((a, b) => a + b, 0) / customCosts.length;
if (estCost(g, avgRowsPerTenant) <= avgCustom) generic = g; // switch for all future calls
}
return `call ${call} tenant ${tenant}: CUSTOM ${p} -> ${latencyMs(p, actual)} ms`;
}
return `call ${call} tenant ${tenant}: GENERIC ${generic} -> ${latencyMs(generic, actual)} ms`;
}
const workload = [7, 9, 13, 42, 99, 8, 1, 55, 1]; // small tenants warm the cache, then the whale shows up
workload.forEach((t, i) => console.log(execute(t, i + 1)));
console.log(`whale with a custom plan would take ${latencyMs(planFor(100_000), 100_000)} ms`);
console.log("fixes: plan_cache_mode=force_custom_plan for this statement/role, split hot tenants, or bind literals for outliers");Output:
call 1 tenant 7: CUSTOM index scan -> 0.45 ms
call 2 tenant 9: CUSTOM index scan -> 0.45 ms
call 3 tenant 13: CUSTOM index scan -> 0.45 ms
call 4 tenant 42: CUSTOM index scan -> 0.45 ms
call 5 tenant 99: CUSTOM index scan -> 0.45 ms
call 6 tenant 8: CUSTOM index scan -> 0.45 ms
call 7 tenant 1: CUSTOM index scan -> 2200.01 ms
call 8 tenant 55: GENERIC index scan -> 0.45 ms
call 9 tenant 1: GENERIC index scan -> 2200.01 ms
whale with a custom plan would take 2200.01 ms
fixes: plan_cache_mode=force_custom_plan for this statement/role, split hot tenants, or bind literals for outliersExpectedcall 1 tenant 7: CUSTOM index scan -> 0.45 ms call 2 tenant 9: CUSTOM index scan -> 0.45 ms call 3 tenant 13: CUSTOM index scan -> 0.45 ms call 4 tenant 42: CUSTOM index scan -> 0.45 ms call 5 tenant 99: CUSTOM index scan -> 0.45 ms call 6 tenant 8: CUSTOM index scan -> 0.45 ms call 7 tenant 1: CUSTOM index scan -> 2200.01 ms call 8 tenant 55: GENERIC index scan -> 0.45 ms call 9 tenant 1: GENERIC index scan -> 2200.01 ms whale with a custom plan would take 2200.01 ms fixes: plan_cache_mode=force_custom_plan for this statement/role, split hot tenants, or bind literals for outliers
Press Run. Snippets must be self-contained — no network, files, or native modules.
| Fix | How | Trade-off |
|---|---|---|
plan_cache_mode = force_custom_plan | Per role, database, session, or SET LOCAL in the transaction | Re-plans every execution (planning cost on hot, simple queries) |
plan_cache_mode = force_generic_plan | Same scopes | Saves planning for uniform queries; dangerous with skew |
| Split hot keys | Route whale tenants to a different statement or table/partition | Application complexity |
| Inline literals for outliers | Driver option or code path that sends literal SQL for known-skewed values | Loses plan caching and needs injection-safe handling |
| Better stats on the skewed column | Higher statistics target, more MCVs | Helps custom plans; a generic plan still ignores the value |
SQL Server calls the mirror problem parameter sniffing (it plans with the first value it sees and caches that plan); Oracle has bind peeking and adaptive cursor sharing; MySQL re-optimizes prepared statements per execution in most cases, so it suffers less from this specific failure.
Seeing misestimates in production
- auto_explain logs the plan of any statement slower than a threshold, optionally with
ANALYZE-style actuals, so you capture the plan at the moment it was slow, not the plan you get when you rerun it later:
-- postgresql.conf (or ALTER SYSTEM / per-database SET); values are examples
-- shared_preload_libraries = 'auto_explain' -- or LOAD 'auto_explain' per session
-- auto_explain.log_min_duration = '500ms' -- log plans of statements slower than this
-- auto_explain.log_analyze = on -- include actual rows (adds timing overhead)
-- auto_explain.log_timing = off -- keep row counts, avoid per-node clock overhead
-- auto_explain.log_buffers = on
-- auto_explain.sample_rate = 0.1 -- sample 10% of statements on busy systems- pg_stat_statements aggregates execution counts, total and mean time, rows and buffer usage per normalized query; a sudden jump in
mean_exec_timefor onequeryidwith unchanged call volume is the signature of a plan change. - EXPLAIN (ANALYZE, BUFFERS) on a copy or a replica, compared with the auto_explain capture.
pg_statsfor the columns involved:n_distinct,most_common_vals,histogram_bounds,correlation, andpg_stat_user_tables.last_analyze / last_autoanalyze.
Runbook: "this query got slow overnight"
Decisions
- 1
Step 1: confirm in pg_stat_statements - mean time jumped, calls stable?
- nextStep 2: get the slow plan - auto_explain log or EXPLAIN ANALYZE on a replica
- 2
Step 2: get the slow plan - auto_explain log or EXPLAIN ANALYZE on a replica
- nextStep 3: walk bottom-up - first node where est vs actual rows diverge
- 3
Step 3: walk bottom-up - first node where est vs actual rows diverge
- nextStep 4: why is that estimate wrong?
- ?
Step 4: why is that estimate wrong?
- last_autoanalyze old, bulk load, new date rangeStep 5a: ANALYZE, tune per-table autovacuum_analyze_scale_factor
- several predicates on related columnsStep 5b: CREATE STATISTICS dependencies/mcv, ANALYZE
- prepared statement, skewed valueStep 5c: plan_cache_mode force_custom_plan for that path
- function, cast or OR the planner cannot estimateStep 5d: rewrite predicate or add expression index/statistics
- 5
Step 5a: ANALYZE, tune per-table autovacuum_analyze_scale_factor
- nextStep 6: re-run EXPLAIN ANALYZE, compare, watch pg_stat_statements
- 6
Step 5b: CREATE STATISTICS dependencies/mcv, ANALYZE
- nextStep 6: re-run EXPLAIN ANALYZE, compare, watch pg_stat_statements
- 7
Step 5c: plan_cache_mode force_custom_plan for that path
- nextStep 6: re-run EXPLAIN ANALYZE, compare, watch pg_stat_statements
- 8
Step 5d: rewrite predicate or add expression index/statistics
- nextStep 6: re-run EXPLAIN ANALYZE, compare, watch pg_stat_statements
- 9
Step 6: re-run EXPLAIN ANALYZE, compare, watch pg_stat_statements
- nextStep 7: last resort - pin with pg_hint_plan or join_collapse_limit, with a ticket to remove it
- 10
Step 7: last resort - pin with pg_hint_plan or join_collapse_limit, with a ticket to remove it
- Confirm it is a plan change, not load. If calls and rows per call are flat but mean time jumped, suspect the plan; if everything got slower, suspect the system (I/O saturation, lock waits, vacuum, a noisy neighbour).
- Capture the bad plan from auto_explain or by running
EXPLAIN (ANALYZE, BUFFERS)with the same parameters (and the same prepared-statement path: an ad hoc query with literals gets a custom plan and may look fine). - Find the first divergence from the bottom of the tree.
- Explain it: stale stats, correlation, skew plus generic plan, an unestimable expression, or a new data distribution (a tenant that grew 100x).
- Fix the estimate, not the plan, whenever possible.
- Verify with EXPLAIN ANALYZE and watch the query's
pg_stat_statementsline recover. - Pin only as a last resort, with a comment and an owner:
pg_hint_planhints such as/*+ HashJoin(o c) Leading((c o)) */, a per-functionSET join_collapse_limit = 1, or a materialized CTE (WITH x AS MATERIALIZED (...)) as an optimization fence.
What happens if you choose otherwise
- Pin plans everywhere: you trade occasional regressions for guaranteed slow decay as data distributions shift, and every upgrade becomes a hint-maintenance project.
- Only rely on autovacuum's defaults for huge append-only tables: estimates for "recent" data stay wrong for hours or days between analyzes.
- Raise default_statistics_target globally to 10,000: ANALYZE and planning slow down everywhere, and correlation problems remain because per-column stats cannot express cross-column relationships.
- Force custom plans for everything: safe for skew, but hot point-lookup statements pay planning on every call; measure planning time (
EXPLAIN (SUMMARY)shows it).
Pitfalls
- Re-running the slow query by hand with literals and getting a fast plan, then concluding "it was a blip"; the application path uses a generic plan.
- Creating extended statistics and forgetting to
ANALYZEafterwards: the statistics object is empty until then. - Expecting
dependenciesstatistics to help range predicates: they apply to equality clauses; usemcvfor more general combinations. - Running
EXPLAIN ANALYZEon writes in production withoutBEGIN ... ROLLBACK. - Leaving pg_hint_plan hints in code after the root cause is fixed; they keep constraining the planner years later.
- Forgetting that
ANALYZEon a partitioned parent is needed for estimates over the whole partitioned table (autovacuum does not analyze partitioned parents).
How the code was checked
- The correlation model ran under Python 3.13 and the plan-cache simulation under
tsc --strictand Node 22. Their output blocks are the real captured output. - The SQL script ran through psql against a local PostgreSQL 17.11 sandbox, including CREATE STATISTICS and the forced generic plan. Only the setup DDL echoes were omitted.
- Costs and latencies in the plan-cache simulation are example values; the auto_explain block is a configuration example, not output.
Interview Q&A
Why do optimizers make bad plans more often because of cardinality than cost-model errors?
Answer
Cost models are roughly right about relative costs; row estimates can be off by orders of magnitude due to correlated predicates, skew and stale stats, and those errors multiply through joins. A plan picked on a 1,000x underestimate is wrong no matter how good the cost formulas are.
What is the independence assumption and how do you fix it in PostgreSQL?
Answer
For a = x AND b = y the planner multiplies the two selectivities as if the columns were independent. For correlated columns that underestimates. CREATE STATISTICS ... (dependencies, ndistinct, mcv) ON a, b FROM t followed by ANALYZE lets the planner use functional dependency degrees, combined distinct counts and multivariate MCVs.
Explain custom vs generic plans and why a prepared statement can suddenly get slow.
Answer
The first five executions are planned with the real parameter values. After that PostgreSQL may switch to a generic plan that ignores the value, if its estimated cost is not worse than the average custom cost. On skewed data the generic plan is tuned for the average value and terrible for outliers. Fix with plan_cache_mode = force_custom_plan on that path, or by treating outliers separately.
How would you debug a query that got slow overnight with no deploy?
Answer
Confirm via pg_stat_statements that it is one query's plan, capture the slow plan with auto_explain or EXPLAIN ANALYZE using the same execution path, find the lowest node where estimated and actual rows diverge, determine why (stale stats, correlation, generic plan, new data shape), fix the estimate (ANALYZE, extended stats, plan_cache_mode, rewrite), verify, and only then consider pinning.
When is plan pinning acceptable?
Answer
As a temporary mitigation for a critical query while the root cause is fixed, or for a query whose data shape is known to be stable. Always documented with an owner, because a pinned plan cannot adapt to growth.
What does auto_explain give you that EXPLAIN does not?
Answer
The plan actually used at the time of the slow execution, including actual rows if log_analyze is on, for statements over a duration threshold. Re-running EXPLAIN later can produce a different plan because of different parameters, statistics or caching.
Why did the GROUP BY estimate drop to 100 after CREATE STATISTICS?
Answer
The ndistinct statistic records the real number of city and state combinations, so the planner no longer multiplies the distinct counts of each column.
How do SQL Server and MySQL compare on parameter-sensitive plans?
Answer
SQL Server plans with the first value it sees and caches that plan (parameter sniffing). MySQL re-optimizes prepared statements per execution in most cases, so it suffers less from this failure.
Check yourself
On a test copy, find two columns you suspect are correlated. Compare EXPLAIN ANALYZE estimates for a combined equality predicate before and after CREATE STATISTICS (dependencies) and ANALYZE.
Elsewhere in the library
These pages stay as they are. This lesson only points at them: Selectivity, Cardinality & Statistics, EXPLAIN & EXPLAIN ANALYZE, MVCC, Snapshot Isolation & Write Skew, Performance Engineering — Profiling, Flame Graphs, Latency Budgets & Hot Paths.
Go Deeper
- Leis et al.: How Good Are Query Optimizers, Really? (VLDB 2015)
- PostgreSQL docs: Statistics Used by the Planner (including extended statistics)
- PostgreSQL docs: CREATE STATISTICS
- PostgreSQL docs: Row Estimation Examples
- PostgreSQL docs: How the Planner Uses Statistics (details)
- PostgreSQL docs: PREPARE and plan_cache_mode
- PostgreSQL docs: auto_explain
- PostgreSQL docs: pg_stat_statements
- pg_hint_plan on GitHub
- CMU Database Group on YouTube (query optimization lectures)