Control CTE materialization fences
A WITH block can be an optimization fence that spills to disk before your WHERE clause runs. Choosing NOT MATERIALIZED lets the filter push down, 66 ms and a 19 MB disk spill become 0.3 ms.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Before PostgreSQL 12 every CTE was materialized (computed once, then scanned). Even now a CTE referenced once can be materialized in ways that block predicate push-down, so a filter you wrote runs after the whole CTE is built and spilled to disk.
A Meridian ledger query wraps a scan in a CTE and filters the result by account_code. Materialized, the CTE builds all 400,000 rows, spills 19 MB to disk, then throws almost all of them away.
Simple terms
A WITH block (a CTE) can behave like a wall the planner will not reach through. When that happens, PostgreSQL builds the entire CTE first, every row, spilled to disk if it is large, and only then applies the WHERE clause you wrote outside it, throwing almost all of that work away. Marking the CTE NOT MATERIALIZED lets the filter slide inside, so the database only ever produces the rows you actually keep.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Query performance runbooks
Neighbouring incidents that share the same diagnostic surface.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.