좋은 CTE, 나쁜 CTE | boringSQL

Tools

SQL Labs ALPHARegreSQLFixturizedryrun

Resources

가이드Shared Buffers Visualizer8KB Page Visualizer

발표아카이브소개

홈»게시물»좋은 CTE, 나쁜 CTE

좋은 CTE, 나쁜 CTE

2026-03-29·18분·Radim Marek

목차

• 예제 스키마

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

• PostgreSQL 12: CTE 인라인

• CTE는 언제 구체화될까?

  • 사례 1: 단일 참조, 부작용 없음 (INLINED)
  • 사례 2: 다중 참조 (MATERIALIZED)
  • 사례 3: 재귀 CTE (ALWAYS MATERIALIZED)
  • 사례 4: 데이터 변경 CTE (ALWAYS MATERIALIZED)
  • 사례 5: VOLATILE 함수 (MATERIALIZED)
  • 사례 6: STABLE 함수 (INLINED)
  • 사례 7: 힌트로 동작 강제하기
  • 사례 8: FOR UPDATE / FOR SHARE (MATERIALIZED)
  • 판단 매트릭스

• 통계 블랙홀

  • PG 17: 통계 전파

• 구체화가 도움이 될 때

• 인라인만으로는 충분하지 않을 때

• 쓰기 가능한 CTE (강력함과 함정)

  • 방금 쓴 것을 읽을 수 없다
  • 튜플 옮기기

• 재귀 CTE는 항상 구체화된다

  • UNION vs UNION ALL

• 이색적인 엣지 케이스

  • 파티션 프루닝 손실
  • Prepared statement와 계획 캐싱
  • work_mem 스필링
  • CTE와 security barrier view

• CTE vs. 서브쿼리 vs. 임시 테이블

• PG 18의 현재 상황

Common Table Expression, 즉 CTE는 개발자들이 기본 SQL을 넘어설 때 가장 먼저 손에 잡는 기능인 경우가 많고, 종종 유일한 기능이기도 합니다. WITH 뒤에 서브쿼리를 쓰고, 이름을 붙인 뒤, 나머지 쿼리에서 사용합니다. 이 객체는 그 쿼리가 실행되는 동안에만 존재합니다.

하지만 CTE의 인기는 대개 코드를 현대화한다는 점보다 명령형 로직을 약속한다는 점과 더 관련이 있습니다. 많은 사람에게 CTE는 ‘무서운 쿼리’를 이해하기 쉽게 만드는 쉬운 처방이자, 데이터베이스에 실행 순서를 강제하는 방법처럼 보입니다. 많은 사람이 쿼리를 작성하는 방식은 마치 옵티마이저에게 “먼저 이걸 하고, 그다음 저걸 해”라고 말하는 것과 같습니다.

이로 인해 문제가 생깁니다. CTE는 쿼리 분해, 재귀, 다중 문장 DDL을 다룹니다. 하지만 플래너는 이를 어떻게 작성하고 사용하는지에 따라 다르게 취급합니다. 오랫동안(PostgreSQL 12 이전) CTE는 최적화 장벽처럼 동작했습니다. 플래너는 그 안으로 조건을 밀어 넣을 수 없었고, 기반 테이블의 인덱스도 활용할 수 없었습니다. CTE를 구체화하고 그 결과를 스캔하는 것 외에는 할 수 있는 일이 없었습니다.

PostgreSQL 12가 이것을 바꿨습니다. 이제 CTE는 작성 방식에 따라 인라인되거나, 구체화되거나, 혹은 그 중간 어딘가의 동작을 하게 됩니다.

예제 스키마

이 글에서는 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가 구체화되었습니다. 예외는 없었습니다. 플래너는 CTE 결과 집합 전체를 계산하고, 임시 tuplestore에 저장한 뒤, 메인 쿼리가 CTE를 참조할 때마다 그 tuplestore를 스캔했습니다. 이 때문에 CTE는 최적화 장벽이 되었습니다. 플래너가 그 안을 들여다볼 수 없었기 때문입니다.

