How to verify a PostgreSQL CTE plan after a performance fix

The symptoms

• •EXPLAIN output for the CTE shows a node that was not part of the agreed plan (for example, a Seq Scan replacing an Index Scan, or an unexpected Nested Loop).
• •The CTE body executes more than once despite appearing once in the text of the query, visible as repeated scan nodes inside the plan tree.
• •A materialize or hash helper node appears or disappears between the baseline and the post-fix plan, suggesting the planner reclassified the CTE.
• •Per-call latency improved but total planner time, as reported by pg_stat_statements, increased, hinting that a different plan shape was selected.
• •Row estimates at the boundary between the CTE and the outer query changed by more than an order of magnitude between the baseline and the candidate fix.

Likely causes

• •The planner inlined a non-CTEMaterialized CTE because the SQL text did not include WITH ... AS MATERIALIZED, changing access methods.
• •Statistics used by the planner drifted (pg_statistic, pg_class.reltuples) so the chosen path switched between Index Scan and Seq Scan even though the query text was unchanged.
• •A dependent object such as an index was dropped or marked invalid, removing the access path the original plan relied on.
• •A parameter-sensitive predicate, a session-level setting (work_mem, random_page_cost, enable_*), or a plan_guide hint changed, silently swapping node types.
• •The fix introduced a new join order or filter that pushes past the CTE boundary, so the outer query now reads from the CTE under different selectivity assumptions.

First ten minutes

1. 01Capture the candidate fix's plan text with EXPLAIN (FORMAT TEXT, ANALYZE, BUFFERS) and store it next to the baseline plan; do not rely on memory.
2. 02Confirm the session settings used for both runs match: current_setting() for random_page_cost, work_mem, enable_seqscan, enable_nestloop, and jit, since a different plan under different settings is not a stable verification.
3. 03Inspect the top-level node and the boundary node where the CTE feeds the outer query; record the scan method on each, the join method used, and the row estimate produced at the boundary.
4. 04Cross-check execution counts: count how many times the CTE body executes (look for repeated scan nodes or a single shared node) and record total time and buffer hits versus reads.
5. 05Read pg_stat_statements for the query's queryid and confirm the planned plan, total planning and execution time, and rows are consistent with the EXPLAIN run.

Evidence to collect

• •Two EXPLAIN outputs, baseline and post-fix, captured with ANALYZE and BUFFERS so timing and I/O are part of the record.
• •Snapshot of session GUCs from current_setting() for random_page_cost, work_mem, enable_seqscan, enable_nestloop, enable_hashjoin, enable_mergejoin, jit, and plan_cache_mode.
• •Row counts at the CTE boundary (rows produced by the CTE result node) for both runs, plus the planner's estimate at that node.
• •pg_stat_statements row for the query: queryid, plans, total_plan_time, total_exec_time, calls, and rows.
• •Object state from pg_class, pg_index, and pg_stats for tables and indexes touched by the CTE, including indisvalid and reltuples.

Where to look

• •pg_stat_statements view: row matching the query's queryid, columns plans, total_plan_time, total_exec_time, calls, rows.
• •pg_class and pg_index for each table and index in the plan: reltuples, relpages, indisvalid, indisready.
• •pg_stats for column-level statistics on predicates feeding the CTE and on join keys crossing the CTE boundary.
• •EXPLAIN output node labels: CTE Scan, CTE Materialize, InitPlan, SubPlan, Result, and the scan/join node types beneath them.
• •Session catalog from current_setting() to ensure plan-affecting GUCs are identical between baseline and post-fix runs.

Diagnostic steps

1. 01Diff the two plan trees node-by-node; a node-type change (Seq Scan to Index Scan or vice versa, Hash Join to Nested Loop) is the primary signal that the plan drifted from the intended behavior.
2. 02Compare row estimates at the CTE boundary: if the post-fix estimate differs by more than a factor of 10 from the baseline, treat the change as a separate investigation, not as a verification result.
3. 03Confirm the CTE materialization decision matches intent: a CTE introduced as an optimization should be referenced once in the plan; if it appears duplicated under the same parent, the planner inlined it.
4. 04Check pg_class.reltuples and pg_stats for tables that drove the original choice; stale reltuples after a bulk load are a common cause of a plan silently switching from Index Scan to Seq Scan.
5. 05Read pg_stat_statements.plans for the queryid; if plans is greater than 1, the planner produced multiple plan shapes, which means verification must target the most recent shape, not just any shape.
6. 06Confirm pg_index.indisvalid is true for every index used by the candidate plan; an invalid index will be skipped and silently replaced by a Seq Scan.

