CTE Scan in PostgreSQL EXPLAIN

What it does

A WITH query (a common table expression, CTE) can be handled in two ways:

• Inlined (folded into the main query): PostgreSQL plans the whole thing together, as if you had written a subquery. No CTE node appears; conditions from the outer query can be pushed down into it and use indexes.
• Materialised: PostgreSQL runs the CTE once, stores its result in memory (or in a temporary file if it outgrows work_mem), and every reference to it in the query is a CTE Scan reading that stored result.

In the plan, the materialised CTE appears as a separate section, CTE <name>, with its own plan underneath, and the CTE Scan on <name> nodes read from it.

When the planner picks it

The rules (PostgreSQL 12 and later) are in the docs: a CTE is inlined if it’s not recursive, has no side effects (a plain SELECT with no volatile functions) and is referenced exactly once. Otherwise it’s materialised. So you get a CTE Scan when:

• the CTE is referenced more than once;
• it’s recursive (WITH RECURSIVE), where you’ll also see Recursive Union and WorkTable Scan;
• it modifies data (WITH moved AS (DELETE … RETURNING …)) or calls a volatile function;
• you wrote AS MATERIALIZED.

AS NOT MATERIALIZED asks for inlining even when it’s referenced more than once (at the risk of computing it twice).

Reading its numbers

CTE Scan on recent  (cost=1684.23..2643.62 rows=1 width=68) (actual time=1.459..7.718 rows=1.00 loops=1)
  Filter: (customer_id = 4242)
  Rows Removed by Filter: 43199
  Storage: Memory  Maximum Storage: 3156kB
  Buffers: shared hit=482
  CTE recent
    ->  Index Scan using orders_created_at_idx on orders  (… rows=43200.00 loops=1)

• CTE recent: the sub-plan that computes the CTE, run once. Its time is counted in the first CTE Scan that reads it.
• Filter and Rows Removed by Filter: conditions from the outer query, applied to the stored rows one by one. A CTE Scan can’t use an index, so this is a full read of the stored result.
• Storage: Memory Maximum Storage: 3156kB (PostgreSQL 18): how much space the stored result took and whether it stayed in memory (Memory) or went to a temporary file (Disk). 14–17 don’t show this line.
• Several CTE Scan on <name> nodes (with aliases like big_spenders_1) mean the CTE was referenced more than once and computed once.
• InitPlan: a scalar subquery on the CTE ((SELECT max(spent) FROM big_spenders)) runs once, before the main plan, and appears under an InitPlan heading.

When it’s a problem

1. A filter on the CTE Scan that could have used an index. If a materialised CTE returns 43,200 rows and the outer query keeps one, PostgreSQL computed and stored 43,200 rows for nothing. Fixes:
   • Remove MATERIALIZED, or write AS NOT MATERIALIZED, so the condition is pushed into the CTE. In the example: 7.8 ms → 0.14 ms.
   • Or move the condition inside the CTE yourself. The trade-off: MATERIALIZED is sometimes used on purpose, as an optimisation fence, to stop the planner from combining the CTE with the outer query in a way that turned out badly. Check whether that was the intent before removing it.
2. Old habits from PostgreSQL 11 and earlier. Before 12, every CTE was materialised. Queries written then sometimes rely on that; after an upgrade, the same query may plan differently.
3. A big CTE spills. Storage: Disk (18) or a large temp file in Buffers means the stored result didn’t fit in work_mem. Select fewer columns or rows in the CTE.
4. Rough estimates for joins on a CTE. When a materialised CTE is joined to other tables, the planner has less to go on than for a table. PostgreSQL 17 improved this by using the statistics and sort order of the columns underneath; on older versions, check the row estimates around a CTE Scan with particular care.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE orders;

(Our test table also had a foreign key to customers; it doesn’t change these plans.)

A materialised CTE with the filter outside:

EXPLAIN (ANALYZE, BUFFERS)
WITH recent AS MATERIALIZED (
  SELECT * FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01'
)
SELECT * FROM recent WHERE customer_id = 4242;