다음과 같은 간단한 쿼리를 보겠습니다.

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에 대해 순차 스캔을 수행합니다. 일치하는 모든 행을 구체화합니다. 그다음 바깥 쿼리가 구체화 이후에 status = 'pending' 필터를 적용합니다. (created_at, status)에 대한 복합 인덱스가 있더라도, 플래너는 두 조건을 결합하기 위해 CTE 경계를 넘어 볼 수 없기 때문에 이를 사용할 수 없습니다.

왜 이렇게 설계되었을까요? 이유는 두 가지였습니다. 첫째는 스냅샷 격리입니다. CTE를 구체화하면 몇 번 참조하든 결과 집합이 하나의 스냅샷에서 한 번만 계산된다는 보장이 생겼습니다. 둘째는 부작용이 있는 엣지 케이스를 보호하기 위해서였습니다. CTE 안에 데이터 변경 문(INSERT, UPDATE, DELETE)이 들어 있으면, 구체화를 통해 그것이 정확히 한 번만 실행되도록 보장할 수 있었습니다.

이 문제에 대한 우회 방법은 커뮤니티에서 잘 알려져 있었습니다. CTE를 서브쿼리로 다시 쓰는 것이었습니다. 서브쿼리는 원래부터 플래너의 일반적인 최적화 규칙, 즉 predicate pushdown과 인라인 대상이었습니다. 같은 쿼리를 SELECT * FROM (SELECT * FROM orders WHERE created_at > '2025-01-01') sub WHERE status = 'pending'로 작성하면 훨씬 더 나은 계획이 나왔습니다.

그 결과 일종의 우회 문화가 생겨났습니다. 개발 중에는 가독성을 위해 CTE로 쿼리를 작성하고, 운영 환경에서는 중첩된 서브쿼리로 다시 작성하는 식이었습니다. 커뮤니티에는 이런 말이 있었습니다. CTE는 최적화 장벽이다. 너무 자주 반복된 말이었습니다. 지금도 많은 개발자가 그렇게 믿고 있습니다. 하지만 PostgreSQL 12 이후로는 더 이상 사실이 아닙니다.

PostgreSQL 12: CTE 인라인

PostgreSQL 12에서는 자동 CTE 인라인이 도입되었습니다. 재귀가 아니고, 부작용이 없고, 한 번만 참조되는 CTE는 이제 기본적으로 인라인됩니다. 플래너는 이를 서브쿼리처럼 취급하며, 평소의 모든 최적화를 적용하기 시작했습니다. predicate pushdown, 인덱스 사용, 조인 재정렬이 마치 CTE 문법이 전혀 없었던 것처럼 그대로 적용됩니다.

이전 섹션의 같은 쿼리는 이제 완전히 다른 계획을 만듭니다.

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))

계획에서 CTE가 완전히 사라졌습니다. 두 조건 모두 orders에 대한 단일 스캔으로 합쳐졌습니다. 적절한 인덱스가 있다면 플래너는 그것을 사용할 수 있습니다. CTE 문법 자체는 실행 계획을 바꾸지 않습니다.

PostgreSQL 12는 또한 플래너의 결정을 재정의할 수 있는 두 개의 새 키워드를 도입했습니다.

• MATERIALIZED - 플래너가 인라인할 상황에서도 CTE를 강제로 구체화합니다
• NOT MATERIALIZED - 플래너가 구체화할 상황에서도 강제로 인라인합니다

-- 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는 언제 구체화될까?

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

가장 단순하고 가장 흔한 경우입니다. CTE를 정확히 한 번 참조하고 부작용이 없다면, 플래너는 이를 인라인합니다.

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)

두 조건이 모두 병합됩니다. 플래너는 orders에 대한 모든 접근 경로를 직접 고려합니다.

사례 2: 다중 참조 (MATERIALIZED)

CTE가 두 번 이상 참조되면 플래너는 이를 구체화합니다. 이것은 실제로 기능입니다. 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는 반복 사이에 작업 테이블을 유지해야 합니다. 이를 인라인하는 방법은 없습니다. 재귀는 글 후반부에서 자세히 다루겠습니다.

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는 항상 구체화됩니다. 부작용은 예측 가능한 순서로 정확히 한 번 실행되어야 합니다.

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)

