PostgreSQL CTEs: WITH queries and materialization

A PostgreSQL common table expression, or CTE, gives a name to a query result within one SQL statement. Define it with WITH, then refer to that name in the main query or a later CTE. It can make a multi-step query easier to inspect, but it does not automatically create a stored table or improve performance.

We ran 31 checks on an isolated PostgreSQL 18.6 server using the examples below. They cover chained queries, statement scope, five execution plans, exact result rows, and bounded recursion. The plans used default planner settings. These local observations are not a production latency benchmark.

Build a query in named stages

Use a disposable database where the schema name cte_lab is unused. This setup creates five orders. Customer A has two paid orders totaling 120, B has two totaling 50, and C has a pending order for 900:

CREATE SCHEMA cte_lab;
CREATE TABLE cte_lab.orders (
    id integer PRIMARY KEY,
    customer text NOT NULL,
    status text NOT NULL,
    amount integer NOT NULL
);
INSERT INTO cte_lab.orders VALUES
(1, 'A', 'paid', 70), (2, 'A', 'paid', 50),
(3, 'B', 'paid', 40), (4, 'B', 'paid', 10),
(5, 'C', 'pending', 900);

Now separate the selection of paid orders from the calculation of customer totals:

WITH paid AS (
    SELECT customer, amount
    FROM cte_lab.orders
    WHERE status = 'paid'
), totals AS (
    SELECT customer, sum(amount) AS total, count(*) AS orders
    FROM paid
    GROUP BY customer
)
SELECT customer, total, orders
FROM totals
WHERE total >= 100
ORDER BY customer;

Observed result, in psql’s unaligned format:

A|120|2

paid contains four rows totaling 170. totals groups those rows by customer. The outer query keeps only the customer whose total reaches 100. The pending amount of 900 never enters the aggregate.

Our independent direct query, using GROUP BY and HAVING without CTEs, returned the same row. Here the reason to use WITH is to make the stages visible. The official WITH query documentation describes the syntax and its optimization rules.

The comma connects definitions within the same WITH clause. Each definition has a name, an optional column-name list, and a parenthesized query. End the statement after its main query, not between the CTE definitions.

A CTE name lasts for one statement

After the preceding statement finished, our separate SELECT * FROM paid; failed with SQLSTATE 42P01, meaning the relation was not found. We also checked that no stored cte_lab.paid table existed. Naming a result inside WITH did not create one.

If several later statements need an independently populated relation, consider a temporary table with explicit creation and lifetime rules. That is a different choice, documented under CREATE TABLE. A CTE is not a persistent cache, and MATERIALIZED below does not make its result survive the statement.

Use specific names such as paid and totals when they describe a meaningful step. Splitting every expression into another CTE can make a short query harder to follow.

Compare default, MATERIALIZED, and NOT MATERIALIZED

For plan inspection, create a separate table with 20,000 rows and a primary-key index. Each label has the same length, and we collect statistics before running the queries:

CREATE TABLE cte_lab.events (
    id integer PRIMARY KEY,
    label text NOT NULL
);
INSERT INTO cte_lab.events
SELECT n, repeat('x', 40)
FROM generate_series(1, 20000) AS g(n);
ANALYZE cte_lab.events;

Start with a CTE referenced once:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

In our run, PostgreSQL folded the CTE into the parent query. The plan used the events_pkey index and had no CTE Scan. The filter on id = 1000 could apply to the underlying table access.

