REMINDER 10-05. WITH를 사용한다고 무조건 구체화되는 것은 아니다

상황: 회원 M0001의 2023년 3월 주문번호와 주문일시를 조회한다. 기존 초안의 M00019는 일반 테이블에 해당 주문이 0건이므로 실제 주문이 31건 있는 M0001로 변경했다. 실습은 startdbpg.tr_ord 일반 테이블을 대상으로 한다.

BAD SQL — 필요하지 않은 MATERIALIZED로 전체 주문을 구체화

WITH orders AS MATERIALIZED (
    SELECT o.ord_no, o.mbr_id, o.ord_dtm
    FROM   startdbpg.tr_ord o
)
SELECT ord_no, ord_dtm
FROM   orders
WHERE  mbr_id = 'M0001'
  AND  ord_dtm >= TIMESTAMP '2023-03-01'
  AND  ord_dtm <  TIMESTAMP '2023-04-01'
ORDER BY ord_no;

SQL

복사

MATERIALIZED는 CTE 결과를 독립적으로 계산하게 만든다. 회원·기간 조건이 CTE 밖에 있어 원본 테이블의 인덱스 검색 조건으로 전달되지 못했다. 이번 실제 계획에서는 전체 주문 730,327건을 Seq Scan하고, CTE Scan에서 730,296건을 Filter로 제거했다.

GOOD SQL — 단순히 한 번 참조하는 CTE는 일반 WITH로 작성

WITH orders AS (
    SELECT o.ord_no, o.mbr_id, o.ord_dtm
    FROM   startdbpg.tr_ord o
)
SELECT ord_no, ord_dtm
FROM   orders
WHERE  mbr_id = 'M0001'
  AND  ord_dtm >= TIMESTAMP '2023-03-01'
  AND  ord_dtm <  TIMESTAMP '2023-04-01'
ORDER BY ord_no;

SQL

복사

이처럼 단일 참조하는 단순하고 부작용 없는 CTE는 PostgreSQL이 상위 쿼리와 통합(folding/inlining)할 수 있다. 실제로 tr_ord_x54(mbr_id, ord_dtm)를 사용해 31건만 인덱스로 조회했다.

비교 대안 — NOT MATERIALIZED를 명시

WITH orders AS NOT MATERIALIZED (
    SELECT o.ord_no, o.mbr_id, o.ord_dtm
    FROM   startdbpg.tr_ord o
)
SELECT ord_no, ord_dtm
FROM   orders
WHERE  mbr_id = 'M0001'
  AND  ord_dtm >= TIMESTAMP '2023-03-01'
  AND  ord_dtm <  TIMESTAMP '2023-04-01'
ORDER BY ord_no;

SQL

복사

NOT MATERIALIZED로 CTE 통합을 유도할 수도 있지만, 이 예제에서는 일반 WITH와 동일한 실행계획을 선택했다. 일부러 명시해야만 인덱스가 사용되는 것이 아니다.

실제 데이터 및 결과 동등성

•

startdbpg.tr_ord 전체 주문 730,327건, 회원 M0001의 2023년 3월 주문 31건.

•

세 방식 모두 31행을 반환했다. MATERIALIZED·NOT MATERIALIZED·일반 WITH 사이의 양방향 EXCEPT ALL 차이 0건.

•

회원과 날짜 조건이 있는 Index Cond를 사용한 것은 일반 WITH와 NOT MATERIALIZED 방식이다.

실제 실행계획 비교 — EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)

항목	BAD: MATERIALIZED	GOOD: 일반 WITH	대안: NOT MATERIALIZED
주문 접근	730,327건 Seq Scan	31건 Index Scan	31건 Index Scan
회원·기간 조건	CTE Scan Filter	Index Cond	Index Cond
Rows Removed by Filter	730,296	없음	없음
주문 인덱스	미사용	tr_ord_x54	tr_ord_x54
Buffers: shared hit	15,199 (추가 read 2,120)	37	37
임시 데이터 기록	temp written=3031	없음	없음
실행시간 (재측정)	675.764ms	0.450ms	0.410ms

위 실행시간은 캐시 상태의 각 1회 재측정값이다. 처음 실행에서는 MATERIALIZED 7,677.864ms, NOT MATERIALIZED 1.762ms, 일반 WITH 0.374ms였다. 캐시 및 시스템 부하가 크게 영향을 주므로 배율 자체보다 접근 범위·계획 차이를 보아야 한다.

BAD SQL — MATERIALIZED 실제 실행계획의 핵심 노드

Sort (rows=31 loops=1)
  Buffers: shared hit=15199 read=2120, temp written=3031
  CTE orders
    -> Seq Scan on tr_ord o (rows=730327 loops=1)
  -> CTE Scan on orders (rows=31 loops=1)
       Filter: (ord_dtm 기간 AND mbr_id = 'M0001')
       Rows Removed by Filter: 730296
Execution Time: 675.764 ms

Plain Text

복사

GOOD SQL — 및 NOT MATERIALIZED — 실제 동일한 형태의 실행계획

Sort (rows=31 loops=1)
  Sort Key: o.ord_no
  Buffers: shared hit=37
  -> Index Scan using tr_ord_x54 on tr_ord o
       (rows=31 loops=1)
       Index Cond: (mbr_id = 'M0001'
                    AND ord_dtm >= '2023-03-01'
                    AND ord_dtm < '2023-04-01')
Execution Time: 일반 WITH 0.450 ms / NOT MATERIALIZED 0.410 ms

Plain Text

복사

실제 계획의 노드·행 수·버퍼·실행시간을 발췌했으며 자료형 변환 등 부가 표기는 생략했다. 실행계획의 Buffers는 원본 테이블·CTE 등 전체 실행에 걸친 측정이다.

핵심

•

단일 참조 CTE에 MATERIALIZED를 무심코 붙이지 말자. 불필요한 구체화가 상위 WHERE 조건의 Pushdown을 막아 전체 데이터 읽기와 중간 결과 저장을 유발할 수 있다. 단순 CTE는 우선 일반 WITH로 작성하고 실행계획을 확인하자.

원본 규칙: 개편 전 08-01.

참고·예외

•

MATERIALIZED가 언제나 나쁜 것은 아니다. 중간 결과를 여러 번 재사용하거나 계산을 한 번으로 제한하려는 목적이 있다면 유용할 수 있다.

•

단순·부작용 없는 단일 참조 CTE의 통합은 PostgreSQL이 허용하는 최적화다. 재귀 CTE, 부작용이 있는 함수, 복수 참조 등의 경우는 달라질 수 있다.

•

MATERIALIZED CTE가 항상 전체 행을 끝까지 생성해야만 하는 것은 아니다. 상위 쿼리가 일부 행만 요구하면 필요한 만큼만 평가될 수도 있으나, 이번 쿼리는 전체 730,327건을 소비했다.

•

NOT MATERIALIZED는 여러 곳에서 참조하는 CTE에도 조건 전달을 허용하는 대신 계산을 중복시킬 수 있다. 이는 10-07에서 비교한다.