PostgreSQL 좋은 CTE, 나쁜 CTE (Good CTE, Bad CTE)

source https://boringsql.com/posts/good-cte-bad-cte/

Common Table Expression, 줄여서 CTE는 기본 SQL에서 한 걸음 더 나갈 때 개발자들이 가장 먼저 집어 드는 기능이고, 경우에 따라선 거의 유일하게 쓰는 기능이기도 합니다. WITH 뒤에 서브쿼리를 쓰고 이름을 붙인 다음, 나머지 쿼리에서 그걸 참조하면 됩니다. 이 객체는 그 쿼리가 실행되는 동안에만 존재합니다.

그런데 CTE가 널리 쓰이는 이유는 코드를 더 현대적으로 짜기 위해서라기보다, 명령형 로직처럼 다룰 수 있을 거라는 기대에 더 가깝습니다. 많은 사람에게 CTE는 '무서운 쿼리'를 좀 더 이해하기 쉽게 풀어주는 쉬운 해법이고, 데이터베이스에 실행 순서를 강제로 먹일 수 있는 방법처럼 보입니다. 실제로 많은 쿼리를 보면 마치 옵티마이저에게 "먼저 이걸 하고, 그다음 저걸 해"라고 말하는 식으로 작성됩니다.

문제는 여기서 생깁니다. CTE는 쿼리 분해, 재귀, 여러 문장을 묶는 DDL까지 다룰 수 있습니다. 하지만 planner는 CTE를 어떻게 작성하고 어떻게 쓰느냐에 따라 다르게 취급합니다. 오랫동안(PostgreSQL 12 이전까지) CTE는 최적화 장벽처럼 동작했습니다. planner는 CTE 안으로 predicate를 밀어 넣을 수 없었고, 기반 테이블의 인덱스도 활용할 수 없었습니다. 할 수 있는 일이라고는 CTE를 materialize한 뒤 그 결과를 훑는 것뿐이었죠.

PostgreSQL 12에서 이 점이 바뀌었습니다. 이제 CTE는 작성 방식에 따라 inlined되기도 하고, materialized되기도 하고, 그 중간쯤으로 처리되기도 합니다.

예제 스키마

이번 글에서는 PostgreSQL Statistics: Why queries run slow에서 사용한 것과 같은 스키마를 그대로 사용하겠습니다.

CREATE TABLE customers (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(id),
    amount numeric(10,2) NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    note text,
    created_at date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE orders_archive (LIKE orders INCLUDING ALL EXCLUDING IDENTITY);

INSERT INTO customers (name)
SELECT 'Customer ' || i
FROM generate_series(1, 2000) AS i;

INSERT INTO orders (customer_id, amount, status, note, created_at)
SELECT
    (random() * 1999 + 1)::int,
    (random() * 500 + 5)::numeric(10,2),
    (ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)::int],
    CASE WHEN random() < 0.3 THEN 'Some note text here for padding' ELSE NULL END,
    '2022-01-01'::date + (random() * 1095)::int
FROM generate_series(1, 100000);

ANALYZE customers;
ANALYZE orders;

나중에 재귀 예제를 볼 때는 자기 자신을 참조하는 계층 구조를 가진 employees 테이블도 필요합니다.

CREATE TABLE employees (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    manager_id integer REFERENCES employees(id),
    department text NOT NULL
);

INSERT INTO employees (name, manager_id, department) VALUES
    ('Alice',   NULL, 'Engineering'),
    ('Bob',     1,    'Engineering'),
    ('Charlie', 1,    'Engineering'),
    ('Diana',   2,    'Engineering'),
    ('Eve',     2,    'Engineering'),
    ('Frank',   3,    'Sales'),
    ('Grace',   3,    'Sales'),
    ('Hank',    6,    'Sales'),
    ('Ivy',     6,    'Sales');

ANALYZE employees;

최적화 장벽의 시대 (PG 12 이전)

이미 다룬 적이 있지만, PostgreSQL 12 이전에는 모든 CTE가 예외 없이 materialized됐습니다. planner는 먼저 CTE 결과 집합을 전부 계산해서 임시 tuplestore에 저장한 다음, 메인 쿼리에서 CTE를 참조할 때마다 그 tuplestore를 스캔했습니다. 그래서 CTE는 optimization fence가 됐습니다. planner가 그 안을 들여다볼 수 없었기 때문입니다.

간단한 쿼리 하나를 보죠.

EXPLAIN WITH filtered AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

PostgreSQL 11 이하에서는 EXPLAIN 결과가 대략 이렇게 나옵니다.

                            QUERY PLAN
-------------------------------------------------------------------
 CTE Scan on filtered  (cost=1840.00..2290.00 rows=2 width=58)
   Filter: (status = 'pending')
   CTE filtered
     ->  Seq Scan on orders  (cost=0.00..1840.00 rows=10000 width=58)
           Filter: (created_at > '2025-01-01'::date)

여기서 무슨 일이 벌어지는지 보세요. CTE는 날짜 조건으로 orders를 sequential scan합니다. 그리고 조건에 맞는 모든 행을 materialize합니다. 그다음 바깥 쿼리가 materialization이 끝난 뒤에야 status = 'pending' 필터를 적용합니다. 설령 (created_at, status)에 복합 인덱스가 있어도 planner는 그걸 쓸 수 없습니다. CTE 경계를 넘어 predicate를 합칠 수 없기 때문이죠.

