SECTION 08 원문 백업

재편집 전 원문 백업 | SECTION 08 | 2026-10-14

원본: SECTION 08. 정렬과 페이징 최적화

WITH는 SQL을 나누어 작성하는 문법이고, MATERIALIZED는 중간 결과의 구체화를 제어한다.

구체화는 반복 처리를 줄일 수도 있지만, 외부 조건의 전달을 차단해 대량 처리를 만들 수도 있다.

이 섹션에서는 PostgreSQL의 CTE(Common Table Expression), MATERIALIZED, NOT MATERIALIZED를 BIG 계열 실습 테이블로 학습한다. 목표는 기능 암기가 아니라 중간 결과를 언제 만들어 재사용하고, 언제 상위 쿼리와 통합해 최적화할지 판단하는 것이다.

실행계획과 근거 구분

•

아래 계획은 실제 실행·측정 결과가 아닌 예상 실행계획이다. 시간·cost·actual rows·Buffers 값을 임의로 만들지 않는다.

•

StartUPTuning_PG_20250616.pdf와 실습 SQL에는 WITH를 이용한 외부 조건 공급 예제가 있다. 여기서 AS MATERIALIZED와 AS NOT MATERIALIZED의 정확한 동작은 PostgreSQL 17 공식 문서 7.8.3을 추가 확인해 설명한다.

•

204건 튜닝 분류에서 MATERIALIZED 적용 14건이 확인되지만, 모든 사례의 원문 SQL·실행계획이 이 요약표에 기록되어 있지는 않다. 아래 BAD/GOOD SQL은 실측 사례의 원문 복사본이 아닌 교육용 재구성이다.

•

MATERIALIZED는 이 섹션에서는 CTE 구체화를 뜻한다. CREATE MATERIALIZED VIEW와 혼동하지 않는다.

이번 섹션에서 기억할 10가지 규칙

ID	구분	핵심 주제
08-01	MUST	WITH를 사용해도 항상 구체화되지는 않는다
08-02	TIP	여러 번 참조하는 중간 결과는 재사용을 검토한다
08-03	TIP	반복되는 복잡한 조인·집계를 구체화할 수 있다
08-04	CHECK	여러 번 참조하는 CTE의 조건 전달에는 NOT MATERIALIZED
08-05	MUST	구체화하기 전에 필요한 검색 조건을 넣자
08-06	TIP	구체화하기 전에 선집계해 결과 건수를 줄이자
08-07	MUST	LIMIT과 WHERE의 적용 순서를 임의로 바꾸지 말자
08-08	CHECK	구체화 이득과 조건 Pushdown 손실을 함께 비교하자
08-09	CHECK	CTE Scan·temp I/O로 구체화의 실제 비용을 확인하자
08-10	MUST	NOT MATERIALIZED의 제약과 Materialized View 차이를 알자

MUST: 작성 원칙 / TIP: 활용 후보 / CHECK: 계획·동등성 확인 필수

먼저 알아둘 동작 규칙

구문	PostgreSQL의 일반적인 처리
WITH c AS (...)	부작용 없는 비재귀 SELECT를 한 번 참조하면 대개 상위 SQL에 통합
동일 CTE를 여러 번 참조	기본적으로 구체화하여 결과를 재사용
WITH c AS MATERIALIZED (...)	CTE를 별도로 계산하도록 강제
WITH c AS NOT MATERIALIZED (...)	조건이 충족되는 경우 상위 SQL에 통합하도록 유도, 중복 계산 가능

전제: 통합 규칙은 재귀가 아니고 volatile 함수가 없는 부작용 없는 SELECT CTE에 대한 설명이다. NOT MATERIALIZED는 재귀 CTE나 부작용 있는 CTE에 대해 강제 통합을 보장하지 않는다. 출처: PostgreSQL 17 — CTE Materialization.

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

MUST · 상황: 회원 M00019의 2023년 3월 주문번호를 조회한다.

BAD 후보 — 불필요한 구체화를 강제

