[PostgreSQL] CTE 최적화 울타리 WITH에 MATERIALIZED 붙였다가 느려질 때

설치·접속: PostgreSQL 설치와 접속

부제: 가독성 좋으라고 쓴 WITH 절이, 옵티마이저가 필터를 안으로 밀어넣지 못해 큰 테이블을 통째로 스캔하는 상황

-- 기본: PG12+ 는 한 번만 참조되는 CTE를 인라인 -> 필터가 안으로 밀려들어감
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
WITH recent AS (
  SELECT * FROM m7_big
)
SELECT * FROM recent WHERE id = 12345;

-- MATERIALIZED: 강제로 울타리를 세워 CTE 전체를 먼저 물리화
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
WITH recent AS MATERIALIZED (
  SELECT * FROM m7_big
)
SELECT * FROM recent WHERE id = 12345;

########## 기본: CTE가 인라인됨 -> 필터가 안으로 밀려 인덱스 스캔 ##########
 Index Scan using m7_big_pkey on m7_big (actual rows=1 loops=1)
   Index Cond: (id = 12345)
 Execution Time: 0.029 ms

########## MATERIALIZED: 울타리가 생겨 CTE 전체를 먼저 계산 -> seq scan ##########
 CTE Scan on recent (actual rows=1 loops=1)
   Filter: (id = 12345)
   Rows Removed by Filter: 199999
   CTE recent
     ->  Seq Scan on m7_big (actual rows=200000 loops=1)
 Execution Time: 16.912 ms    <- 약 580배 느림

PostgreSQL 12 이전에는 WITH가 항상 최적화 울타리(optimization fence)였다. CTE를 먼저 통째로 계산하고 그 결과에 바깥 조건을 나중에 적용했기 때문에, 위처럼 id=12345 필터가 CTE 안으로 못 들어가 20만 행을 전부 훑었다. PG12부터는 한 번만 참조되고 부작용 없는 CTE를 자동으로 인라인해서, 필터가 인덱스 스캔으로 밀려들어간다(0.029ms). 문제는 옛 습관대로, 혹은 명시적으로 MATERIALIZED를 붙이면 그 울타리가 되살아나 다시 seq scan으로 떨어진다는 것. 반대로 같은 CTE를 여러 번 참조하는데 매번 재계산이 비싸다면 MATERIALIZED로 한 번만 계산하게 고정하는 게 이득이다. 즉 MATERIALIZED는 "이 CTE 결과를 재사용·격리하라"는 의도적 지시이지, 그냥 붙이는 장식이 아니다.

이렇게도 쓴다

인라인을 명시적으로 강제한다(옵티마이저가 안 밀어줄 때). (조합: NOT MATERIALIZED)

WITH recent AS NOT MATERIALIZED (SELECT * FROM m7_big)
SELECT * FROM recent WHERE id = 12345;   -- 필터를 안으로 밀어넣음

 

같은 CTE를 여러 번 쓰고 재계산이 비싸면 물리화로 한 번만 계산한다. (조합: 다중 참조)

WITH heavy AS MATERIALIZED (SELECT ... 무거운 집계 ...)
SELECT * FROM heavy a JOIN heavy b ON ...;   -- heavy를 1회만 계산

 

무엇이 인라인/물리화됐는지 실행계획으로 확인한다. (조합: EXPLAIN)

EXPLAIN (ANALYZE) WITH x AS (SELECT ...) SELECT * FROM x WHERE ...;

 

재귀 CTE는 항상 물리화된다(인라인 대상 아님). (조합: RECURSIVE)

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM cat WHERE id=1
  UNION ALL SELECT c.id, c.parent_id FROM cat c JOIN tree t ON c.parent_id=t.id
) SELECT * FROM tree;

 

CTE 대신 서브쿼리로 바꿔 옵티마이저에 더 자유를 준다.

SELECT * FROM (SELECT * FROM m7_big) s WHERE s.id = 12345;

 

CTE 안에 DML을 넣어 부작용을 격리한다(이 경우 항상 물리화). (조합: WITH ... INSERT/UPDATE)

WITH moved AS (DELETE FROM m7_big WHERE id<10 RETURNING *)
INSERT INTO archive SELECT * FROM moved;