왜 이렇게 설계됐을까요? 이유는 두 가지였습니다. 첫째는 snapshot isolation입니다. CTE를 materialize하면 몇 번을 참조하든 결과 집합이 하나의 snapshot에서 한 번만 계산된다는 점이 보장됩니다. 둘째는 부작용이 있는 엣지 케이스를 막기 위해서였습니다. CTE 안에 데이터를 수정하는 문장(INSERT, UPDATE, DELETE)이 들어 있으면, materialization 덕분에 그 문장이 정확히 한 번만 실행됩니다.

커뮤니티에서는 꽤 익숙한 우회법이 자리 잡았습니다. CTE를 서브쿼리로 다시 쓰는 방식입니다. 서브쿼리는 원래부터 planner의 일반적인 최적화 규칙을 그대로 적용받았고, 여기에는 predicate pushdown과 inlining도 포함됐습니다. 같은 쿼리를 SELECT * FROM (SELECT * FROM orders WHERE created_at > '2025-01-01') sub WHERE status = 'pending'처럼 쓰면 훨씬 나은 plan이 나왔습니다.

이 때문에 일종의 우회 문화까지 생겼습니다. 개발할 때는 가독성을 위해 CTE로 쿼리를 작성하고, 실제 운영에 넣기 전에는 중첩 서브쿼리로 다시 바꾸는 식이었죠. 커뮤니티에는 이런 말도 있었습니다. CTEs are optimization fences. 너무 자주 반복된 말이라 지금도 그렇게 믿는 개발자가 많습니다. 하지만 PostgreSQL 12 이후로는 더 이상 맞는 말이 아닙니다.

PostgreSQL 12: CTE 인라인화

PostgreSQL 12에서는 CTE 자동 인라인화가 도입됐습니다. 재귀가 아니고, 부작용이 없고, 한 번만 참조되는 CTE는 이제 기본적으로 inlined됩니다. planner는 이런 CTE를 서브쿼리처럼 취급하기 시작했고, 평소 하던 최적화를 그대로 적용합니다. predicate pushdown, 인덱스 활용, join 재정렬이 모두 CTE 문법이 애초에 없었던 것처럼 똑같이 적용됩니다.

앞 절에서 본 같은 쿼리도 이제는 완전히 다른 plan을 만들어 냅니다.

EXPLAIN WITH filtered AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

                      QUERY PLAN
---------------------------------------------------------------------------
Seq Scan on orders (cost=0.00..2355.00 rows=2 width=58)
   Filter: ((created_at > '2025-01-01'::date) AND (status = 'pending'::text))

plan에서 CTE는 아예 사라졌습니다. 두 predicate가 orders에 대한 단일 scan으로 합쳐졌죠. 적절한 인덱스가 있다면 planner는 그걸 사용할 수 있습니다. 즉, CTE 문법을 썼다고 해서 실행 계획이 달라지지는 않습니다.

PostgreSQL 12에서는 planner의 결정을 직접 덮어쓸 수 있는 키워드도 두 개 추가됐습니다.

• MATERIALIZED - planner가 inline하려는 경우에도 CTE를 강제로 materialize합니다

• NOT MATERIALIZED - planner가 materialize하려는 경우에도 강제로 inline합니다

-- force materialization
EXPLAIN WITH filtered AS MATERIALIZED ( 
   SELECT * FROM orders WHERE created_at > '2025-01-01' 
) 
SELECT * FROM filtered WHERE status = 'pending';

-- Force inlining
EXPLAIN WITH filtered AS NOT MATERIALIZED (
   SELECT * FROM orders WHERE created_at > '2025-01-01' 
)
SELECT * FROM filtered WHERE status = 'pending';

이건 VIEW inlining과 같은 원리입니다.

CTE는 언제 materialized될까?

사례 1: 단일 참조, 부작용 없음 (INLINED)

가장 단순하고, 또 가장 흔한 경우입니다. CTE를 정확히 한 번만 참조하고 부작용도 없다면 planner는 이를 inline합니다.

EXPLAIN WITH recent AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM recent WHERE status = 'pending';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2355.00 rows=2 width=59)
   Filter: ((created_at > '2025-01-01'::date) AND (status = 'pending'::text))
(2 rows)

두 predicate가 하나로 합쳐졌습니다. planner는 orders에 대해 가능한 모든 access path를 직접 검토합니다.

사례 2: 여러 번 참조됨 (MATERIALIZED)

CTE가 두 번 이상 참조되면 planner는 이를 materialize합니다. 사실 이건 제약이라기보다 기능에 가깝습니다. CTE를 한 번만 계산하고 재사용하니까요. 덕분에 같은 일을 중복해서 하지 않아도 됩니다.

EXPLAIN WITH summary AS (
    SELECT status, count(*) AS cnt FROM orders GROUP BY status
)
SELECT a.status, b.status
FROM summary a, summary b
WHERE a.cnt > b.cnt;

                                 QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=2355.04..2355.52 rows=5 width=64)
   Join Filter: (a.cnt > b.cnt)
   CTE summary
     ->  HashAggregate  (cost=2355.00..2355.04 rows=4 width=17)
           Group Key: orders.status
           ->  Seq Scan on orders  (cost=0.00..1855.00 rows=100000 width=9)
   ->  CTE Scan on summary a  (cost=0.00..0.08 rows=4 width=40)
   ->  CTE Scan on summary b  (cost=0.00..0.08 rows=4 width=40)