CTE Scan on recent  (cost=1684.23..2643.62 rows=1 width=68) (actual time=1.459..7.718 rows=1.00 loops=1)
  Filter: (customer_id = 4242)
  Rows Removed by Filter: 43199
  Storage: Memory  Maximum Storage: 3156kB
  Buffers: shared hit=482
  CTE recent
    ->  Index Scan using orders_created_at_idx on orders  (cost=0.42..1684.23 rows=42640 width=34) (actual time=0.025..3.694 rows=43200.00 loops=1)
          Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-07-01 00:00:00+00'::timestamp with time zone))
          Index Searches: 1
          Buffers: shared hit=482
Planning:
  Buffers: shared hit=3
Planning Time: 0.095 ms
Execution Time: 7.753 ms

The same query without MATERIALIZED. The CTE is inlined, both conditions reach the table, and the planner uses the index on customer_id:

EXPLAIN (ANALYZE, BUFFERS)
WITH recent AS (
  SELECT * FROM orders WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01'
)
SELECT * FROM recent WHERE customer_id = 4242;

Bitmap Heap Scan on orders  (cost=4.58..81.99 rows=1 width=34) (actual time=0.061..0.126 rows=1.00 loops=1)
  Recheck Cond: (customer_id = 4242)
  Filter: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-07-01 00:00:00+00'::timestamp with time zone))
  Rows Removed by Filter: 19
  Heap Blocks: exact=20
  Buffers: shared hit=23
  ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=20 width=0) (actual time=0.029..0.030 rows=20.00 loops=1)
        Index Cond: (customer_id = 4242)
        Index Searches: 1
        Buffers: shared hit=3
Planning Time: 0.085 ms
Execution Time: 0.138 ms

A CTE used twice, materialised by default:

EXPLAIN (ANALYZE, BUFFERS)
WITH big_spenders AS (
  SELECT customer_id, sum(total) AS spent
  FROM orders
  WHERE created_at >= '2024-06-01' AND created_at < '2024-07-01'
  GROUP BY customer_id
)
SELECT count(*) FILTER (WHERE spent > 900), avg(spent),
       (SELECT max(spent) FROM big_spenders)
FROM big_spenders;

Aggregate  (cost=3719.56..3719.57 rows=1 width=72) (actual time=55.446..55.449 rows=1.00 loops=1)
  Buffers: shared hit=2 read=480, temp read=86 written=156
  CTE big_spenders
    ->  HashAggregate  (cost=1897.42..2261.85 rows=29154 width=36) (actual time=23.154..38.989 rows=43200.00 loops=1)
          Group Key: orders.customer_id
          Batches: 5  Memory Usage: 8241kB  Disk Usage: 744kB
          Buffers: shared hit=2 read=480, temp read=86 written=156
          ->  Index Scan using orders_created_at_idx on orders  (cost=0.42..1684.23 rows=42640 width=10) (actual time=0.056..10.554 rows=43200.00 loops=1)
                Index Cond: ((created_at >= '2024-06-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-07-01 00:00:00+00'::timestamp with time zone))
                Index Searches: 1
                Buffers: shared hit=2 read=480
  InitPlan 2
    ->  Aggregate  (cost=655.97..655.98 rows=1 width=32) (actual time=5.321..5.321 rows=1.00 loops=1)
          ->  CTE Scan on big_spenders big_spenders_1  (cost=0.00..583.08 rows=29154 width=32) (actual time=0.001..2.715 rows=43200.00 loops=1)
                Storage: Memory  Maximum Storage: 2200kB
  ->  CTE Scan on big_spenders  (cost=0.00..583.08 rows=29154 width=32) (actual time=23.157..46.133 rows=43200.00 loops=1)
        Storage: Memory  Maximum Storage: 2200kB
        Buffers: shared hit=2 read=480, temp read=86 written=156
Planning:
  Buffers: shared hit=39 read=1
Planning Time: 0.191 ms
Execution Time: 55.710 ms

The grouping ran once (CTE big_spenders), and both references read its 43,200 stored rows. That’s what materialising is for. On PostgreSQL 17.11 the first example produced the same plan without the Storage line.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. Its query editor runs the statement under the cursor (⌘↩), so you can rerun the two versions of a CTE one after the other.