PostgreSQL CTE plan regression: detect when materialization changed the path

The symptoms

• •EXPLAIN (ANALYZE, BUFFERS) shows a new "CTE Scan" or "Subquery Scan" node where the prior plan inlined the CTE directly into the outer query.
• •Execution time and shared/local buffer counts grow sharply while row counts stay similar, indicating the CTE is being re-evaluated per outer row rather than computed once.
• •Row estimates on the CTE-derived node deviate from actual rows by orders of magnitude, suggesting the planner chose materialization with stale statistics on the boundary.
• •Plan flips after a PostgreSQL minor upgrade, extension version change, or ANALYZE that alters pg_statistic for tables referenced inside the CTE.
• •The same query text produces different plans on a replica versus the primary, often correlated with read-only settings or differing work_mem affecting inlining thresholds.
• •Application latency increases on queries that touch a previously fast path, with no schema or data change in the underlying tables.

Likely causes

• •Planner switch from CTE inlining to materialization across PostgreSQL versions, where the optimizer becomes more conservative about folding a non-simple CTE into the outer query.
• •Self-referential or multi-referenced CTE that the planner must materialize, but where the cost estimate of that materialization is wrong because of skewed statistics on the inner relation.
• •Use of data-modifying CTEs (INSERT/UPDATE/DELETE ... RETURNING) or WITH ... [NOT] MATERIALIZED hints that force evaluation mode and are inherited by later versions that interpret them differently.
• •Changes to cte_inline_threshold, work_mem, or plan_cache_mode that shift the cost balance between inlining and materialization on a stable query.
• •Stats drift: stale pg_statistic after bulk loads, partition pruning changes, or new indexes that alter the planner's chosen path inside the CTE subtree.
• •Side effects of plan regression on a dependency: a view or function that wraps a CTE inherits a now-materialized node, and consumers further down the call chain feel the latency.

First ten minutes

1. 01Capture the exact SQL text and bind values from the failing path; pin the database server version with `SHOW server_version;` and note the planner settings `SHOW cte_inline_threshold;`, `SHOW work_mem;`, `SHOW plan_cache_mode;`.
2. 02Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the failing query in a session with the same role and search_path as production, and save the full plan text to a versioned file.
3. 03Run EXPLAIN (ANALYZE, BUFFERS) on the same query text against a known-good reference (an older dump, a read replica, or a prior plan captured in auto_explain) and diff the node tree.
4. 04Check pg_stat_user_tables and pg_stats for the relations referenced inside the CTE; record last_analyze and most_common_vals frequency rows that influence the boundary cost.
5. 05Read pg_stat_statements for the queryid of the affected statement; compare mean_exec_time, calls, and shared_blks_hit/read across the regression window.
6. 06Confirm whether the CTE is referenced more than once in the outer query, uses a data-modifying statement, or contains volatile functions—each of which forces materialization.

Evidence to collect

• •EXPLAIN (ANALYZE, BUFFERS, VERBOSE) output before and after the regression, stored as diffable text with planner settings annotated.
• •pg_stat_statements snapshot showing the queryid, mean_exec_time, calls, rows, and buffer counts across the regression window.
• •pg_stat_user_tables.n_mod_since_analyze and pg_stats correlation entries for the relations inside the CTE boundary.
• •Server configuration snapshot: server_version_num, cte_inline_threshold, work_mem, plan_cache_mode, jit settings.
• •auto_explain log lines for the failing query, if enabled, with nested statement timing and buffer usage per node.
• •List of CTE references in the outer query (single vs. multiple), presence of data-modifying statements, and use of MATERIALIZED/NOT MATERIALIZED hints.

Where to look

• •PostgreSQL planner boundary between the outer query and the CTE subtree, visible in EXPLAIN VERBOSE as a "CTE Scan" node versus inlined child nodes.
• •pg_stat_statements view for the queryid, where mean_exec_time and shared buffer deltas expose the materialization cost.
• •pg_stats and pg_class for the inner relations: staleness, n_distinct, and most_common_freqs drive the cost estimate at the boundary.
• •postgresql.conf and the active configuration source (ALTER SYSTEM, ALTER DATABASE, ALTER ROLE) for cte_inline_threshold and plan_cache_mode.
• •Application log correlation: trace the failing query's bind values back to a code path that may have changed, even when SQL text is identical.
• •Migration and deployment logs for the regression window to correlate plan flips with version upgrades, extension changes, or bulk loads.

Diagnostic steps