WITH orders AS MATERIALIZED (
  SELECT o.ord_no, o.mbr_id, o.ord_dtm
  FROM tr_ord_big AS o
)
SELECT ord_no, ord_dtm
FROM orders
WHERE mbr_id = 'M00019'
  AND ord_dtm >= TIMESTAMP '2023-03-01'
  AND ord_dtm < TIMESTAMP '2023-04-01';

SQL

복사

GOOD — CTE를 통합하거나 직접 WHERE를 작성

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

SQL

복사

예상 실행계획

BAD (강제 MATERIALIZED, 가능한 구조)
CTE orders
  -> Seq Scan on tr_ord_big   -- CTE에 WHERE 없음
CTE Scan on orders
  Filter: (mbr_id = 'M00019' AND ord_dtm 범위)
  => 넓은 결과를 먼저 만들고 나서 필터링 가능

GOOD (통합이 성공하고 x04 인덱스를 선택한 경우)
Index Scan using tr_ord_big_x04 on tr_ord_big
  Index Cond: (mbr_id = 'M00019' AND ord_dtm 범위)
  => 원본 테이블에서 바로 조건 검색 가능

Plain Text

복사

핵심: 단일 참조의 단순·부작용 없는 CTE는 보통 WITH orders AS (...)만 써도 통합된다. NOT MATERIALIZED를 의식적으로 명시해야만 빨라지는 것은 아니다.

주의: BAD의 전체 구체화가 반드시 모든 행을 끝까지 소비하는 것은 아니며 실제 요구량과 계획 형태에 따라 달라질 수 있다. 이 비교의 핵심은 최종 필터의 내부 전달 가능 여부다.

근거: PostgreSQL 17 공식 문서 7.8.3의 단일 참조 CTE 예제를 BIG 테이블로 재구성.

REMINDER 08-02. 여러 번 사용하는 중간 결과는 구체화를 검토하자

TIP · 상황: 2023년 3월 한 회원의 전체 주문 수와 픽업 완료 주문 수를 한 결과 행으로 조회한다.

BAD 후보 — 동일 주문 범위를 두 번 집계