(8 rows)

CTE Scan 노드는 두 번 나오지만 HashAggregate는 한 번만 실행됩니다. 비용이 큰 계산을 여러 번 참조하는 경우라면, 이게 바로 원하는 동작입니다.

사례 3: 재귀 CTE (ALWAYS MATERIALIZED)

재귀 CTE는 반복 사이에 작업용 테이블을 유지해야 합니다. 그래서 inline할 방법이 없습니다. 재귀는 글 뒤쪽에서 좀 더 자세히 다루겠습니다.

EXPLAIN WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

                                          QUERY PLAN
-------------------------------------------------------------------------------
 CTE Scan on subordinates  (cost=17.21..18.83 rows=81 width=40)
   CTE subordinates
     ->  Recursive Union  (cost=0.00..17.21 rows=81 width=13)
           ->  Seq Scan on employees  (cost=0.00..1.11 rows=1 width=13)
                 Filter: (id = 1)
           ->  Hash Join  (cost=0.33..1.53 rows=8 width=13)
                 Hash Cond: (e.manager_id = s.id)
                 ->  Seq Scan on employees e  (cost=0.00..1.09 rows=9 width=13)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4)
                       ->  WorkTable Scan on subordinates s  (cost=0.00..0.20 rows=10 width=4)
(10 rows)

사례 4: 데이터를 수정하는 CTE (ALWAYS MATERIALIZED)

INSERT, UPDATE, DELETE를 포함한 CTE는 항상 materialized됩니다. 부작용은 예측 가능한 순서로, 정확히 한 번만 실행돼야 하기 때문입니다.

EXPLAIN WITH deleted AS (
    DELETE FROM orders WHERE status = 'cancelled' RETURNING *
)
SELECT count(*) FROM deleted;

                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=2670.13..2670.14 rows=1 width=8)
   CTE deleted
     ->  Delete on orders  (cost=0.00..2105.00 rows=25117 width=6)
           ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25117 width=6)
                 Filter: (status = 'cancelled'::text)
   ->  CTE Scan on deleted  (cost=0.00..502.34 rows=25117 width=0)
(6 rows)

여기서 CTE Scan이 보이는 이유는 count(*)가 실행되기 전에 DELETE가 끝까지 먼저 수행돼야 하기 때문입니다.

사례 5: VOLATILE 함수 (MATERIALIZED)

CTE 안에 VOLATILE 함수가 있으면 planner는 이를 materialize합니다. 함수가 여러 번 평가되면서 서로 다른 결과가 나오는 일을 막기 위해서입니다.

EXPLAIN WITH rand AS (
    SELECT id, random() AS r FROM orders
)
SELECT * FROM rand WHERE r < 0.01;

                              QUERY PLAN
-----------------------------------------------------------------------
 CTE Scan on rand  (cost=2105.00..4355.00 rows=33333 width=12)
   Filter: (r < '0.01'::double precision)
   CTE rand
     ->  Seq Scan on orders  (cost=0.00..2105.00 rows=100000 width=12)
(4 rows)

rand는 한 번만 참조되는데도 CTE Scan이 있습니다. random()이 VOLATILE이기 때문에 materialization이 강제되는 겁니다.

사례 6: STABLE 함수 (INLINED)

now() 같은 STABLE 함수는 inline을 막지 않습니다. 이유는 시간이 트랜잭션 시작 시점에 고정되기 때문입니다.

EXPLAIN WITH recent AS (
    SELECT * FROM orders
    WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent WHERE status = 'pending';

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2855.00 rows=2 width=59)
   Filter: ((status = 'pending'::text) AND (created_at > (now() - '7 days'::interval)))
(2 rows)

CTE Scan은 없습니다. planner는 사례 1처럼 CTE를 inline하고 두 predicate를 합칩니다. now()는 트랜잭션 안에서 같은 값을 돌려주므로 STABLE입니다. 그리고 planner의 inlining 체크는 VOLATILE 함수만 봅니다(contain_volatile_functions()). STABLE은 이 검사를 통과합니다.

그런데 왜 많은 사람이 STABLE도 inline을 막는다고 생각할까요? PostgreSQL 12 이전에는 volatility와 상관없이 모든 CTE가 materialized됐기 때문입니다. PG 12에서 inlining이 도입된 뒤에도 함수 수준에서 실제 장벽이 되는 건 VOLATILE뿐이었습니다. 하지만 "CTEs are optimization fences"라는 오래된 인식이 너무 깊게 남아 있어서, 많은 개발자가 STABLE도 문제일 거라고 짐작해 버린 거죠. 실제로는 아닙니다.

실제로 inline을 막는 함수는 clock_timestamp()입니다. now()와 달리 이 함수는 VOLATILE이고 호출할 때마다 다른 값을 돌려줍니다. clock_timestamp()가 들어간 CTE는 materialize됩니다. 마찬가지로 random()이나 nextval()도 VOLATILE이기 때문에 materialization을 강제합니다(사례 5 참고).