count(*)가 실행되기 전에 DELETE가 완전히 수행되어야 하므로 CTE Scan이 존재합니다.

사례 5: VOLATILE 함수 (MATERIALIZED)

CTE에 VOLATILE 함수가 포함되어 있으면, 플래너는 함수가 여러 번 평가되어 서로 다른 결과를 내는 일을 막기 위해 CTE를 구체화합니다.

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이므로 구체화가 강제됩니다.

사례 6: STABLE 함수 (INLINED)

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

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은 없습니다. 플래너는 사례 1과 마찬가지로 CTE를 인라인하고 두 조건을 병합합니다. now()는 트랜잭션 내에서 같은 값을 반환하므로 STABLE입니다. 그리고 플래너의 인라인 검사에서는 VOLATILE 함수만 확인합니다(contain_volatile_functions()를 통해). 따라서 STABLE은 이 검사를 통과합니다.

왜 사람들은 STABLE이 인라인을 막는다고 생각할까요? PostgreSQL 12 이전에는 변동성과 상관없이 모든 CTE가 구체화되었기 때문입니다. PG 12가 인라인을 도입했을 때 함수 수준의 유일한 장벽은 VOLATILE이었습니다. 하지만 “CTE는 최적화 장벽이다”라는 오래된 사고방식이 너무 깊이 자리 잡고 있어서 많은 개발자가 STABLE도 문제라고 가정했습니다. 그렇지 않습니다.

실제로 인라인을 막는 함수는 clock_timestamp()입니다. now()와 달리 이것은 VOLATILE이며 호출할 때마다 다른 값을 반환합니다. clock_timestamp()가 들어 있는 CTE는 구체화됩니다. 마찬가지로 random()과 nextval()도 VOLATILE이어서 구체화를 강제합니다(사례 5에서 보았듯이).

now()가 있는 CTE가 구체화되는 것을 본다면, 원인은 다른 데 있습니다. 다중 참조이거나, 데이터 변경 문이 있거나, 명시적인 MATERIALIZED 힌트가 있는 경우입니다. STABLE을 탓하지 마십시오.

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

플래너의 결정은 언제든지 재정의할 수 있습니다.

-- 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가 구체화되었다면 orders 테이블에 대한 단일 스캔과, 그 결과에 대한 두 개의 CTE Scan이 보였을 것입니다. 하지만 여기서는 플래너가 이 쿼리를 두 개의 서브쿼리를 표준 조인한 것처럼 취급했습니다.

여러 번 참조되는 CTE에 NOT MATERIALIZED를 사용할 때는 주의하십시오. 인라인을 강제하면 서브쿼리는 참조마다 한 번씩 실행됩니다. 위 예제에서는 orders 테이블이 두 번 스캔됩니다. 한 번은 a용, 한 번은 b용입니다. 결과 집합이 작다면 괜찮을 수 있습니다. 크다면 작업량이 두 배가 됩니다. 사용하기 전에 반드시 측정하십시오.

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

행 잠금 절은 한 번만 참조되고 부작용이 없는 CTE라도 구체화를 강제합니다. 내부적으로 플래너의 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은 구체화를 확인해 줍니다.

판단 매트릭스

PostgreSQL 버전별 전체 그림은 다음과 같습니다.

통계 블랙홀

PostgreSQL Statistics: Why queries run slow에서 언급했듯이, 구체화된 CTE는 “통계가 가지 않는 곳” 중 하나입니다. 실무적으로 보면 이것이 아마 CTE 구체화의 가장 큰 문제일 것입니다.

플래너가 CTE를 구체화하면 결과 집합은 임시 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에서 실제 통계를 읽어와 기본값이 아니라 실제 데이터 분포를 바탕으로 추정치를 만들었을 것입니다.