SELECT
 (SELECT COUNT(*)
  FROM tr_ord_big o
  WHERE o.mbr_id = 'M00019'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01') AS all_cnt,
 (SELECT COUNT(*)
  FROM tr_ord_big o
  WHERE o.mbr_id = 'M00019'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
    AND o.ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

GOOD 후보 — 검색한 결과를 MATERIALIZED CTE로 재사용

WITH orders AS MATERIALIZED (
  SELECT o.ord_st
  FROM tr_ord_big o
  WHERE o.mbr_id = 'M00019'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT
 (SELECT COUNT(*) FROM orders) AS all_cnt,
 (SELECT COUNT(*) FROM orders WHERE ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

예상 실행계획

BAD (가능한 형태)
Result
  InitPlan 1 -> Aggregate -> 주문 인덱스 검색
  InitPlan 2 -> Aggregate -> 주문 인덱스 검색 (ord_st 필터)
  => 같은 기간을 두 번 검색할 가능성

GOOD (가능한 형태)
CTE orders
  -> Index Scan on tr_ord_big
       Index Cond: (mbr_id + ord_dtm 범위)
Result
  InitPlan 1 -> Aggregate -> CTE Scan on orders
  InitPlan 2 -> Aggregate -> CTE Scan on orders
       Filter: (ord_st = 'PKUP')
  => 원본 주문은 한 번 읽되 구체화 결과는 두 번 읽음

Plain Text

복사

핵심: 동일한 중간 결과를 여러 군데 사용한다면 원본 테이블 재탐색을 피할 수 있다.

주의: 이 예제는 CTE 재사용을 보여주기 위한 교육용 비교다. 실제로는 단일 COUNT(*) FILTER (WHERE ord_st = 'PKUP') 집계가 더 단순하고 효율적일 수 있다. 한 번 구체화하면 그 결과를 읽는 비용도 존재한다. 작은 인덱스 검색을 두 번 하는 편이 더 나을 수 있다.

근거: PostgreSQL 공식 문서 7.8.3의 다중 참조 재사용 규칙. 204건 집계의 MATERIALIZED 처리 분류와 연계.

REMINDER 08-03. 복잡한 조인·집계가 반복되면 작은 결과의 구체화를 검토하자

TIP · 상황: 두 회원의 2023년 3월 주문금액 합계를 하나의 결과 행에 표시한다. 주문과 회원을 조인한 집계를 두 번 계산할 필요가 있는지 검토한다.

BAD 후보 — 유사한 복잡한 집계를 두 번 작성

SELECT
 (SELECT SUM(o.ord_amt)
  FROM tr_ord_big o JOIN ms_mbr_big m ON m.mbr_id = o.mbr_id
  WHERE m.mbr_id = 'M00019'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01') AS amt_19,
 (SELECT SUM(o.ord_amt)
  FROM tr_ord_big o JOIN ms_mbr_big m ON m.mbr_id = o.mbr_id
  WHERE m.mbr_id = 'M00020'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01') AS amt_20;

SQL

복사

GOOD 후보 — 대상 회원만 미리 선별·집계하고 결과 재사용

WITH monthly AS MATERIALIZED (
  SELECT o.mbr_id, SUM(o.ord_amt) AS ord_amt
  FROM tr_ord_big o JOIN ms_mbr_big m ON m.mbr_id = o.mbr_id
  WHERE o.mbr_id IN ('M00019', 'M00020')
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
  GROUP BY o.mbr_id
)
SELECT
 (SELECT ord_amt FROM monthly WHERE mbr_id = 'M00019') AS amt_19,
 (SELECT ord_amt FROM monthly WHERE mbr_id = 'M00020') AS amt_20;

SQL

복사

예상 실행계획

BAD (가능한 형태)
InitPlan 1 -> Aggregate -> 회원 JOIN + 주문 검색
InitPlan 2 -> Aggregate -> 회원 JOIN + 주문 검색

GOOD (가능한 형태)
CTE monthly
  -> HashAggregate / GroupAggregate
       -> 회원 JOIN + 주문 검색 (대상 회원 2명)
Result
  -> InitPlan 1 -> CTE Scan on monthly (mbr_id = 'M00019')
  -> InitPlan 2 -> CTE Scan on monthly (mbr_id = 'M00020')

Plain Text

복사

핵심: 먼저 필요한 범위로 좁힌 뒤 중복 실행되는 조인·집계를 한 번 계산하여 재사용할 수 있다. 다만 이 사례처럼 각 회원이 인덱스로 매우 좁게 검색된다면 구체화 자체가 오히려 비용일 수 있다.

실제 튜닝 기록: 사례 0317_01에는 반복 발생한 복잡한 인라인 뷰 조인에 대해 단독 실행 결과가 작다는 점을 근거로 구체화 처리했다는 설명이 있다. 위 SQL은 그 원문이 아닌 개념 재구성이다.

REMINDER 08-04. 여러 번 참조하는 CTE에 각기 다른 조건을 공급하려면 NOT MATERIALIZED를 검토하자

CHECK · 상황: 2023년 3월, 두 회원 각각의 주문 건수를 구한다.

BAD 후보 — 월간 주문을 통째로 만들어 두 번 필터링

WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT
 (SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00019') AS cnt_19,
 (SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00020') AS cnt_20;

SQL

복사

GOOD 후보 — 참조 위치별 회원 조건을 원본 검색에 결합

WITH orders AS NOT MATERIALIZED (
  SELECT o.mbr_id, o.ord_no
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT
 (SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00019') AS cnt_19,
 (SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00020') AS cnt_20;

SQL

복사

예상 실행계획

BAD (가능한 형태)
CTE orders
  -> tr_ord_big의 3월 주문 집합 스캔
Result
  -> Aggregate -> CTE Scan on orders (mbr_id = 'M00019')
  -> Aggregate -> CTE Scan on orders (mbr_id = 'M00020')

GOOD (x04: mbr_id, ord_dtm 인덱스 선택 시)
Result
  -> Aggregate -> Index Only Scan / Index Scan
       Index Cond: (mbr_id = 'M00019' AND ord_dtm 범위)
  -> Aggregate -> Index Only Scan / Index Scan
       Index Cond: (mbr_id = 'M00020' AND ord_dtm 범위)
  => 원본을 두 번 조회하지만 각 조회 범위가 작음

Plain Text

복사

핵심: MATERIALIZED는 계산 재사용에 유리할 수 있지만 상위 조건의 개별 전달을 막는다. NOT MATERIALIZED는 중복 스캔을 감수하고도 각 참조의 검색 범위가 매우 작아질 때 후보가 된다.

주의: NOT MATERIALIZED가 항상 빠른 것은 아니다. 대상 회원이 많거나 월간 CTE 자체가 작으면 한 번 읽어 재사용하는 편이 나을 수 있다. 이 예제에서 둘 다 결과적으로 동일한 두 COUNT 값을 반환한다.

근거: PostgreSQL 17 공식 문서 7.8.3의 다중 참조 CTE와 NOT MATERIALIZED 예제를 BIG 테이블로 변환한 교육용 SQL.

REMINDER 08-05. MATERIALIZED 전에 업무상 필요한 조건을 내부에 넣자

MUST · 상황: 특정 회원 한 명의 3월 주문 목록을 CTE 형태로 읽는다.

BAD — 구체화 후에 회원을 필터링

WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT ord_no, ord_dtm
FROM orders
WHERE mbr_id = 'M00019';

SQL

복사

GOOD — 회원 조건과 기간 조건을 구체화 전에 적용

WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  WHERE o.mbr_id = 'M00019'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT ord_no, ord_dtm
FROM orders;

SQL

복사

예상 실행계획

BAD (가능한 형태)
CTE orders
  -> Index Scan / Seq Scan on tr_ord_big
       Index Cond 또는 Filter: (ord_dtm 범위)
CTE Scan on orders
  Filter: (mbr_id = 'M00019')

GOOD (x04 인덱스를 사용하는 경우)
CTE orders
  -> Index Scan using tr_ord_big_x04
       Index Cond: (mbr_id = 'M00019' AND ord_dtm 범위)
CTE Scan on orders
  => 작은 중간 결과가 생성될 수 있음

Plain Text

복사

핵심: 구체화가 필요하다면 구체화할 데이터 자체를 먼저 최소화하자. 필터 조건이 구체화 이후에만 적용되면 상위 쿼리의 조건을 원본 검색에 반영하기 어렵다.

주의: 단일 참조의 이 SQL은 실제로는 MATERIALIZED를 없애 인라인화하는 방식도 함께 검토해야 한다. 위 BAD/GOOD은 MATERIALIZED를 유지해야 하는 상황의 필터 배치 비교다.

현장 기록: 0806_04에는 일자 조건을 WITH 절 안으로 옮기면서 선집계를 적용했다는 메모가 있다. 해당 기록의 원문 SQL이 충분히 공개된 것은 아니므로 일반적인 조건 배치 원칙으로만 연결한다.

REMINDER 08-06. 구체화할 데이터는 가능하면 먼저 집계해 줄이자

TIP · 상황: 2023년 3월 주문을 회원별 건수·총금액으로 집계한다. MATERIALIZED를 유지해야 한다고 가정한다.

BAD — 상세 주문을 전부 구체화한 다음 집계

WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_amt
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT mbr_id, COUNT(*) AS ord_cnt, SUM(ord_amt) AS ord_amt
FROM orders
GROUP BY mbr_id;

SQL

복사

GOOD — 회원별 집계를 구체화 전에 수행

WITH monthly AS MATERIALIZED (
  SELECT o.mbr_id,
         COUNT(*) AS ord_cnt,
         SUM(o.ord_amt) AS ord_amt
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
  GROUP BY o.mbr_id
)
SELECT mbr_id, ord_cnt, ord_amt
FROM monthly;

SQL

복사

예상 실행계획

BAD (가능한 형태)
CTE orders
  -> 기간 내 상세 주문 행 검색
HashAggregate (mbr_id)
  -> CTE Scan on orders
  => 상세 주문 행을 중간 결과로 보관한 뒤 집계

GOOD (가능한 형태)
CTE monthly
  -> HashAggregate / GroupAggregate (mbr_id)
       -> 기간 내 주문 검색
CTE Scan on monthly
  => 회원 수만큼으로 줄어든 중간 결과를 읽음

Plain Text

복사

핵심: 이후 단계에서 그룹별 집계만 필요하다면 상세 수천·수만 행을 구체화하기보다 먼저 GROUP BY해 중간 결과의 크기를 줄일 수 있다.

주의: 동일한 SELECT 결과를 내는 비교이지만 MATERIALIZED를 꼭 사용할 필요가 없다면 CTE 제거·일반 GROUP BY도 평가해야 한다. 집계 자체에 HashAggregate 메모리 사용이 필요하며, 데이터 분포에 따라 성능은 달라진다.

실제 사례: 0423_16은 MATERIALIZED 대상에 GROUP BY를 적용해 중간 건수를 줄였다는 기록이 있다. 0806_04는 일자 조건 내부 적용과 선집계가 함께 기록되어 있다.

REMINDER 08-07. LIMIT 전후로 조건을 옮기면 결과가 달라질 수 있다

MUST · 상황: 회원 M00019의 최근 주문 100건을 조회한다.

BAD — 전체 주문 TOP 100을 먼저 구체화하고 회원으로 필터링

WITH latest AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  ORDER BY o.ord_dtm DESC, o.ord_no DESC
  LIMIT 100
)
SELECT ord_no, ord_dtm
FROM latest
WHERE mbr_id = 'M00019'
ORDER BY ord_dtm DESC, ord_no DESC;

SQL

복사

GOOD — 회원을 필터링하고 그 회원의 TOP 100을 구체화

WITH latest AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  WHERE o.mbr_id = 'M00019'
  ORDER BY o.ord_dtm DESC, o.ord_no DESC
  LIMIT 100
)
SELECT ord_no, ord_dtm
FROM latest
ORDER BY ord_dtm DESC, ord_no DESC;

SQL

복사

예상 실행계획

BAD (가능한 형태)
CTE latest
  -> Limit
       -> Sort / Index Scan (전체 주문 TOP 100)
CTE Scan on latest
  Filter: (mbr_id = 'M00019')
  => 전체 TOP 100에 속하지 않은 회원 주문은 누락됨

GOOD (적합한 회원·일시 정렬 인덱스가 있다면)
CTE latest
  -> Limit
       -> Index Scan (mbr_id = 'M00019', 일시 DESC)
CTE Scan on latest
  => 해당 회원의 최근 100건 조회

Plain Text

복사

핵심: WHERE → ORDER BY → LIMIT의 논리적 요구사항을 먼저 확정해야 한다. MATERIALIZED CTE의 경계를 가로질러 LIMIT 전후에 조건을 옮기면 SQL 자체의 의미가 달라질 수 있다.

주의: BAD와 GOOD은 동등한 최적화 전후 SQL이 아니다. BAD는 '전 회원의 최근 100건 중 이 회원의 주문'이라는 다른 요구사항에 맞는 쿼리다. 집계, DISTINCT, LIMIT 등의 경계 안팎으로 조건을 이동할 때는 결과 의미부터 확인한다.

근거: PostgreSQL CTE 구체화와 부모 조건 Pushdown 제한에 따른 일반적 의미를 이용한 교육용 추가 사례. 원본 튜닝 사례의 실측 근거는 아니다.

REMINDER 08-08. 작은 결과의 재사용과 큰 CTE의 구체화 비용을 함께 판단하자

CHECK · 상황: 2023년 전체 기간 중 특정 회원 두 명의 주문금액을 집계한다.

BAD 후보 — 외부 집합이 작은데 두 기간의 대량 주문을 각각 구체화

WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_dtm, o.ord_amt
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-01-01'
    AND o.ord_dtm < TIMESTAMP '2024-01-01'
)
SELECT m.mbr_id,
       SUM(orders.ord_amt) AS ord_amt
FROM ms_mbr_big m
JOIN orders ON orders.mbr_id = m.mbr_id
WHERE m.mbr_id IN ('M00019', 'M00020')
GROUP BY m.mbr_id;

SQL

복사

GOOD 후보 — 큰 중간 결과 대신 선택적 인덱스 검색 활용

WITH orders AS NOT MATERIALIZED (
  SELECT o.mbr_id, o.ord_dtm, o.ord_amt
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-01-01'
    AND o.ord_dtm < TIMESTAMP '2024-01-01'
)
SELECT m.mbr_id,
       SUM(orders.ord_amt) AS ord_amt
FROM ms_mbr_big m
JOIN orders ON orders.mbr_id = m.mbr_id
WHERE m.mbr_id IN ('M00019', 'M00020')
GROUP BY m.mbr_id;

SQL

복사

예상 실행계획

BAD (가능한 형태)
CTE orders
  -> 1년치 주문 스캔
Hash Join
  -> CTE Scan on orders
  -> 선택한 회원 2건
  -> Aggregate (mbr_id)

GOOD (최적화가 통합되고 복합 인덱스를 선택할 때)
HashAggregate / GroupAggregate
  -> Nested Loop
       -> 회원 2건
       -> Index Scan using tr_ord_big_x04
            Index Cond: (mbr_id = m.mbr_id AND ord_dtm 범위)
  => 처음부터 필요한 회원 주문만 검색

Plain Text

복사

핵심: 재사용할 결과가 작은가, 구체화 이전에 얼마나 줄일 수 있는가, 각 사용처의 필터가 매우 선택적인가를 함께 비교한다.

주의: 여기서는 CTE를 한 번만 참조하므로 GOOD은 NOT MATERIALIZED를 명시하지 않아도 기본적으로 통합될 수 있다. 이 사례는 구체화를 강제하는 습관을 경계하기 위한 비교다. 원래의 SELECT는 두 SQL 모두 같은 회원·기간 집계를 구한다.

자료 구분: 구체화 반복 방지의 실제 기록은 사례 0317_01; 큰 중간 결과로 인한 조건 전달 손실과 NOT MATERIALIZED는 PostgreSQL 17 공식 문서의 일반 동작에 기반한다.

REMINDER 08-09. CTE Scan과 temp I/O를 구분해서 확인하자

CHECK · 상황: MATERIALIZED를 적용했더니 CTE Scan이 나타났다. 이를 보고 디스크 I/O가 발생했다고 판단할 수 있을까?

BAD — EXPLAIN의 노드명만 보고 디스크 구체화라고 단정

EXPLAIN (COSTS OFF)
WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00019';

SQL

복사

GOOD — 실습 환경에서 Buffers와 실제 노드 반복을 확인

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
WITH orders AS MATERIALIZED (
  SELECT o.mbr_id, o.ord_no, o.ord_dtm
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
)
SELECT COUNT(*) FROM orders WHERE mbr_id = 'M00019';

SQL

복사

예상 실행계획

Aggregate
  -> CTE Scan on orders
       Filter: (mbr_id = 'M00019')
       CTE orders
         -> Index Scan / Seq Scan on tr_ord_big
              Filter 또는 Index Cond: (ord_dtm 범위)

실측 시에만 확인할 수 있는 항목
  actual rows / loops / Buffers: shared hit/read ...
  Buffers: temp read/written ...  ← 실제로 임시 I/O가 발생한 경우
  (위 줄은 값이 아닌 확인 항목 목록임)

Plain Text

복사

핵심: CTE Scan은 별도로 계산된 CTE 결과를 읽는다는 단서다. 하지만 그것만으로 임시 파일에 기록되었는지, 메모리에서 충분히 처리했는지 단정할 수는 없다.

주의: EXPLAIN ANALYZE는 SQL을 실제 실행한다. 이 문서에서는 측정하지 않았다. temp 읽기·쓰기가 보고되더라도 해당 SQL의 다른 Sort·Hash 연산이 원인일 수 있으므로 계획의 어느 노드에서 발생했는지 함께 확인해야 한다.

근거: PostgreSQL 17의 CTE 재사용·구체화 설명 및 일반 EXPLAIN/BUFFERS 동작을 바탕으로 작성한 점검 규칙.

REMINDER 08-10. NOT MATERIALIZED의 제약과 Materialized View의 차이를 알자

MUST · 상황: NOT MATERIALIZED를 쓰면 모든 CTE가 무조건 인라인화될 것이라고 오해한다.

BAD — volatile 함수가 있는 CTE도 강제로 인라인화된다고 가정

-- random()은 VOLATILE 함수이다.
WITH picked AS NOT MATERIALIZED (
  SELECT random() AS v
)
SELECT a.v AS v1, b.v AS v2
FROM picked AS a CROSS JOIN picked AS b;

SQL

복사

GOOD — 단일 값 재사용의 의도를 명확히 표현

WITH picked AS MATERIALIZED (
  SELECT random() AS v
)
SELECT v AS v1, v AS v2
FROM picked;

SQL

복사

예상 실행계획

BAD (NOT MATERIALIZED 지정이 통합을 보장하지 않음)
CTE picked
  -> Result (random() 평가)
Nested Loop
  -> CTE Scan on picked (a)
  -> CTE Scan on picked (b)

GOOD
CTE picked
  -> Result (random() 평가)
CTE Scan on picked
  => 동일한 v를 두 출력 컬럼으로 사용

Plain Text

복사

핵심: PostgreSQL의 NOT MATERIALIZED 통합 규칙은 비재귀·부작용 없는 SELECT에 적용되는 동작이다. volatile 함수를 포함한 CTE에는 요청한 인라인화가 적용되지 않을 수 있다.

주의: 위 BAD도 PostgreSQL에서 random()이 각 참조에서 독립 평가된다는 뜻이 아니다. 이 예시의 두 쿼리는 결과적으로 같은 난수 값을 두 번 출력할 수 있다. 잘못된 것은 SQL 결과라기보다 NOT MATERIALIZED의 강제력에 대한 오해다.

CTE의 MATERIALIZED와 Materialized View는 완전히 다르다

항목	WITH ... AS MATERIALIZED	CREATE MATERIALIZED VIEW
개념	하나의 SQL 문장 내부에서 CTE 계산을 별도로 처리	DB에 저장되는 별도 객체 생성
지속 범위	현재 SQL 실행에 속하는 중간 결과	객체를 삭제하기 전까지 유지
재사용	해당 쿼리 내 참조에서 재사용	다른 SQL에서도 조회 가능
갱신	각 쿼리 실행 시 필요에 따라 계산	별도로 REFRESH MATERIALIZED VIEW 수행
이번 섹션	교육 대상	개념 구분만 하고 객체 관리·갱신은 다루지 않음

근거: PostgreSQL 17 공식 문서 7.8.3. Materialized View는 별도 DB 객체이므로 여기의 CTE 구체화 사례 14건과 동일하다고 볼 근거는 없다.

개발자가 기억할 판단표

상황	먼저 검토할 방식	이유
한 번 참조하는 단순 WITH	기본 WITH(인라인화 가능)	외부 조건을 원본 인덱스 검색에 공급할 기회
한 번 참조하지만 별도 계산이 꼭 필요	MATERIALIZED 후보	중간 결과 계산 경계 명시
동일 중간 결과를 여러 번 사용	MATERIALIZED 후보	반복 계산 회피
여러 참조에서 각자 매우 다른 좁은 필터 사용	NOT MATERIALIZED 후보	각 참조의 조건 Pushdown
구체화할 상세 행이 너무 많음	필터·선집계 후 MATERIALIZED	중간 결과 크기 축소
CTE 안에 LIMIT·집계·DISTINCT가 있음	결과 동등성 우선 검토	조건 이동 시 의미 변경 가능
volatile 함수가 들어 있음	통합 제약 확인	NOT MATERIALIZED를 써도 통합 제한 가능

판단의 중심: 구체화 비용 + 이후 재사용 비용과 통합 후 중복 계산 비용 + 선택적 검색의 이익을 비교한다. 이것은 판단 개념이며 실제 계산 공식이나 측정값이 아니다.

최종 실행계획 점검 절차

-- 실제 인덱스 확인
SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename = 'tr_ord_big'
ORDER BY indexname;

-- SQL을 실행하지 않고 계획 노드 확인
EXPLAIN (COSTS OFF)
WITH x AS MATERIALIZED (
  SELECT o.mbr_id, SUM(o.ord_amt) AS amt
  FROM tr_ord_big o
  WHERE o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
  GROUP BY o.mbr_id
)
SELECT x.mbr_id, x.amt
FROM x
WHERE x.mbr_id = 'M00019';

SQL

복사

EXPLAIN에서 CTE·CTE Scan의 존재 여부, Index Cond와 Filter의 위치, 집계 전후의 예상 행 수를 비교한다. 이후 실습 환경이 확보되면 EXPLAIN (ANALYZE, BUFFERS)로 실제 행 수와 I/O를 측정한다.

개발 전 체크리스트

WITH를 썼다는 이유만으로 자동 구체화된다고 생각하지 않았는가?

CTE가 한 번 참조되는지 여러 번 참조되는지 확인했는가?

별도 계산·재사용이 필요한 경우에만 MATERIALIZED를 검토했는가?

구체화가 상위 WHERE 조건의 내부 전달을 차단하지 않는가?

여러 참조에서 작은 범위만 필요할 때 NOT MATERIALIZED도 비교했는가?

구체화할 중간 결과에 불필요한 컬럼·행이 들어 있지는 않은가?

GROUP BY를 먼저 적용해 구체화 행을 줄일 수 있는가?

LIMIT 전후로 필터를 이동하면서 결과의 의미가 달라지지 않았는가?

CTE Scan을 보고 무조건 디스크 I/O로 단정하지 않았는가?

결과 집합·NULL·중복·정렬 순서를 변경 전후 검증했는가?

참고 자료 및 출처 구분

기존 PostgreSQL 교재·실습에서 확인된 내용

•

StartUPTuning_PG_20250616.pdf p.238~243: 집계 인라인 뷰의 조건 공급, WITH로 필터된 회원 집합을 재사용, LATERAL 및 인라인 뷰 제거와 비교

•

StartUPTuning_PG_SQL_Script.sql — SQL-맛튜닝-조건공급2-3: WITH w1 AS (...)로 GOLD 회원 집합을 정의하고 JOIN·EXISTS에 재사용하는 실습. 여기에 MATERIALIZED 지정 구문이 쓰였다는 뜻은 아니다.

•

: MATERIALIZED 처리 14건. 특히 0317_01(복잡한 반복 조인), 0423_16(GROUP BY 후 구체화), 0806_04(날짜 조건 내부 적용·선집계)에서 개선 맥락을 확인. 그 외 상당수는 적용 기법명만 있으므로 효과를 단정하지 않는다.

공식 문서로 검증한 PostgreSQL 기능

•

PostgreSQL 17 — WITH Queries, 7.8.3 CTE Materialization: 단일·다중 참조 기본 동작, MATERIALIZED / NOT MATERIALIZED, 재귀·volatile 예외, 인라인화와 조건 Pushdown

교육용으로 별도 작성한 내용

•

본문 BAD/GOOD SQL 10개 규칙은 BIG 계열 테이블에 맞춘 예제 또는 비교 시나리오이며 204건의 원문 SQL을 그대로 옮긴 것은 아니다.

•

예상 실행계획은 기능·접근 방식의 차이를 이해하기 위한 개념도다. 실측 실행계획이나 검증 완료된 성능 향상률은 없다.

추후 그림 제작 메모 — 이미지 미삽입

•

그림 A · CTE 기본 동작: 한 번 참조한 CTE는 메인 SQL에 통합될 수 있고, 여러 번 참조한 CTE는 구체화될 수 있는 경로 비교

•

그림 B · MATERIALIZED vs NOT MATERIALIZED: 3월 주문 전체를 한 번 계산해 두 번 읽는 방식과, 회원별로 좁게 두 번 인덱스를 검색하는 방식 비교

•

그림 C · 구체화 전후 선집계: 주문 상세 N행을 구체화하는 BAD와 회원별 집계 M행만 구체화하는 GOOD의 데이터량 비교

•

그림 D · LIMIT과 WHERE 경계: 전체 TOP 100에서 회원 필터 vs 회원을 먼저 필터링한 TOP 100의 서로 다른 결과 강조

•

그림 E · 메모리와 임시 파일: CTE Scan을 해석할 때 temp I/O 존재 여부는 실제 Buffers에서 확인해야 한다는 점 시각화

SECTION 08 한 줄 요약: 작고 반복 사용하는 결과는 구체화를, 선택적으로 검색할 결과는 인라인화를 검토하되, 조건 전달과 결과의 정확성을 먼저 확인하자.