1. 01Diff the two EXPLAIN (ANALYZE, BUFFERS) outputs and identify the first node where the plan shape diverges; mark whether the boundary is now a CTE Scan or remains inlined.
2. 02Compute rows x width and actual time deltas at the boundary node; if actual time is much higher than estimated and loops > 1, suspect per-outer-row re-evaluation rather than one-shot materialization.
3. 03Count CTE references in the outer query: a single reference with a simple SELECT is a candidate for inlining; multiple references or data-modifying CTEs force materialization and rule out inlining fixes.
4. 04Recompute statistics with ANALYZE on the inner relations and re-run EXPLAIN; if the plan returns to the inlined form, attribute the regression to stats drift, not version semantics.
5. 05Toggle the MATERIALIZED / NOT MATERIALIZED hint in a test session and re-EXPLAIN; the node tree should change in the opposite direction, confirming the boundary is the optimizer's choice.
6. 06Cross-check the official monitoring statistics documentation to confirm the meaning of pg_stat_statements columns and the buffer accounting model before drawing conclusions.
7. 07Reproduce on a restored backup or staging clone with production-like data volume; small datasets can mask the cost difference and lead to a false negative on the boundary.

Common mistakes

• •Assuming identical SQL text guarantees an identical plan; planner settings, statistics, and version semantics all sit between the text and the node tree.
• •Comparing only execution time and ignoring buffer counts, which is where materialization costs (extra reads, hashing) show up before wall-clock time diverges.
• •Forcing MATERIALIZED on a single-reference, side-effect-free CTE and accepting the per-outer-row cost as unavoidable, when inlining would have avoided the materialization altogether.
• •Running ANALYZE and declaring victory without re-checking EXPLAIN, because stats drift is one cause among several and the plan may still be wrong.
• •Trusting a plan captured on a small dev dataset and shipping the fix to production, where the boundary cost scales with row count and the regression returns.

Safe fixes

• •Rewrite the query so the CTE is referenced only once and contains no side effects, allowing the planner to inline it; verify with EXPLAIN VERBOSE that the CTE Scan node disappears.
• •Apply NOT MATERIALIZED explicitly in a test session when the CTE must remain a named boundary but inlining is desired; confirm the plan shape before promoting.
• •Run ANALYZE on the inner relations when pg_stat_user_tables.n_mod_since_analyze is high, then re-EXPLAIN to test whether correct statistics alone restore the plan.
• •Tune cte_inline_threshold upward in a test session when the planner refuses to inline a borderline CTE; re-EXPLAIN to confirm inlining, then consider the change in a controlled rollout.
• •Restructure multi-reference CTEs into temporary tables or derived tables when the cost of materialization exceeds the cost of recomputation for a single reference path.
• •Add an index or partial index that matches the CTE's filter, lowering the inner cost estimate so the planner prefers inlining at the boundary.

Prove the fix

1. 01EXPLAIN (ANALYZE, BUFFERS, VERBOSE) shows the CTE boundary either inlined or explicitly NOT MATERIALIZED, and the estimated vs. actual row counts at that node agree within one order of magnitude.
2. 02pg_stat_statements for the same queryid reports mean_exec_time returning to the pre-regression baseline over a statistically meaningful number of calls.
3. 03Shared and local buffer counts per execution drop to within tolerance of the known-good plan, confirming the materialization cost is removed or bounded.
4. 04Running EXPLAIN across at least three sessions with cleared caches and varied bind values produces the same plan shape, ruling out plan_cache_mode or plan instability.
5. 05A canary run on a staging clone with production-scale data shows the plan shape and timing match the pre-regression baseline before the change is promoted.
6. 06Auto_explain logs, if enabled, show the same node ordering and per-node timing as the reference plan for the regression window's representative traffic.

Prevention and next steps

• •Capture EXPLAIN (ANALYZE, BUFFERS) baselines for known-critical queries in version control, and diff them whenever PostgreSQL is upgraded or planner settings change.
• •Monitor pg_stat_statements for mean_exec_time drift on tagged queries and alert on plan-cache miss or buffer-count spikes that precede wall-clock regressions.
• •Schedule ANALYZE after bulk loads or partition operations, and track n_mod_since_analyze to bound the window in which stats drift can flip a CTE boundary.
• •Document the rationale for MATERIALIZED / NOT MATERIALIZED hints in code review so future changes do not silently flip planner behavior across versions.
• •Test plan stability on production-scale datasets in staging, not on small dev clones, so the cost of inlining versus materialization is evaluated under realistic row counts.

Safe commands and checks

SHOW server_version; SHOW server_version_num;
SHOW cte_inline_threshold; SHOW plan_cache_mode; SHOW work_mem; SHOW jit;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query_text>;
SELECT queryid, calls, mean_exec_time, rows, shared_blks_hit, shared_blks_read, temp_blks_read FROM pg_stat_statements WHERE query ILIKE '%<cte_marker>%' ORDER BY mean_exec_time DESC LIMIT 20;
SELECT relname, n_live_tup, n_mod_since_analyze, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN (<inner_relations>);
SELECT attname, n_distinct, most_common_vals, most_common_freqs FROM pg_stats WHERE tablename = '<inner_relation>' AND attname IN (<filter_columns>);
SELECT pg_get_viewdef('<view_oid>'); -- to inspect CTEs nested inside views or functions
ANALYZE VERBOSE <inner_relation>; -- scoped to specific relations referenced inside the CTE