Common mistakes

• •Verifying latency only; a faster query that uses a Seq Scan instead of the intended Index Scan satisfies timing but violates the scan-behavior contract.
• •Comparing EXPLAIN without ANALYZE; estimated plans hide actual node counts and buffer usage, so they cannot confirm scan and join behavior under load.
• •Running the post-fix EXPLAIN with different session GUCs than the baseline; this compares two unrelated plans and is not a verification of the fix.
• •Trusting a single execution; PostgreSQL may pick different plans across calls, so a single EXPLAIN cannot guarantee the fix holds for all invocations.
• •Ignoring the CTE boundary row estimate; a plan can keep the right scan type but still mis-estimate selectivity and regress under realistic data skew.

Safe fixes

• •If the planner inlined the CTE against intent, re-run the verification with WITH ... AS MATERIALIZED explicitly stated so the materialization decision is explicit rather than inferred.
• •If statistics drifted, run ANALYZE on the tables feeding the CTE, then re-capture EXPLAIN (ANALYZE, BUFFERS) and re-check the boundary node; do not change the query as a substitute for fresh statistics.
• •If an index used by the baseline plan is marked invalid in pg_index, treat the index as out of scope; verify the plan only against the indexes the system actually considers usable.
• •If session GUCs differ, persist the baseline GUCs (using a documented configuration, not ad-hoc SET LOCAL inside the same session as the post-fix run) and re-capture both plans under the same settings.
• •If pg_stat_statements shows plans greater than 1, capture multiple EXPLAIN runs and treat the verification as a property of the most recent plan shape, recording the conditions under which it was produced.

Prove the fix

1. 01EXPLAIN (ANALYZE, BUFFERS) of the candidate query returns a plan tree whose node types at every position match the baseline plan: same scan methods on each table, same join method at each join, and the same number of executions of the CTE body.
2. 02The row estimate at the CTE boundary (the row produced by the CTE Scan or CTE Materialize node) is within a documented tolerance of the baseline estimate, recorded explicitly in the verification record.
3. 03pg_stat_statements for the query's queryid shows total_exec_time consistent with the EXPLAIN run, with no spike in total_plan_time that would indicate plan instability.
4. 04Session GUCs current_setting() for plan-affecting parameters are identical between baseline and post-fix captures; the verification record lists each parameter and its value for both runs.
5. 05Repeated EXPLAIN runs (at least three back-to-back invocations) return the same top-level plan shape; if the shape changes between calls, the fix is not yet stable and verification cannot be declared complete.

Prevention and next steps

• •When changing a CTE-bearing query, always capture EXPLAIN (ANALYZE, BUFFERS) on the exact production SQL text before and after the change, and store both outputs in version control next to the migration.
• •Keep pg_stat_statements enabled and review plans and total_plan_time for queries whose plans column shows more than one shape, since plan instability is the leading indicator of a regression that EXPLAIN alone may miss.
• •Run ANALYZE on CTE-referenced tables after bulk loads or large deletes so that pg_class.reltuples and pg_stats reflect current row distributions before the next verification cycle.
• •Record session GUCs used during verification; if random_page_cost, work_mem, or enable_* differ between environments, treat plan diffs as environmental rather than as evidence about the query.

Safe commands and checks

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query_text>
SELECT current_setting('random_page_cost'), current_setting('work_mem'), current_setting('enable_seqscan'), current_setting('enable_nestloop'), current_setting('enable_hashjoin'), current_setting('enable_mergejoin'), current_setting('jit');
SELECT queryid, query, plans, calls, total_exec_time, total_plan_time, rows FROM pg_stat_statements WHERE queryid = <queryid>;
SELECT n.nspname, c.relname, c.reltuples::bigint, c.relpages FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname IN (<table_a>, <table_b>);
SELECT indexrelid::regclass, indisvalid, indisready FROM pg_index WHERE indrelid = '<schema>.<table>'::regclass;
SELECT attname, n_distinct, most_common_vals, histogram_bounds FROM pg_stats WHERE tablename = '<table>' AND attname IN (<column_a>, <column_b>);
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
ALTER TABLE <schema>.<table> SET (fillfactor = <percent>);