단순한 쿼리에서는 이것이 큰 차이를 만들지 않을 수 있습니다. 하지만 구체화된 CTE가 조인으로 이어지면, 기본값 기반 추정이 연쇄적으로 번질 수 있습니다. 플래너가 해시 조인보다 중첩 루프를 선택할 수도 있고, 그 반대일 수도 있습니다. 메모리 필요량을 과소평가해서 예기치 않게 디스크로 스필할 수도 있습니다.

PG 17: 통계 전파

PostgreSQL 17은 구체화된 CTE에 대해 두 가지 중요한 개선을 가져왔습니다.

컬럼 통계 전파. 플래너가 CTE Scan 노드를 만들 때, 이제 기반 쿼리의 컬럼 통계를 스캔 노드로 전파합니다. 이것은 원본 테이블의 n_distinct, MCV 목록, 히스토그램이 CTE 스캔의 추정에 반영될 수 있음을 뜻합니다.

경로 키 전파. 이제 구체화된 CTE는 정렬 순서 정보를 보존합니다. CTE의 서브쿼리가 정렬된 출력을 생성하면, 플래너는 이를 알고 이후 단계에서 중복 정렬을 건너뛸 수 있습니다.

이러한 개선은 추정 격차를 크게 줄여 주지만, 완전히 없애지는 못합니다. 계획 정확도 측면에서는 인라인된 CTE가 여전히 더 낫습니다. 플래너가 전파된 복사본이 아니라 기반 테이블의 통계를 직접 사용하기 때문입니다. CTE를 굳이 구체화할 필요가 없다면, 강제하지 마십시오.

구체화가 도움이 될 때

구체화가 항상 나쁜 것은 아닙니다.

다중 참조. CTE 결과가 여러 곳에서 사용되면, 구체화는 그것을 한 번만 계산합니다. 구체화가 없으면 서브쿼리가 참조마다 한 번씩 실행됩니다.

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는 둘 다 구체화된 결과를 읽습니다. 구체화가 없다면 전체 집계가 두 번 실행되었을 것입니다.

비싼 VOLATILE 표현식. CTE에 volatile 함수 호출이나 값비싼 계산이 포함되어 있으면, 구체화는 그것들이 정확히 한 번만 실행되도록 보장합니다.

데이터 변경 작업. 쓰기 가능한 CTE의 핵심 목적은 부작용이 한 번 발생하고, RETURNING 데이터가 아래 단계에서 사용 가능해지는 것입니다. 여기서는 구체화가 선택 사항이 아닙니다.

인라인만으로는 충분하지 않을 때

도입부에서 말한 명령형 사고방식, 즉 “먼저 이걸 하고, 그다음 저걸 하자”는 CTE가 인라인된다고 해서 사라지지 않습니다. 그리고 이것은 개인적으로 제가 가장 좋아하는 주제이자, 쿼리 리팩터링 일이 끊임없이 생기는 원천입니다.

개발자들은 여전히 쿼리를 순차적 파이프라인처럼 구성하며, 그 구조 자체가 구체화와는 무관한 성능 문제를 만들 수 있습니다.

흔한 패턴은 조립 라인처럼 쿼리를 만드는 것입니다. 첫 번째 CTE가 행을 필터링하고, 다음 CTE가 관련 테이블을 LEFT JOIN한 뒤 GROUP BY로 메타데이터를 집계하고, 그다음 CTE가 집계된 결과를 기준으로 다시 필터링합니다. 읽기에는 깔끔한 파이프라인처럼 보이지만, 중간의 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는 한 번씩만 참조되므로 모두 인라인됩니다. 구체화도 없고, 최적화 장벽도 없습니다. 플래너는 전체 쿼리를 봅니다. 그런데 무엇이 문제일까요?

order_metadata 안의 GROUP BY입니다. 인라인 이후에도 플래너는 was_archived = false 조건을 집계 아래로 밀어 넣을 수 없습니다. 먼저 모든 필터링된 주문을 orders_archive와 LEFT JOIN하고, orders와 자기 조인을 수행하고, 그 모든 행에 대해 집계를 계산한 뒤에야, 조건에 맞지 않는 행을 버릴 수 있습니다. recent_orders가 50,000행을 반환하는데 실제로 아카이브된 것이 200행뿐이라면, 49,800행에 대해 불필요한 조인과 집계를 하고 있는 셈입니다.