Now request separate evaluation explicitly:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS MATERIALIZED (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

This plan had a producer that read all 20,000 rows with a sequential scan. Its outer CTE Scan kept one row and removed 19,999 through the filter. Finally, request folding:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS NOT MATERIALIZED (
    SELECT id, label FROM cte_lab.events
)
SELECT id, label FROM selected WHERE id = 1000;

The NOT MATERIALIZED plan used the primary-key index, like the default single-reference query. All three returned the same ID, 1000, and the same 40-character label. The difference was how the work was arranged, not which row was selected.

Tested form	Observed access	Result rows
Single reference, default	Primary-key index; no CTE Scan	1
Single reference, MATERIALIZED	20,000-row producer; outer CTE Scan filters 19,999	1
Single reference, NOT MATERIALIZED	Primary-key index; no CTE Scan	1

The documented default applies to a non-recursive, side-effect-free SELECT CTE without volatile functions: a single reference can be folded, while multiple references normally favor separate evaluation. These examples fit that scope. Do not generalize their plan shape to recursive or data-changing statements.

EXPLAIN ANALYZE executes the statement. Here the measured statements only read our synthetic table; the setup and cleanup commands do change the lab database. PostgreSQL’s EXPLAIN guide explains execution measurements. Our PostgreSQL EXPLAIN ANALYZE guide covers plan reading in more detail.

Reusing a CTE changes the decision

This deliberately small self-join refers to the same CTE twice. It keeps the result easy to verify while exposing the reuse decision:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
WITH selected AS (
    SELECT id, label FROM cte_lab.events
)
SELECT a.id, b.label
FROM selected AS a
JOIN selected AS b ON b.id = a.id
WHERE a.id = 1000;

Our default plan had one producer for selected and two CTE Scan nodes. We then changed only AS ( to AS NOT MATERIALIZED (. The revised plan had no CTE Scan and used two indexed accesses. Both returned the same row.

That result supports folding for this particular selective lookup. It does not show that materialization is always wasteful. If a shared stage performs expensive work needed by several references, calculating it once can avoid repeating that work. We did not test an expensive function or assign a speed multiplier to either approach.

Inspect the actual query, row counts, indexes, and plan before adding a directive. Keep result-equivalence checks alongside plan inspection. A plan that looks cheaper is not useful if a rewrite changes the intended result. For workload-level prioritization, our pg_stat_statements guide explains how to identify queries worth investigating.

Use bounded recursion for a small hierarchy

A recursive CTE can feed the previous iteration’s rows into its next step. This self-contained example starts at node 1 and follows parent-child relationships. It keeps a visited path and a depth bound:

WITH RECURSIVE nodes(id, parent_id, name) AS (
    VALUES (1, NULL::integer, 'root'),
           (2, 1, 'alpha'), (3, 1, 'beta'), (4, 2, 'leaf')
), walk(id, name, depth, path) AS (
    SELECT id, name, 0, ARRAY[id]
    FROM nodes WHERE id = 1
    UNION ALL
    SELECT n.id, n.name, w.depth + 1, w.path || n.id
    FROM walk AS w
    JOIN nodes AS n ON n.parent_id = w.id
    WHERE w.depth < 3 AND NOT n.id = ANY(w.path)
)
SELECT id, name, depth, path FROM walk ORDER BY path;

Observed result:

1|root|0|{1}
2|alpha|1|{1,2}
4|leaf|2|{1,2,4}
3|beta|1|{1,3}

The anchor creates the root row at depth zero. Each recursive step finds children, increments depth, and appends the child ID to the path. The path check rejects a node already visited along that route. The depth condition allows at most three edges from the root.

We tested a cycle by changing the root’s parent from NULL to 4, creating the route 1 → 2 → 4 → 1. The path guard stopped the revisit and returned the same four rows. Changing the depth bound from 3 to 1 returned only the root and its direct children. An absent anchor returned no rows.

The depth bound is a limit, not proof that a larger hierarchy was fully traversed. Choose and document it for the task. Likewise, this path guard is per route, not a global deduplication rule for every graph. The example has unique node IDs and one parent per node.

ORDER BY path controls the displayed order. Do not infer an execution or visitation order from the recursive output without an explicit final sort. The SELECT reference describes result ordering and recursive query syntax. We leave data-changing CTEs and unbounded graph traversal outside this guide.

Clean up and choose the simplest useful form

After checking the results, remove only the objects created for this lab:

DROP TABLE cte_lab.events;
DROP TABLE cte_lab.orders;
DROP SCHEMA cte_lab;

Our cleanup check confirmed that the schema no longer existed. The isolated server was stopped and its temporary initialization password file removed after testing.

Use a CTE when a named stage clarifies a query or recursion is needed. Let the default plan be your starting point. Consider MATERIALIZED or NOT MATERIALIZED only with a reason you can inspect in the plan and verify against the expected rows. Our tests establish these local results; they do not benchmark production data, concurrent workloads, or cloud hosting.