now()가 들어간 CTE가 materialize되는 걸 봤다면 원인은 다른 데 있습니다. 여러 번 참조됐거나, 데이터를 수정하는 문장이 있거나, 명시적으로 MATERIALIZED 힌트를 준 경우입니다. STABLE 탓으로 돌리면 안 됩니다.

사례 7: 힌트로 동작 강제하기

planner의 결정은 언제든 직접 덮어쓸 수 있습니다.

-- force materialization on something that would normally be inlined
EXPLAIN WITH filtered AS MATERIALIZED (
    SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM filtered WHERE amount > 400;

                              QUERY PLAN
----------------------------------------------------------------------
 CTE Scan on filtered  (cost=2105.00..2670.58 rows=5290 width=92)
   Filter: (amount > '400'::numeric)
   CTE filtered
     ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=59)
           Filter: (status = 'pending'::text)
(5 rows)

-- force inlining on something that would normally be materialized
EXPLAIN WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM filtered a
JOIN filtered b ON a.customer_id = b.customer_id;

                                    QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=2419.21..10686.65 rows=317742 width=118)
   Hash Cond: (orders.customer_id = orders_1.customer_id)
   ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=59)
         Filter: (status = 'pending'::text)
   ->  Hash  (cost=2105.00..2105.00 rows=25137 width=59)
         ->  Seq Scan on orders orders_1  (cost=0.00..2105.00 rows=25137 width=59)
               Filter: (status = 'pending'::text)
(7 rows)

CTE가 materialized됐다면 orders 테이블을 한 번만 스캔한 뒤, 그 결과에 대해 두 번의 CTE Scan이 나와야 합니다. 하지만 실제로는 플래너가 이 쿼리를, 두 개의 서브쿼리를 일반적으로 조인한 것처럼 처리했습니다.

여러 번 참조되는 CTE에 NOT MATERIALIZED를 쓸 때는 특히 조심해야 합니다. 인라인을 강제하면 서브쿼리가 참조될 때마다 한 번씩 실행됩니다. 위 예시에서는 orders 테이블을 두 번 스캔합니다. a를 위해 한 번, b를 위해 한 번입니다. 결과 집합이 작다면 괜찮을 수도 있습니다. 하지만 크기가 커지면 일을 두 배로 하게 됩니다. 쓰기 전에 꼭 측정해 보세요.

사례 8: FOR UPDATE / FOR SHARE (MATERIALIZED)

행 잠금(row-locking) 절은 부작용이 없고 한 번만 참조되는 CTE라도 materialization을 강제합니다. 내부적으로 플래너의 contain_dml() 체크는 FOR UPDATE와 FOR SHARE를 데이터 변경문과 똑같이 취급합니다.

EXPLAIN WITH locked AS (
    SELECT * FROM orders WHERE status = 'pending' FOR UPDATE
)
SELECT * FROM locked WHERE amount > 400;

                         QUERY PLAN
----------------------------------------------------------------------------
 CTE Scan on locked  (cost=2356.37..2921.95 rows=5290 width=92)
   Filter: (amount > '400'::numeric)
   CTE locked
     ->  LockRows  (cost=0.00..2356.37 rows=25137 width=65)
           ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=65)
                 Filter: (status = 'pending'::text)
(6 rows)

FOR UPDATE가 없었다면 이 CTE는 인라인됐을 겁니다. 여기서는 LockRows 노드와 CTE Scan이 materialization이 일어났다는 걸 보여줍니다.

판단 매트릭스

PostgreSQL 버전별로 정리하면 전체 그림은 이렇습니다.

통계의 블랙홀

PostgreSQL Statistics: Why queries run slow에서 언급했듯, materialized CTE는 "통계가 닿지 않는 곳" 중 하나입니다. 실무적으로 보면 이게 CTE materialization의 가장 큰 문제라고 해도 과장이 아닙니다.

플래너가 CTE를 materialize하면 결과 집합은 임시 tuplestore에 저장됩니다. 그런데 이 tuplestore에는 pg_statistic 엔트리가 없습니다. 히스토그램도 없고, MCV도 없고, 상관관계 데이터도 없습니다. 그래서 플래너는 하드코딩된 기본값을 써서 행 수와 값 분포를 추정할 수밖에 없습니다.

직접 보죠. 아래는 10,000개 행을 대상으로 한 CTE입니다.

EXPLAIN WITH all_orders AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM all_orders WHERE status = 'pending' AND amount > 400;

                              QUERY PLAN
-----------------------------------------------------------------------
 CTE Scan on all_orders  (cost=1855.00..4355.00 rows=5290 width=92)
   Filter: ((amount > '400'::numeric) AND (status = 'pending'::text))
   CTE all_orders
     ->  Seq Scan on orders  (cost=0.00..1855.00 rows=100000 width=59)
(4 rows)

플래너는 5,290행이라고 추정했습니다. 이 숫자는 어디서 나왔을까요? 플래너는 CTE 내부의 status에 대한 MCV 리스트도 없고, amount에 대한 히스토그램도 모릅니다. 그래서 amount 범위 비교에는 기본 선택도 0.3333을 쓰고, status 동등 비교에도 대략적인 추정값을 적용한 다음, 이를 입력 100,000행에 곱합니다.

이 CTE가 인라인됐다면 플래너는 orders 테이블의 pg_statistic에서 실제 통계를 읽어 와서, 기본값이 아니라 실제 데이터 분포를 바탕으로 추정했을 겁니다.