해결책은 집계 후 필터링 패턴을 상관 서브쿼리 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는 첫 번째 일치 행을 찾으면 바로 중단합니다. 플래너는 created_at > '2024-01-01'을 orders에 대한 인덱스 스캔까지 끝까지 밀어 넣을 수 있고, 그다음 결과마다 관련 테이블을 조회합니다. 집계도 없고, 낭비되는 작업도 없습니다.

경험칙: CTE에 GROUP BY가 있거나, 단지 불리언 값(“이 행에 관련 데이터가 있는가?”)을 계산하려고 LEFT JOIN을 사용하고 있다면, 플래너가 넘어서 볼 수 없는 벽을 만든 것입니다. 상관된 EXISTS를 사용하면 플래너가 필터를 아래로 밀어 넣고 스캔을 일찍 멈출 수 있습니다. 이것은 CTE가 구체화되었는지 인라인되었는지와 무관하게 적용됩니다.

쓰기 가능한 CTE (강력함과 함정)

데이터 변경 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)

이 쿼리는 오래된 취소 주문을 삭제하고, 아카이브 테이블로 옮긴 뒤, 몇 건이 아카이브되었는지 셉니다. 모두 하나의 원자적 문장 안에서 이루어지므로, 애플리케이션 수준의 조정이 필요 없습니다.

하지만 날카로운 모서리도 있습니다.

방금 쓴 것을 읽을 수 없다

데이터 변경 CTE 안의 모든 하위 문장은 같은 스냅샷을 봅니다. 즉, 하나의 CTE 효과는 대상 테이블 을 읽는 다른 CTE나 메인 쿼리에는 보이지 않습니다. 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) 쿼리는 삽입 이전의 스냅샷을 봅니다. 삽입된 데이터가 필요하다면, 테이블을 다시 읽지 말고 ins CTE의 RETURNING 절을 사용해야 합니다.

튜플 옮기기

흔한 패턴은 쓰기 가능한 CTE를 사용해 행을 테이블 간에 원자적으로 이동하는 것입니다.

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

이 쿼리는 스테이징 테이블의 모든 행을 삭제하고, 그것들을 운영 테이블에 삽입하는 작업을 하나의 원자적 연산으로 수행합니다. 데이터가 두 테이블 모두에 있거나 어느 쪽에도 없는 순간이 없습니다.

데이터 변경 CTE는 문장 전체에 대해 병렬 쿼리를 비활성화합니다. 읽기와 쓰기를 섞은 복잡한 쿼리라면, 쓰기 CTE 때문에 읽기 전용 부분조차 병렬성이 막힙니다.

재귀 CTE는 항상 구체화된다

재귀 CTE는 반복적인 작업 테이블 메커니즘을 사용합니다. 이름과 달리 이것은 진짜 재귀는 아닙니다. PostgreSQL은 끝나지 않은 쿼리의 중첩 스택을 만들며 “자기 자신을 호출”하지 않습니다. 대신 반복문으로 동작합니다.

1. 비재귀 항(“seed”)을 실행합니다. 결과를 작업 테이블에 넣습니다.
2. 작업 테이블을 입력으로 사용해 재귀 항을 실행합니다. 새 행은 다음 작업 테이블이 됩니다.
3. 재귀 항이 더 이상 새 행을 반환하지 않을 때까지 반복합니다.
4. 모든 반복의 합집합을 반환합니다.

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

재귀 CTE에서 UNION과 UNION ALL의 선택은 일반 쿼리보다 더 중요합니다.

UNION ALL은 중복을 포함해 모든 행을 유지합니다. 이것은 더 빠르지만 위험합니다. 그래프에 사이클이 있으면 재귀가 끝나지 않습니다. PostgreSQL은 쿼리를 취소하거나 메모리가 고갈될 때까지 계속 실행합니다.

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

