PostgreSQL CTE performance checklist

The symptoms

• •Query latency increases after refactoring a subquery into a WITH clause, even though row counts and join shape appear unchanged in application logs.
• •EXPLAIN shows the CTE evaluated once as a separate node whose row estimate is far above the rows actually consumed downstream, indicating materialization rather than inlining.
• •A recursive WITH (WITH RECURSIVE) consumes large temp space or spills to disk, visible in pg_stat_database or in the work_mem-related fields of EXPLAIN ANALYZE buffers output.
• •Predicate filters applied outside the CTE do not reduce the rows inside it, suggesting the planner treated the CTE as an optimization fence.
• •Removing or rewriting the CTE restores prior performance, but the team is unsure which CTE property caused the regression and whether the fix is durable.

Likely causes

• •The CTE is being materialized because it is non-recursive, referenced more than once, or contains a side-effecting or volatile function, and MATERIALIZED semantics are the PostgreSQL default.
• •A reference to a non-cte-safe function inside the CTE forces materialization and blocks predicate pushdown into the inner query.
• •Recursive CTEs iterate more times than expected because the termination condition depends on a column that does not monotonically approach the base case.
• •The CTE replaces what was previously a correlated subquery, removing the planner's ability to push a join key or filter down into the inner scan.
• •Statistics on tables referenced inside the CTE are stale, so the planner underestimates or overestimates rows and selects a nested-loop or hash join plan that does not match production cardinality.
• •The CTE returns more columns than the downstream query needs, so the executor computes a wider tuple than necessary and inflates buffer traffic.

First ten minutes

1. 01Capture the exact query text and identify every WITH clause; note whether each CTE is referenced once or multiple times, and whether any CTE is recursive.
2. 02Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the current query and on the previous form without the CTE, against a representative dataset, and save both outputs for comparison.
3. 03Inspect the EXPLAIN tree for a Materialize node attached to the CTE subtree; if present, the planner has chosen to compute and store the full result before the outer query reads it.
4. 04Check pg_stat_user_tables for the tables inside the CTE; compare n_live_tup, last_analyze, last_autoanalyze against the date of the deployment to determine whether stale statistics are plausible.
5. 05Read the Buffers block for the CTE subtree; note shared hit, shared read, shared written, and any temp read or written values, which together indicate whether materialization is the dominant cost.
6. 06Compare actual rows against planned rows at the CTE boundary and at each downstream consumer; large gaps indicate estimate errors that drive materialization decisions.

Evidence to collect

• •EXPLAIN (ANALYZE, BUFFERS, VERBOSE) output for the CTE form and the non-CTE form, with identical bind parameters.
• •pg_stat_user_tables entries for every relation referenced inside each WITH clause, including last_analyze and last_autoanalyze timestamps.
• •EXPLAIN (ANALYZE, BUFFERS) for the CTE on its own, scoped to just the WITH body, to isolate whether the cost is inside the CTE or in the join with the outer query.
• •List of functions used inside the CTE and their volatility classification from pg_proc.provolatile.
• •Row counts at each EXPLAIN node, paired with planned rows, to localize where estimates diverge from reality.
• •Server-side log lines for the query around the regression window, filtered by application_name or user, capturing duration, rows, and buffer counts if log_min_duration_statement and related settings are enabled.

Where to look

• •The CTE subtree in EXPLAIN, specifically the Materialize node and its inner plan, since this is where the unexpected work is concentrated.
• •pg_stat_user_tables for the relations referenced inside each WITH clause, where stale analyze timestamps correlate with bad row estimates.
• •pg_proc.provolatile for any function called inside the CTE, since volatile functions are the canonical reason a CTE cannot be safely inlined.
• •The Buffers block of EXPLAIN ANALYZE, where shared read versus shared hit indicates whether cost is I/O or compute, and temp read or temp written indicates spilling.
• •Server log fields that record statement duration, rows returned, and buffer usage, to confirm that the regression observed in EXPLAIN reproduces under the application's bind parameters.
• •The pg_stat_statements view if available, to compare the same logical query before and after the CTE refactor by calls, total_exec_time, and mean_exec_time.

Diagnostic steps

1. 01Locate each WITH clause in the query text and classify it as single-use, multi-use, or recursive; multi-use and recursive CTEs are the most likely materialization candidates.
2. 02Run EXPLAIN (ANALYZE, BUFFERS, VERBOSE) and confirm whether the CTE subtree contains a Materialize node, distinguishing it from an inlined subquery plan which would appear as plain scan and join nodes.
3. 03Compare row estimates versus actual rows at the Materialize boundary; a divergence larger than roughly an order of magnitude suggests the planner's decision to materialize is based on a stale or missing statistic.
4. 04Inspect pg_proc.provolatile for every function referenced inside the CTE; a volatility of 'v' or 's' is sufficient to prevent the planner from treating the inner query as a transparent subquery.
5. 05Check whether the outer query applies filters on columns produced by the CTE; if those filters cannot be pushed inside, the materialized row set is necessarily larger than the rows actually consumed.
6. 06For recursive CTEs, verify the recursive term moves strictly toward the termination condition; if not, the iteration count can grow multiplicatively with input size.
7. 07Re-run ANALYZE on the relations inside the CTE and re-execute EXPLAIN to determine whether the regression was driven by estimate errors rather than by CTE semantics.