단순한 쿼리라면 이 차이가 크게 중요하지 않을 수도 있습니다. 하지만 materialized CTE가 조인으로 이어지면, 기본값 기반 추정이 연쇄적으로 문제를 일으킬 수 있습니다. 원래는 hash join이 나아야 할 자리에 nested loop를 고를 수도 있고, 반대도 가능합니다. 필요한 메모리를 과소평가해서 예상치 못하게 디스크로 spill이 날 수도 있습니다.

PG 17: 통계 전파

PostgreSQL 17에서는 materialized CTE에 두 가지 중요한 개선이 들어갔습니다.

컬럼 통계 전파. 플래너가 CTE Scan 노드를 만들 때, 이제는 하위 쿼리의 컬럼 통계를 scan 노드로 전파합니다. 즉, 원본 테이블의 n_distinct, MCV 리스트, 히스토그램이 CTE scan의 추정에도 반영될 수 있습니다.

Path key 전파. 이제 materialized CTE도 정렬 순서 정보를 보존합니다. CTE의 서브쿼리가 정렬된 결과를 만든다면, 플래너가 그 사실을 알고 downstream에서 불필요한 sort를 건너뛸 수 있습니다.

이 개선으로 추정 오차는 꽤 줄었지만, 완전히 사라진 건 아닙니다. 계획 정확도만 놓고 보면 인라인된 CTE가 여전히 더 낫습니다. 플래너가 전파된 복사본이 아니라 베이스 테이블의 실제 통계를 직접 보기 때문입니다. CTE를 굳이 materialize할 이유가 없다면, 억지로 그렇게 만들지 마세요.

materialization이 도움이 될 때

materialization이 항상 나쁜 건 아닙니다.

여러 번 참조될 때. CTE 결과를 여러 곳에서 쓴다면 materialization은 한 번 계산한 결과를 재사용합니다. 반대로 없으면 참조할 때마다 서브쿼리가 다시 실행됩니다.

EXPLAIN WITH monthly_totals AS (
    SELECT date_trunc('month', created_at) AS month,
           status,
           sum(amount) AS total
    FROM orders
    GROUP BY 1, 2
)
SELECT cur.month, cur.status, cur.total,
       prev.total AS prev_month_total,
       cur.total - prev.total AS delta
FROM monthly_totals cur
LEFT JOIN monthly_totals prev
    ON cur.month = prev.month + interval '1 month'
    AND cur.status = prev.status;

                                       QUERY PLAN
-------------------------------------------------------------------------------
 Merge Left Join  (cost=3887.46..3990.90 rows=4384 width=136)
   Merge Cond: ((cur.month = ((prev.month + '1 mon'::interval))) AND (cur.status = prev.status))
   CTE monthly_totals
     ->  HashAggregate  (cost=3105.00..3181.72 rows=4384 width=49)
           Group Key: date_trunc('month'::text, (orders.created_at)::timestamp with time zone), orders.status
           ->  Seq Scan on orders  (cost=0.00..2355.00 rows=100000 width=23)
   ->  Sort  (cost=352.87..363.83 rows=4384 width=72)
         Sort Key: cur.month, cur.status
         ->  CTE Scan on monthly_totals cur  (cost=0.00..87.68 rows=4384 width=72)
   ->  Sort  (cost=352.87..363.83 rows=4384 width=72)
         Sort Key: ((prev.month + '1 mon'::interval)), prev.status
         ->  CTE Scan on monthly_totals prev  (cost=0.00..87.68 rows=4384 width=72)
(12 rows)

집계는 한 번만 실행됩니다. cur와 prev 둘 다 materialize된 결과를 읽습니다. materialization이 없다면 이 집계 전체를 두 번 돌려야 했을 겁니다.

비용이 큰 VOLATILE 표현식. CTE 안에 volatile 함수 호출이나 무거운 계산이 들어 있다면, materialization은 그것들이 정확히 한 번만 실행되도록 보장합니다.

데이터 변경 작업. writable CTE의 핵심은 부작용이 한 번만 발생하고, 그 RETURNING 데이터를 아래 단계에서 이어서 쓸 수 있다는 점입니다. 여기서는 materialization이 선택 사항이 아닙니다.

인라인만으로는 부족할 때

서두에서 말한 "먼저 이걸 하고, 그다음 저걸 한다"는 식의 명령형 사고방식은, 플래너가 CTE를 인라인한다고 해서 사라지지 않습니다. 개인적으로도 이건 쿼리 리팩터링할 때마다 계속 튀어나오는, 정말 끊임없이 소재를 제공하는 주제입니다.

개발자들은 여전히 쿼리를 순차적인 파이프라인처럼 구성합니다. 그런데 바로 그 구조 자체가 materialization과는 별개로 성능 문제를 만들기도 합니다.