PostgreSQL 14는 순회 순서(너비 우선 대 깊이 우선)를 제어하고 사이클을 감지하기 위한 수작업 패턴을 대체하는 SQL 표준 SEARCH 및 CYCLE 절을 추가했습니다. SEARCH BREADTH FIRST BY / SEARCH DEPTH FIRST BY는 순서를 제어하고, CYCLE은 사이클을 자동으로 감지하고 표시하므로, 방문한 ID 배열을 누적하는 오래된 패턴보다 훨씬 깔끔합니다.

순수한 계층형 데이터(사이클이 없는 트리)라면 재귀 CTE의 대안으로 ltree 확장을 고려할 수 있습니다. 이것은 전체 경로를 라벨 트리로 저장하고, GiST 인덱스를 통해 효율적인 조상/자손 질의를 지원합니다. 대가로 정규화되지 않은 저장과 즉석 재귀 사이의 절충이 생깁니다.

이색적인 엣지 케이스

파티션 프루닝 손실

파티션된 테이블 위의 CTE를 구체화하면, CTE 스캔 쪽에서는 파티션 프루닝이 일어날 수 없습니다. 구체화된 결과는 파티션 메타데이터와 분리된 평평한 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' 조건은 구체화 이후에 적용됩니다. CTE를 만들기 위해 모든 파티션을 스캔하게 되며, 실제로는 한두 개만 필요할 수도 있습니다. 파티션 프루닝을 유지하려면 NOT MATERIALIZED를 사용하거나, 그냥 플래너가 인라인하도록 두십시오.

Prepared statement와 계획 캐싱

PostgreSQL은 prepared statement의 처음 5회 실행에 대해서는 custom plan을 생성합니다. 그 이후에는 generic plan으로 전환할 수 있습니다. generic plan은 실제 파라미터 값을 모르기 때문에, CTE 인라인 결정이 custom plan과 generic plan 사이에서 달라질 수 있습니다.

즉 처음 5번 호출에서는 인라인되던 CTE가 6번째부터 구체화되기 시작할 수도 있고, 그 반대도 가능합니다. prepared statement에서 갑작스러운 계획 변화를 본다면, CTE 인라인 동작이 바뀌었는지 확인하십시오.

work_mem 스필링

구체화된 CTE는 work_mem 한도 내에서 결과를 메모리에 저장합니다. 결과 집합이 이 한도를 넘으면, 조용히 임시 파일로 디스크에 스필합니다. 이것은 오류가 아닙니다. 단지 더 느려질 뿐입니다.

log_temp_files = 0(모든 임시 파일을 로깅)으로 모니터링하거나, EXPLAIN (ANALYZE, BUFFERS)에서 임시 읽기/쓰기 횟수를 확인하십시오.

PostgreSQL 18: 이제 EXPLAIN이 메모리/디스크 사용량을 보여 준다

PostgreSQL 18부터 EXPLAIN ANALYZE는 CTE 구체화를 포함한 Material 노드의 메모리 및 디스크 사용량을 보고합니다. 구체화된 CTE가 정확히 얼마나 많은 메모리를 사용했는지, 디스크로 스필했는지를 볼 수 있습니다.

CTE와 security barrier view

security_barrier 뷰는 바깥쪽 필터가 적용되기 전에 데이터베이스가 뷰 내부 로직을 완전히 해결하도록 강제하는 “블랙박스”입니다.

Security-barrier 뷰는 이미 보안 조치로서 서브쿼리 평탄화를 막습니다(사용자 정의 함수가 보면 안 되는 행을 보지 못하게 하기 위해서입니다). 여기에 CTE를 결합하면 최적화 장벽이 겹쳐집니다. 플래너는 뷰도 인라인할 수 없고 CTE도 인라인할 수 없습니다. 이런 시나리오에서 성능이 중요하다면, 보안에 민감한 필터링 결과를 먼저 임시 테이블로 구체화하는 방법을 고려하십시오.

CTE vs. 서브쿼리 vs. 임시 테이블

CTE, 한 번 참조 PG 12+에서는 플래너가 이를 인라인하므로 실행 계획에 영향을 주지 않습니다. 복잡한 쿼리를 나누는 기본 선택지입니다.