Common mistakes

• •Assuming the planner will inline every CTE; PostgreSQL materializes by default for CTEs that are non-recursive or referenced more than once, and does not push predicates into the inner query in that case.
• •Treating the regression as a missing index and adding an index that the Materialize node never reaches because it operates on the materialized tuple stream rather than the base table.
• •Rewriting the CTE as a subquery without checking whether the original CTE contained a non-cte-safe function; the inline rewrite can change function-evaluation cardinality in subtle ways.
• •Running ANALYZE once and declaring victory, when the real driver was an unbounded recursive term; statistics changes will not bound recursion depth.
• •Comparing plan shape alone and ignoring Buffers, so a plan that looks cheaper on paper is actually saturating shared_buffers and producing extra read traffic.

Safe fixes

• •If the CTE is referenced exactly once and contains no non-cte-safe function, replace WITH cte AS (CTE_MATERIALIZED ...) with a regular subquery in the FROM clause; verify with EXPLAIN that no Materialize node remains.
• •If the CTE is referenced once but cannot be safely inlined because of a volatile function, move the volatile call to the outer query so the CTE inner relation remains a transparent subquery, and re-run EXPLAIN to confirm predicate pushdown.
• •If the CTE is referenced more than once and is expensive to materialize, leave it as WITH but restrict its columns to exactly what each consumer needs, then re-measure the Buffers block to confirm a reduction in shared hit and read traffic.
• •For recursive CTEs, add a depth or iteration bound in the recursive term and re-run EXPLAIN ANALYZE to confirm the iteration count is bounded and stable under varying input sizes.
• •Run ANALYZE on every relation referenced inside the CTE when pg_stat_user_tables shows stale or missing statistics, and re-run EXPLAIN to compare planned versus actual rows at the CTE boundary.
• •If predicates from the outer query need to filter the CTE, push those predicates into the CTE body itself rather than relying on the planner to push them through a materialization boundary.

Prove the fix

1. 01EXPLAIN (ANALYZE, BUFFERS, VERBOSE) on the rewritten query shows no Materialize node attached to the former CTE subtree, or shows Materialize with materially fewer rows and lower shared read traffic than before.
2. 02The actual rows at the CTE boundary now match the planned rows within a small tolerance, indicating that statistics-driven plan choices are aligned with reality.
3. 03Shared hit plus shared read in the Buffers block for the CTE subtree decreases compared to the pre-fix EXPLAIN, and temp read or temp written drops to zero if spilling was the dominant cost.
4. 04pg_stat_statements, when available, reports a lower mean_exec_time and total_exec_time for the same normalized query text over a comparable window of calls.
5. 05Application-level latency for the affected endpoint returns to within the pre-regression baseline over a sample of representative calls, with no change to row counts returned.
6. 06If the CTE is recursive, EXPLAIN ANALYZE shows a bounded loop count that does not grow with input size, and the Buffers block shows no temp read or temp written under the expected workload.

Prevention and next steps

• •Treat every CTE introduction as a performance change, not just a readability change, and capture EXPLAIN (ANALYZE, BUFFERS) for both the WITH form and a subquery-equivalent form during code review.
• •Keep CTEs narrow in column count so materialization does not carry unused payload through the executor, and so the Buffers block accurately reflects work the consumer actually uses.
• •Avoid non-cte-safe functions inside CTEs unless materialization is intentional, and document the rationale when it is, so the next reviewer knows the fence is deliberate.
• •Schedule ANALYZE on tables that participate in CTE bodies after bulk loads or large updates, since CTE materialization decisions depend heavily on accurate row estimates.
• •For recursive CTEs, encode an explicit depth or iteration guard in the recursive term and add a comment describing the termination invariant, so future edits cannot silently make recursion unbounded.

Safe commands and checks

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <cte_query>;
SELECT schemaname, relname, n_live_tup, last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname IN (<relations_inside_cte>);
SELECT proname, provolatile FROM pg_proc WHERE proname IN (<functions_inside_cte>);
SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements WHERE query ILIKE '%<cte_signature>%' ORDER BY total_exec_time DESC LIMIT 10;
ANALYZE <relation_inside_cte>;