흔한 패턴이 이렇습니다. 첫 번째 CTE에서 행을 필터링하고, 다음 CTE에서 관련 테이블을 LEFT JOIN한 뒤 GROUP BY로 메타데이터를 집계하고, 그다음 단계에서 다시 집계 결과를 기준으로 필터링합니다. 읽기에는 깔끔한 파이프라인처럼 보이지만, 중간의 GROUP BY가 플래너가 더 이상 최적화하지 못하는 벽을 만들어 버립니다.

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
),
order_metadata AS (
    SELECT
        o.id,
        bool_or(oa.id IS NOT NULL) AS was_archived,
        count(o2.id) AS related_count
    FROM recent_orders o
    LEFT JOIN orders_archive oa ON o.id = oa.id
    LEFT JOIN orders o2 ON o.customer_id = o2.customer_id AND o2.id != o.id
    GROUP BY o.id
)
SELECT o.*, m.was_archived, m.related_count
FROM recent_orders o
JOIN order_metadata m ON o.id = m.id
WHERE m.was_archived = false
  AND m.related_count > 0;

여기 있는 각 CTE는 한 번씩만 참조되므로 전부 인라인됩니다. materialization도 없고, optimization fence도 없습니다. 플래너는 쿼리 전체를 한 번에 봅니다. 그럼 뭐가 문제일까요?

문제는 order_metadata 안의 GROUP BY입니다. 인라인으로 풀어도 planner는 was_archived = false 조건을 집계 아래로 밀어 넣지 못합니다. 먼저 필터링된 모든 order를 orders_archive와 LEFT JOIN하고, orders와 self-join한 뒤, 그 전부에 대해 집계를 계산하고 나서야 조건에 맞지 않는 행을 버릴 수 있습니다. recent_orders가 50,000행을 반환하는데 실제로 archive된 건 200건뿐이라면, 49,800행은 괜히 join하고 집계하느라 낭비하는 셈입니다.

해결 방법은 "집계한 뒤 필터링"하는 패턴을 상관 EXISTS 서브쿼리로 바꾸는 것입니다:

SELECT o.*
FROM orders o
WHERE o.created_at > '2024-01-01'
  AND NOT EXISTS (
    SELECT 1 FROM orders_archive oa WHERE oa.id = o.id
  )
  AND EXISTS (
    SELECT 1 FROM orders o2
    WHERE o2.customer_id = o.customer_id AND o2.id != o.id
  );

EXISTS는 첫 번째로 일치하는 행을 찾는 순간 바로 멈춥니다. planner는 created_at > '2024-01-01' 조건을 orders의 index scan까지 끝까지 밀어 넣은 다음, 결과마다 관련 테이블을 확인하면 됩니다. 집계도 없고, 쓸데없는 작업도 없습니다.

실전 감각으로 기억할 규칙: CTE 안에서 GROUP BY나 LEFT JOIN을 써서 불리언 하나("이 행에 관련 데이터가 있나?")를 계산하고 있다면, planner가 그 너머를 보지 못하게 벽을 세운 셈입니다. 상관 EXISTS를 쓰면 planner가 필터를 아래로 밀어 넣고, 스캔도 일찍 멈출 수 있습니다. CTE가 materialized되든 inlined되든 이 점은 같습니다.

Writable CTEs (강력하지만 함정도 많다)

데이터를 수정하는 CTE를 쓰면 WITH 절 안에서 INSERT, UPDATE, DELETE를 실행하고, 그 RETURNING 결과를 뒤따르는 CTE나 메인 쿼리에서 활용할 수 있습니다.

EXPLAIN WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < '2023-01-01'
    RETURNING *
),
archived AS (
    INSERT INTO orders_archive
    SELECT * FROM deleted
    RETURNING id
)
SELECT count(*) FROM archived;

                                          QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=2709.19..2709.20 rows=1 width=8)
   CTE deleted
     ->  Delete on orders  (cost=0.00..2355.00 rows=8334 width=6)
           ->  Seq Scan on orders  (cost=0.00..2355.00 rows=8334 width=6)
                 Filter: ((created_at < '2023-01-01'::date) AND (status = 'cancelled'::text))
   CTE archived
     ->  Insert on orders_archive  (cost=0.00..166.68 rows=8334 width=92)
           ->  CTE Scan on deleted  (cost=0.00..166.68 rows=8334 width=92)
   ->  CTE Scan on archived  (cost=0.00..166.68 rows=8334 width=0)
(9 rows)

이 쿼리는 오래된 취소 주문을 삭제하고, archive 테이블로 옮기고, 몇 건이 archive됐는지까지 한 번의 atomic statement로 처리합니다. 애플리케이션 레벨에서 따로 조율할 필요도 없습니다.

하지만 여기에도 날카로운 함정이 있습니다.

방금 쓴 데이터를 바로 읽을 수는 없다

데이터를 수정하는 CTE의 모든 하위 statement는 같은 snapshot을 봅니다. 즉, 한 CTE의 변경 결과는 다른 CTE나 메인 쿼리가 target table을 읽을 때 보이지 않습니다. CTE 단계 사이에서 데이터를 전달할 수 있는 통로는 RETURNING 절뿐입니다.

SELECT count(1) FROM orders WHERE customer_id = 1;

 count
-------
    31
(1 row)

WITH ins AS (
    INSERT INTO orders (customer_id, amount, status, created_at)
    VALUES (1, 100.00, 'pending', CURRENT_DATE)
    RETURNING id
)
-- this does NOT see the row we just inserted
SELECT count(1) FROM orders WHERE customer_id = 1;

 count
-------
    31
(1 row)

이 count(1) 쿼리는 insert 이전 snapshot을 봅니다. 삽입한 데이터가 필요하다면 테이블을 다시 읽지 말고 ins CTE의 RETURNING 절을 써야 합니다.