CTE, 여러 번 참조(작은 결과) 허용 가능한 비용을 나타냅니다. 구체화되므로 서브쿼리는 한 번만 실행됩니다. 몇백 행 정도를 생성하는 집계나 필터링된 부분집합이라면 오버헤드는 최소입니다.

Henrietta Dombrovskaya가 강조하듯이, “최고의 임시 테이블은 만들지 않은 테이블이다”. 임시 테이블을 꺼내 들기 전에 항상 인덱싱과 쿼리 재작성 옵션을 먼저 모두 시도하십시오. DDL 오버헤드가 실행 이득보다 더 큰 경우가 많기 때문입니다.

CTE, 여러 번 참조(큰 결과) 는 임시 테이블을 고려할 수 있는 경우입니다. 구체화된 CTE에는 인덱스도 통계도 없습니다. 임시 테이블은 둘 다 가질 수 있으며, Introduction to Buffers에서 다뤘듯이 임시 테이블은 더 단순한 잠금과 WAL 오버헤드가 없는 로컬 버퍼를 사용합니다. CTE에서 나온 100k+ 행과 조인하고 있다면, 임시 테이블을 만들고, 인덱스를 추가하고, ANALYZE를 수행하십시오. 데이터 변경 작업 은 쓰기 가능한 CTE를 사용하십시오. 원자적이고 단일 문장인 동작을 제공하는 다른 대안은 없습니다.

재귀 CTE. 순수 SQL에서는 대안이 없습니다.

인덱스/통계가 필요한 큰 중간 결과. 정말 필요하다면 임시 테이블을 사용하십시오. Reading Buffer statistics에서 설명했듯이, 임시 테이블은 인덱스, 통계, 버퍼 관리 등 구체화된 CTE가 갖지 못하는 완전한 플래너 지원을 제공합니다.

PG 18의 현재 상황

PostgreSQL 18은 혁명적인 변화 없이도 CTE 처리를 계속 다듬고 있습니다.

• EXPLAIN이 메모리/디스크 사용량을 보여 줍니다. 이제 CTE 구체화 노드가 work_mem 안에 들어갔는지, 디스크로 스필했는지 마침내 볼 수 있습니다.
• 같은 테이블 위의 CTE에 대해 더 나은 쿼리 계획. 여러 CTE가 같은 기반 테이블을 참조할 때 중복 스캔을 제거하는 데 플래너가 더 똑똑해졌습니다.
• 규칙 기반으로 정의된 갱신 가능 뷰에서의 데이터 변경 CTE 수정. 쓰기 가능한 CTE가 rules로 정의된 뷰와 잘못 상호작용하던 엣지 케이스가 해결되었습니다.
• CTE 인라인은 성숙해졌습니다. 핵심 인라인 로직은 PG 12 이후 바뀌지 않았습니다. 달라진 것은 그 주변입니다. 더 나은 통계 전파(PG 17), 더 나은 구체화 진단(PG 18), 더 나은 비용 추정이 추가되었습니다.

CTE는 좋은 도구입니다. 다만 언제 날카로운 쪽을 잡고 있는지만 알아두십시오.

boringSQL 뉴스레터 구독

최대 월 1회 이메일. 언제든지 구독 취소할 수 있습니다.

구독하기

• postgresql
• deep-dive
• sql

← 이전 pg_regresql: 진정으로 이식 가능한 PostgreSQL 통계

📅 예정된 행사

데이터베이스, SQL, 그리고 데이터에 관한 모든 것을 이야기하기 위해 다음 PostgreSQL 밋업에서 만나 보세요

• 21-22 Apr 26PostgreSQL Conference Germany 2026, Essen speaking
• 4-5 Jun 26PG Data Conference 2026, Chicago speaking
• 8 Sep 26PgDay UK 2026, London attending

지난 발표와 슬라이드 보기 →

© 2024 boringSQL, 사이트 제작 Clusterity s.r.o.; EU에서 Hetzner Cloud로 호스팅.