Estimator blow-up and planning-time tax on a partitioned table
An analytics API runs one query against events: 2.4 billion rows, range-partitioned by month into 36 partitions, with B-tree indexes on tenant_id and created_at plus a GIN index on a payload jsonb column. The query is issued from a prepared statement and the server switches to a generic plan after five executions. Two symptoms. First, for a three-predicate filter the planner estimates 180 rows but the actual count is 1.9 million, and the chosen nested loop takes 41 minutes against a predicted 12 seconds. The three filter columns are tenant_id (very high cardinality), region (12 values) and event_type (9 values); in the real data region and event_type are strongly correlated, and some combinations never occur. Second, a simple point lookup of one tenant's most recent row costs 38 ms of planning time and 2 ms of execution, because the planner builds index paths for all 36 partitions. default_statistics_target = 100 and plan_cache_mode = auto.
1. Explain the 180-row versus 1.9-million-row misestimate and the concrete fix.
Read the worked answer
A single-column estimate multiplies selectivities as if the columns were independent: roughly 1/12 × 1/9 gives about 1% of the table, and the per-column histograms then apply that to the tenant_id restriction, landing near 180. Reality is that region and event_type move together, and some pairs are absent, so per-column statistics cannot express the joint distribution. The planner has no reason to distrust its own number, so a nested loop looks cheap on the inner side.
The fix is extended statistics, which are multivariate objects built by ANALYZE:
CREATE STATISTICS events_region_type (dependencies, ndistinct, mcv)
ON region, event_type, tenant_id FROM events;
ANALYZE events;
dependencies captures the functional-ish relationship, ndistinct corrects the row count for the grouping combination, and mcv (with a list of columns) covers the case of non-uniform joint values such as missing pairs. Then confirm the change on the real query with EXPLAIN (ANALYZE, BUFFERS) rather than assuming: the rows estimate should move into the same order of magnitude as the actual, and the join method should change without you forcing it.
Also inspect pg_stats for tenant_id - a very high-cardinality column often needs a higher n_distinct/statistics target than 100 before the joint statistics can help, and check whether ANALYZE is actually keeping up on the newest partitions.
2. Explain the 38 ms planning time and how to remove it without breaking other queries.
Read the worked answer
Planning cost is roughly proportional to the number of partitions the planner must consider. With a generic plan, the partition key comparison is against a parameter, not a literal, so no partitions can be eliminated at plan time - the planner builds an Append over all 36 partitions and their indexes. That fixed 38 ms is paid once per generic plan, but if the plan is replanned per execution (custom plans, or after invalidation) it is paid on the query path. A 2 ms lookup behind a 38 ms plan is dominated by planning.
Options, in order of preference:
SET plan_cache_mode = force_custom_planfor the API's role. Custom plans substitute the actual parameter values, so plan-time pruning reduces 36 partitions to 1 and planning collapses to a few milliseconds. The trade-off is re-planning for genuinely high-frequency statements - measure withpg_stat_statementsbefore rolling out globally.- Reduce the partition count, or use a composite strategy: sub-partition by hash on
tenant_idso a tenant-scoped lookup touches a single partition regardless of planning mode. Fewer partitions is the only fix that helps both planning and run-time pruning. - Make sure the API actually passes the partition key in the predicate; without it, neither plan-time nor run-time pruning applies and the plan is unavoidably an all-partition append.
- Consider a covering index for the common projection so each partition's index path is cheaper to build and to run.
Whatever you choose, verify with EXPLAIN (ANALYZE, BUFFERS) on a replica and watch the pg_stat_statements mean plan time for the affected statements.
3. How do you validate both changes in production without destabilising other workloads?
Read the worked answer
Treat the two changes as separate experiments with separate blast radii.
For the statistics change: build the extended statistics and ANALYZE on the primary during a low-write period (ANALYZE on a 2.4 billion row partitioned table reads samples from every partition, so it is I/O, not lock, pressure). Before and after, capture EXPLAIN (ANALYZE, BUFFERS) for the ten highest-total-time statements from pg_stat_statements, not just the problem query - better estimates can flip other plans to nested loops if their indexes are missing. Keep a rollback ready: DROP STATISTICS restores the prior behaviour immediately.
For the planning-time change: plan_cache_mode is settable per role or per session, so scope it to the API's service role rather than the whole cluster. Compare mean planning and execution time in pg_stat_statements for both the point lookups and the heavy analytics statements - forcing custom plans is the right call for tens of thousands of short lookups and the wrong call for a statement executed 200 times a second with a 40 ms planning cost.
Guardrails: run the checks against a replica first, keep auto_explain sampling on for the first few hours to catch silent regressions on statements nobody was watching, and alert on the 95th percentile of the API's query latency rather than on averages, because the failure mode here is a small subset of queries getting catastrophically slower.