Tuple shuffling

흔히 writable CTE를 써서 여러 테이블 사이로 행을 atomic하게 옮기기도 합니다.

WITH moved AS (
    DELETE FROM orders_staging
    RETURNING *
)
INSERT INTO orders
SELECT * FROM moved;

이 쿼리는 staging 테이블의 모든 행을 지우고 production 테이블에 넣는 작업을 한 번의 atomic operation으로 처리합니다. 데이터가 두 테이블에 동시에 있거나, 반대로 어디에도 없는 순간이 생기지 않습니다.

데이터를 수정하는 CTE가 들어가면 statement 전체에서 parallel query가 비활성화됩니다. 읽기와 쓰기가 섞인 복잡한 쿼리라면, 쓰기 CTE 하나 때문에 읽기 전용 부분까지 병렬 실행을 못 하게 됩니다.

Recursive CTEs는 항상 materialized된다

Recursive CTE는 반복적으로 동작하는 working-table 메커니즘을 사용합니다. 이름은 recursive지만, 실제로는 진짜 재귀처럼 동작하지 않습니다. PostgreSQL이 미완성 쿼리 스택을 중첩해서 "자기 자신을 호출"하는 방식이 아니라, 루프를 돌면서 처리합니다.

1. non-recursive term("seed")을 실행한다. 결과를 working table에 넣는다.

2. working table을 입력으로 recursive term을 실행한다. 새로 나온 행은 다음 working table이 된다.

3. recursive term이 더 이상 새 행을 반환하지 않을 때까지 반복한다.

4. 모든 반복 결과의 union을 반환한다.

WITH RECURSIVE org_chart AS (
    -- Seed: start from the CEO
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive term: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

id |  name   | manager_id | depth
----+---------+------------+-------
  1 | Alice   |            |     1
  2 | Bob     |          1 |     2
  3 | Charlie |          1 |     2
  4 | Diana   |          2 |     3
  5 | Eve     |          2 |     3
  6 | Frank   |          3 |     3
  7 | Grace   |          3 |     3
  8 | Hank    |          6 |     4
  9 | Ivy     |          6 |     4
(9 rows)

UNION vs UNION ALL

Recursive CTE에서 UNION과 UNION ALL 중 무엇을 쓰느냐는 일반 쿼리보다 훨씬 더 중요합니다.

UNION ALL은 중복을 포함해 모든 행을 그대로 유지합니다. 더 빠르지만 위험합니다. 그래프에 cycle이 있으면 재귀가 끝나지 않기 때문입니다. PostgreSQL은 쿼리를 취소하거나 메모리가 바닥날 때까지 계속 실행합니다.

UNION은 반복 단계마다 중복을 제거합니다. 그래서 cycle이 있는 그래프에서 무한 루프를 막을 수 있지만, 매 단계마다 행을 해시하고 비교하는 비용이 추가됩니다.

PostgreSQL 14에서는 SQL 표준 SEARCH와 CYCLE 절이 추가되면서, 순회 순서(breadth-first vs. depth-first)를 제어하고 cycle을 감지하기 위해 예전처럼 수동 패턴을 짤 필요가 없어졌습니다. SEARCH BREADTH FIRST BY / SEARCH DEPTH FIRST BY로 순서를 제어하고, CYCLE은 cycle을 자동으로 감지해 표시해 줍니다. 예전처럼 방문한 ID 배열을 계속 쌓는 방식보다 훨씬 깔끔합니다.

순수한 계층형 데이터(cycle이 없는 트리)라면 recursive CTE 대신 ltree extension도 고려할 만합니다. 전체 경로를 label tree 형태로 저장하고, GiST index로 ancestor/descendant 쿼리를 효율적으로 처리할 수 있습니다. 대신 on-the-fly recursion이 아니라 비정규화된 저장 방식을 택해야 한다는 trade-off가 있습니다.

드물지만 까다로운 엣지 케이스들

Partition pruning이 사라지는 경우

partitioned table 위에 CTE를 materialize하면, CTE scan 쪽에서는 partition pruning이 일어나지 않습니다. materialized 결과는 partition 메타데이터와 끊어진 평평한 tuplestore이기 때문입니다.

-- assume orders is range-partitioned by created_at
WITH recent AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM recent WHERE created_at > '2025-06-01';

created_at > '2025-06-01' 조건은 materialization 이후에 적용됩니다. 즉, 실제로는 한두 개 partition만 보면 되더라도 CTE를 만들기 위해 모든 partition을 스캔합니다. partition pruning을 유지하려면 NOT MATERIALIZED를 쓰거나, 그냥 planner가 inline할 수 있게 두는 편이 낫습니다.

Prepared statements와 plan caching {#prepared-statements-and-plan-caching}

PostgreSQL은 prepared statement를 처음 5번 실행할 때는 custom plan을 만듭니다. 그 이후에는 generic plan으로 전환할 수 있습니다. 그리고 CTE inlining 여부는 custom plan과 generic plan에서 달라질 수 있습니다. generic plan은 실제 parameter 값을 모르기 때문입니다.

즉, 처음 5번 호출에서는 inline되던 CTE가 6번째부터 materialize되기 시작할 수도 있고, 반대로 바뀔 수도 있습니다. prepared statement에서 plan이 갑자기 달라진다면, CTE inlining 동작이 바뀐 건 아닌지 확인해 보세요.

work_mem spilling

materialized CTE는 결과를 메모리에 저장하고, 그 한계는 work_mem으로 정해집니다. 결과 집합이 이 한도를 넘으면 조용히 디스크의 임시 파일로 spill됩니다. 에러가 나는 건 아니고, 그냥 느려질 뿐입니다.

log_temp_files = 0(모든 temp 파일 로그)으로 모니터링하거나, EXPLAIN (ANALYZE, BUFFERS)에서 temp read/write 횟수를 확인하면 됩니다.

PostgreSQL 18: 이제 EXPLAIN에 memory/disk 사용량이 표시된다

PostgreSQL 18부터는 EXPLAIN ANALYZE가 Material node의 메모리와 디스크 사용량을 보고합니다. CTE materialization도 여기에 포함됩니다. materialized CTE가 실제로 얼마나 많은 메모리를 썼는지, 디스크로 spill됐는지까지 바로 확인할 수 있습니다.

CTE와 security barrier views

security_barrier view는 바깥쪽 필터를 적용하기 전에 view 내부 로직을 데이터베이스가 먼저 완전히 해석하도록 강제하는 "블랙박스"입니다.

security-barrier view는 보안상 이유로 원래부터 subquery flattening을 막습니다. 사용자 정의 함수가 보면 안 되는 행을 보지 못하게 하려는 장치입니다. 여기에 CTE까지 결합하면 최적화 장벽이 더 두꺼워집니다. planner는 view도 inline하지 못하고 CTE도 inline하지 못합니다. 이런 상황에서 성능이 중요하다면, 보안상 민감한 필터링 결과를 먼저 temporary table로 materialize하는 방법을 고려해 볼 수 있습니다.

CTE vs. subquery vs. temporary table

한 번만 참조되는 CTE 는 PG 12+에서 planner가 inline 처리하므로 실행 계획에 영향을 주지 않습니다. 복잡한 쿼리를 읽기 좋게 나눌 때 기본 선택지로 가장 무난합니다.

여러 번 참조되는 CTE (결과가 작은 경우) 도 충분히 감당할 만한 비용입니다. Materialization이 일어나면 subquery는 한 번만 실행됩니다. 집계 결과나 필터링된 일부처럼 몇백 행 정도만 나오는 경우라면 오버헤드는 거의 없습니다.

Henrietta Dombrovskaya가 강조하듯, "The best temporary table is the one you didn't create". temp table로 가기 전에 인덱싱과 쿼리 재작성으로 해결할 수 있는 여지가 없는지 먼저 끝까지 살펴보세요. DDL 오버헤드가 실행 성능 이득보다 더 커지는 경우가 많기 때문입니다.

여러 번 참조되는 CTE (결과가 큰 경우) 는 대신 temporary table을 고려해볼 만한 상황입니다. Materialized CTE에는 인덱스도 없고 통계 정보도 없습니다. 반면 temporary table은 둘 다 가질 수 있고, Introduction to Buffers에서 다뤘듯 local buffer를 사용하므로 locking도 더 단순하고 WAL 오버헤드도 없습니다. CTE에서 나온 100k+ 행과 조인해야 한다면 temp table을 만들고, 인덱스를 추가한 뒤 ANALYZE까지 해두세요.

쓰기 가능한 CTE를 사용하는 데이터 변경 작업. 원자적으로, 단일 statement 안에서 처리하는 동작은 다른 대안으로는 얻을 수 없습니다.

Recursive CTE. 순수 SQL 안에서는 대체재가 없습니다.

인덱스/통계 정보가 필요한 큰 중간 결과. 정말 필요하다면 temporary table을 쓰세요. Reading Buffer statistics에서 설명했듯 temporary table은 인덱스, 통계 정보, buffer 관리까지 planner가 완전히 지원하지만, materialized CTE에는 그런 지원이 없습니다.

The PG 18 state of affairs

PostgreSQL 18에서는 CTE 처리 방식이 계속 다듬어지고 있지만, 판을 뒤집을 정도의 큰 변화는 없습니다.

• EXPLAIN에서 CTE materialization 노드의 메모리/디스크 사용량을 보여줍니다. 이제 CTE가 work_mem 안에 들어왔는지, 아니면 디스크로 spill됐는지 직접 확인할 수 있습니다.

• 같은 테이블을 대상으로 하는 CTE의 쿼리 계획이 더 좋아졌습니다. 여러 CTE가 같은 기반 테이블을 참조할 때 planner가 중복 스캔을 더 똑똑하게 제거합니다.

• rules로 정의된 updatable view에서 data-modifying CTE 관련 문제가 수정됐습니다. Writable CTE가 rules 기반 view와 잘못 상호작용하던 edge case가 해결됐습니다.

• CTE inlining은 이제 충분히 성숙했습니다. 핵심 inlining 로직 자체는 PG 12 이후로 바뀌지 않았습니다. 대신 그 주변이 계속 좋아졌습니다. 통계 정보 전파는 더 나아졌고(PG 17), materialisation 진단도 좋아졌고(PG 18), 비용 추정도 더 정확해졌습니다.

CTE는 좋은 도구입니다. 다만 지금 내가 쥔 쪽이 칼날인지 아닌지는 알고 써야 합니다.