SECTION 10. 고급 SQL 활용 전략

상세 페이지

반복되는 조회를 줄이기 위한 LATERAL, CTE, MATERIALIZED의 사용과 제거를 비교한다. 기능 자체가 빠른 것이 아니라 외부 집합과 내부 접근 범위에 맞게 선택해야 한다.

난이도: 중급~중상급. SECTION 01~09를 이해한 다음 학습한다.

계획의 상태: 10-01·10-03·10-04·10-05·10-06은 실제 PostgreSQL 실행으로 검증했으며, 10-07 이후의 계획은 제공 교재·기존 원문에 근거한 예상·개념적 실행계획이다. 실측과 예상을 구분하고, 성능 개선을 단정하기 전에 인덱스·자료형·결과 동등성을 확인한다.

PART A. 필요한 행에 조건을 공급하는 고급 SQL

일반 JOIN·선집계만으로 불필요한 범위를 줄일 수 있는지 먼저 판단하고, LATERAL과 CTE를 도구로 활용한다.

REMINDER 10-01. 선별된 회원부터 JOIN하고 집계하자

상황: 2019년 3월 20일 SNS로 가입한 GOLD 회원의 2023년 상반기 주문금액 합계를 구한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord에서 세 방식의 결과와 실행계획을 검증했다.

BAD — 주문을 먼저 회원별로 집계하고 조건에 맞는 회원을 나중에 JOIN

SELECT m.mbr_id, m.nick_nm, a.ord_amt
FROM   startdbpg.ms_mbr m
JOIN (
    SELECT o.mbr_id, SUM(o.ord_amt) AS ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
      AND  o.ord_dtm <  TIMESTAMP '2023-07-01'
    GROUP BY o.mbr_id
) a
  ON a.mbr_id = m.mbr_id
WHERE m.mbr_gd = 'GOLD'
  AND m.join_dtm >= TIMESTAMP '2019-03-20'
  AND m.join_dtm <  TIMESTAMP '2019-03-21'
  AND m.join_tp = 'SNS'
ORDER BY m.mbr_id;

SQL

복사

GOOD — 조건에 맞는 회원을 JOIN해 주문을 제한한 후 집계

SELECT m.mbr_id, m.nick_nm,
       SUM(o.ord_amt) AS ord_amt
FROM   startdbpg.ms_mbr m
JOIN   startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
WHERE  m.mbr_gd = 'GOLD'
  AND  m.join_dtm >= TIMESTAMP '2019-03-20'
  AND  m.join_dtm <  TIMESTAMP '2019-03-21'
  AND  m.join_tp = 'SNS'
  AND  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-07-01'
GROUP BY m.mbr_id, m.nick_nm
ORDER BY m.mbr_id;

SQL

복사

비교 대안 — LATERAL로 선별된 회원별 주문금액 집계

SELECT m.mbr_id, m.nick_nm, a.ord_amt
FROM   startdbpg.ms_mbr m
JOIN LATERAL (
    SELECT SUM(o.ord_amt) AS ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.mbr_id = m.mbr_id
      AND  o.ord_dtm >= TIMESTAMP '2023-01-01'
      AND  o.ord_dtm <  TIMESTAMP '2023-07-01'
    HAVING COUNT(*) > 0
) a ON TRUE
WHERE  m.mbr_gd = 'GOLD'
  AND  m.join_dtm >= TIMESTAMP '2019-03-20'
  AND  m.join_dtm <  TIMESTAMP '2019-03-21'
  AND  m.join_tp = 'SNS'
ORDER BY m.mbr_id;

SQL

복사

LATERAL은 바깥쪽 m.mbr_id를 내부 집계에 공급한다. HAVING COUNT(*) > 0는 기간 내 주문이 없는 회원을 제외해 원래의 INNER JOIN 결과 의미를 유지한다. 이번 조건에서는 일반 JOIN보다 더 빠르지 않았다.

실제 데이터 및 결과 동등성 검증

•

조건에 해당하는 회원은 1명, M0427 (Mountain8)이다.

•

해당 회원의 2023년 상반기 주문은 6건, 금액 합계는 72,500이다.

•

같은 기간 전체 회원의 주문은 102,493건이며, 주문이 있는 회원은 9,688명이다.

•

세 방식 모두 (M0427, Mountain8, 72500.000) 동일한 1행을 반환한다.

•

세 방식의 결과를 양방향 EXCEPT ALL로 검증했을 때 차이 0건이다.

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

항목	BAD — 주문 선집계	GOOD — 일반 JOIN 후 집계	대안 — LATERAL 집계
집계 전 주문 접근 행 수	102,493	6	6
회원 필터 결과	1명	1명	1명
집계 방식	HashAggregate (9,688그룹)	GroupAggregate (1그룹)	Aggregate (1회)
조인 방식	Hash Join	Nested Loop	Nested Loop
주문 접근 인덱스	tr_ord_x51(ord_dtm)	tr_ord_x54(mbr_id, ord_dtm)	tr_ord_x54(mbr_id, ord_dtm)
Buffers: shared hit	1,647	144	144
실행시간 (재측정 각 1회)	93.742ms	2.013ms	2.146ms

다른 재측정에서 BAD 99.798ms, 일반 JOIN 2.081~2.233ms, LATERAL 2.146~2.298ms를 관찰했다. 일반 JOIN과 LATERAL은 버퍼 사용량이 같았으며, 이 미세한 실행시간 차이로 우열을 단정할 수 없다. 실제 소요시간은 캐시·부하·통계에 따라 변동한다.

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

Sort (rows=1 loops=1)
  Buffers: shared hit=1647
  -> Hash Join (rows=1 loops=1)
       Hash Cond: (o.mbr_id = m.mbr_id)
       -> HashAggregate (rows=9688 loops=1)
            Group Key: o.mbr_id
            Memory Usage: 4241kB
            -> Index Scan using tr_ord_x51 on tr_ord o
                 (rows=102493 loops=1)
                 Index Cond: (ord_dtm >= '2023-01-01'
                              AND ord_dtm < '2023-07-01')
       -> Hash
            -> Seq Scan on ms_mbr m (rows=1 loops=1)
                 Rows Removed by Filter: 9998
Execution Time: 93.742 ms

Plain Text

복사

실제 노드 구조와 행 수를 유지해 핵심만 발췌·축약했다. Memory Usage는 최초 계획에서 확인된 값이며, 최종 실행시간은 재측정 결과다.

GOOD — 실제 실행계획의 핵심 노드

GroupAggregate (rows=1 loops=1)
  Group Key: m.mbr_id
  Buffers: shared hit=144
  -> Sort (rows=6 loops=1)
       -> Nested Loop (rows=6 loops=1)
            -> Seq Scan on ms_mbr m (rows=1 loops=1)
                 Rows Removed by Filter: 9998
            -> Index Scan using tr_ord_x54 on tr_ord o
                 (rows=6 loops=1)
                 Index Cond: (mbr_id = m.mbr_id
                              AND ord_dtm >= '2023-01-01'
                              AND ord_dtm < '2023-07-01')
Execution Time: 2.013 ms

Plain Text

복사

위 Index Cond는 읽기 편하게 PostgreSQL의 자료형 변환 표기를 생략한 발췌다.

LATERAL — 실제 실행계획의 핵심 노드

Sort (rows=1 loops=1)
  Buffers: shared hit=144
  -> Nested Loop (rows=1 loops=1)
       -> Seq Scan on ms_mbr m (rows=1 loops=1)
            Rows Removed by Filter: 9998
       -> Aggregate (rows=1 loops=1)
            Filter: (count(*) > 0)
            -> Index Scan using tr_ord_x54 on tr_ord o
                 (rows=6 loops=1)
                 Index Cond: (mbr_id = m.mbr_id
                              AND ord_dtm >= '2023-01-01'
                              AND ord_dtm < '2023-07-01')
Execution Time: 2.146 ms

Plain Text

복사

위 계획은 실제 실행에서 주요 노드·행 수·버퍼만 발췌했으며, Index Cond의 자료형 표기를 생략했다.

왜 달라졌나? BAD의 집계 인라인 뷰는 실제 계획에서 2023년 상반기 전체 102,493건을 읽고 회원 9,688명으로 그룹화했다. 그 후에야 회원 1명과 JOIN했다. GOOD은 회원 조건을 먼저 적용하고, 기존 복합 인덱스 tr_ord_x54(mbr_id, ord_dtm)를 통해 필요한 주문 6건만 읽고 집계했다. LATERAL 역시 같은 인덱스로 6건만 읽어 비슷한 계획과 성능을 보였다. 새 인덱스는 생성하지 않았다.

핵심: 결과적으로 소수의 회원만 필요하다면, 전체 주문을 먼저 GROUP BY하지 말고 회원을 선별해 JOIN한 후 집계하자. LATERAL은 외부 회원 조건을 내부 집계에 전달하는 대안이지만, 일반 JOIN으로 같은 접근 범위를 구현할 수 있다면 필수는 아니다.

주의할 예외

•

세 방식 모두 INNER JOIN의 결과 의미를 가지므로 기간 내 주문이 없는 회원은 반환하지 않는다. 주문이 없는 회원도 나타내려면 LEFT JOIN을 포함해 결과 의미를 재설계해야 한다.

•

ms_mbr.mbr_id는 PK로 고유하다. 동일한 회원이 중복 행으로 JOIN되면 집계 의미가 달라질 수 있다.

•

PostgreSQL은 SQL의 작성 순서 그대로 실행하지 않는다. 이 사례에서는 실제로 BAD의 전체 선집계와 GOOD의 회원 우선 탐색이 선택됐지만 데이터 분포, 조건 선택성, 인덱스에 따라 다른 계획도 가능하다.

•

회원 조건의 선택성이 낮고 조회 대상이 많다면 선집계가 더 유리할 수 있다. LATERAL은 외부 행마다 내부 집계가 평가되므로 외부 회원 수가 많을 때 반복 접근 비용을 확인해야 한다.

•

LATERAL 집계에서 HAVING COUNT(*) > 0를 생략하면 주문이 없는 회원에 대해서도 SUM=NULL인 집계 결과 1행이 발생할 수 있어 일반 INNER JOIN과 결과가 달라진다.

원본 규칙: 개편 전 06-07(일반 JOIN), 07-02(LATERAL).

REMINDER 10-03. 그룹별 최신 1건은 LATERAL + LIMIT으로 찾자

상황: GOLD 회원 전체를 대상으로, 회원마다 가장 최근 주문 한 건의 주문번호와 주문금액을 조회한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord로 검증했다. 최신 주문일시가 같으면 주문번호(ord_no)가 큰 주문을 선택한다.

BAD — MAX(주문일시)로 시각만 구하고 다시 JOIN (최신 1건 보장 실패)

SELECT m.mbr_id, o.ord_no, o.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT mbr_id, MAX(ord_dtm) AS last_dtm
    FROM   startdbpg.tr_ord
    GROUP BY mbr_id
) last_o
  ON last_o.mbr_id = m.mbr_id
LEFT JOIN startdbpg.tr_ord o
  ON o.mbr_id = last_o.mbr_id
 AND o.ord_dtm = last_o.last_dtm
WHERE m.mbr_gd = 'GOLD'
ORDER BY m.mbr_id, o.ord_no DESC;

SQL

복사

MAX(ord_dtm)가 같은 주문이 여러 건이면 재JOIN 결과도 여러 건이다. 실제 GOLD 회원 120명에게 최신 일시 동률이 있어서 BAD는 3,266행을 반환했다. 이 SQL과 아래 GOOD의 결과는 동등하지 않다.

정상 비교안 — ROW_NUMBER로 회원별 최신 1건 선택

SELECT m.mbr_id, x.ord_no, x.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT o.mbr_id, o.ord_no, o.ord_amt,
           ROW_NUMBER() OVER (
               PARTITION BY o.mbr_id
               ORDER BY o.ord_dtm DESC, o.ord_no DESC
           ) AS rn
    FROM   startdbpg.tr_ord o
    JOIN   startdbpg.ms_mbr ms
      ON   ms.mbr_id = o.mbr_id
    WHERE  ms.mbr_gd = 'GOLD'
) x
  ON x.mbr_id = m.mbr_id
 AND x.rn = 1
WHERE m.mbr_gd = 'GOLD'
ORDER BY m.mbr_id;

SQL

복사

회원별로 ord_dtm DESC, ord_no DESC 정렬 순위의 첫 행을 선택한다. LATERAL과 완전히 같은 업무 결과를 내는 정상적인 대안이므로 공정한 성능 비교 대상으로 사용했다.

GOOD — 회원별 LATERAL에서 최신 한 건만 조회

SELECT m.mbr_id, x.ord_no, x.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN LATERAL (
    SELECT o.ord_no, o.ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.mbr_id = m.mbr_id
    ORDER BY o.ord_dtm DESC, o.ord_no DESC
    LIMIT 1
) x ON TRUE
WHERE m.mbr_gd = 'GOLD'
ORDER BY m.mbr_id;

SQL

복사

먼저 GOLD 회원을 찾고, 각 회원의 주문 인덱스 구간에서 최신 한 건만 선택한다. LEFT JOIN LATERAL이므로 주문이 없는 GOLD 회원도 NULL 주문번호·금액으로 유지한다.

데이터와 결과 검증

•

ms_mbr의 GOLD 회원 3,146명, 해당 회원 전체 주문 270,123건.

•

실제 주문이 존재하는 GOLD 회원은 3,124명이며, 주문이 없는 회원 22명도 최종 결과에 남는다.

•

120명은 가장 최근 ord_dtm에 주문이 2건씩 있어, 단순 MAX + JOIN 방식의 행 수가 3,266건으로 증가한다.

•

ROW_NUMBER와 LATERAL은 모두 3,146행, 양방향 EXCEPT ALL 검증에서 차이 0건이다.

•

tr_ord.ord_dtm은 실제 데이터에서 NULL 0건이므로 정렬 SQL에 NULLS LAST를 사용하지 않았다.

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

항목	BAD: MAX + JOIN	정상 대안: ROW_NUMBER	GOOD: LATERAL + LIMIT
결과 행 수	3,266 (동률 중복)	3,146	3,146
주요 처리	전체 주문 MAX 집계 + 재JOIN	회원 주문 270,123건에 WindowAgg	회원별 인덱스 역방향 탐색 + Limit
주문 처리 대상	전체 주문 730,327건 집계	GOLD 주문 270,123건	Limit 3,146회, 인덱스 최상단 소수 행
Shared Buffer Hit	15,473	248,367	15,587
실행시간 (재측정)	288.970ms	746.864ms	55.754ms

캐시가 준비된 상태의 각각 1회 실행시간이다. 다른 재측정에서도 MAX+JOIN 303.060ms, ROW_NUMBER 718.775ms, LATERAL 55.869~56.634ms였다. 동률 처리 요건이 다른 MAX+JOIN은 속도 우열을 직접 비교하기 위한 동등 SQL이 아니다.

BAD 핵심 실행계획 — 원래 MAX + JOIN

Incremental Sort (rows=3266 loops=1)
  Buffers: shared hit=15473
  -> Nested Loop Left Join (rows=3266 loops=1)
       -> Merge Left Join (rows=3146 loops=1)
            -> Index Scan using ms_mbr_pk on ms_mbr m
                 (rows=3146 loops=1)
            -> Finalize GroupAggregate (rows=9951 loops=1)
                 -> Gather Merge
                      -> Partial GroupAggregate
                           -> Parallel Index Only Scan using tr_ord_x54
                                (rows=365164 loops=2)
       -> Index Scan using tr_ord_x54 on tr_ord o
            (rows=1 loops=3146)
Execution Time: 288.970 ms

Plain Text

복사

두 worker 처리의 rows=365164 loops=2는 실제 실행계획의 노드별 표기이며, 전체 주문 730,327건을 병렬로 접근했다.

ROW_NUMBER 정상 대안 핵심 실행계획

Merge Left/Right Join (rows=3146 loops=1)
  Buffers: shared hit=248367
  -> WindowAgg (rows=3124 loops=1)
       Run Condition: (row_number() OVER (?) <= 1)
       -> Incremental Sort (rows=270123 loops=1)
            Sort Key: o.mbr_id, o.ord_dtm DESC, o.ord_no DESC
            -> Nested Loop (rows=270123 loops=1)
                 -> GOLD 회원 검색 (rows=3146 loops=1)
                 -> Index Scan using tr_ord_fk01 on tr_ord o
                      (rows=86 loops=3146)
Execution Time: 746.864 ms

Plain Text

복사

GOOD 핵심 실행계획 — LATERAL + LIMIT

Nested Loop Left Join (rows=3146 loops=1)
  Buffers: shared hit=15587
  -> GOLD 회원 검색 (rows=3146 loops=1)
  -> Limit (rows=1 loops=3146)
       -> Incremental Sort (rows=1 loops=3146)
            Sort Key: o.ord_dtm DESC, o.ord_no DESC
            Presorted Key: o.ord_dtm
            -> Index Scan Backward using tr_ord_x54 on tr_ord o
                 (rows=2 loops=3146)
                 Index Cond: (mbr_id = m.mbr_id)
Execution Time: 55.754 ms

Plain Text

복사

실제 노드 구조·행 수·버퍼·실행시간을 발췌했으며 PostgreSQL 자료형 변환 등 부가 표기는 생략했다. rows=2는 노드의 loops당 표시된 행 수이지 모든 회원이 반드시 정확히 두 행을 읽었다는 뜻은 아니다.

핵심 실행계획 해석: 이미 존재하는 tr_ord_x54(mbr_id, ord_dtm)를 이용해 회원 ID를 인덱스 조건으로 공급하고, 주문일시를 역방향으로 읽는다. 현재 인덱스 키에는 ord_no가 없으므로 동률 처리를 위한 Incremental Sort는 남지만, 최신 일시 근처의 소수 행만 처리한 후 LIMIT 1에서 멈춘다. 새 인덱스는 만들지 않았다.

핵심: 외부 집합의 각 키마다 정렬 기준상 상위 한 건이 필요하다면 LATERAL + ORDER BY ... LIMIT 1을 검토하자. 특히 외부 행이 상대적으로 적고, (외부 키, 정렬 키) 인덱스가 있으면 전체 그룹을 윈도 집계하거나 최댓값을 선집계하는 접근을 줄일 수 있다.

주의할 예외

•

MAX(주문일시) + JOIN은 동률 주문을 모두 반환할 수 있으므로 한 건 선택과 요구사항이 다르다. ord_no DESC 같은 유일한 동률 기준을 명시해야 한다.

•

GOLD 회원 3,146명에 대해 LATERAL 내부 탐색이 3,146회 수행되므로 항상 빠른 것은 아니다. 회원 수가 훨씬 많거나 정렬 지원 인덱스가 없으면 반복 탐색·정렬 비용이 커진다. 10-04에서 반대 조건을 검토한다.

•

다른 인덱스·통계·데이터 분포·캐시 상태에서는 ROW_NUMBER나 선집계가 유리할 수 있다. 여기서 검증된 수치를 일반적인 속도 배율로 해석해서는 안 된다.

•

실제 ord_dtm NULL은 0건이다. NULL이 생길 수 있는 업무에서는 PostgreSQL의 DESC 기본 NULL 정렬 위치가 FIRST임을 고려해야 한다.

원본 규칙: 개편 전 07-04.

REMINDER 10-04. 회원이 많다면 LATERAL 반복 집계보다 선집계를 검토하자

상황: 전체 회원 9,999명의 2023년 상반기(1월 1일~6월 30일) 주문금액 합계를 구한다. 회원별 집계가 필요하지만 대상 회원을 줄이는 별도 필터는 없다. 일반 테이블 startdbpg.ms_mbr와 startdbpg.tr_ord로 실제 실행계획과 결과를 검증했다.

BAD — 회원별 LATERAL 내부 집계가 반복 실행

SELECT m.mbr_id, m.nick_nm, x.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN LATERAL (
    SELECT SUM(o.ord_amt) AS ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.mbr_id = m.mbr_id
      AND  o.ord_dtm >= TIMESTAMP '2023-01-01'
      AND  o.ord_dtm <  TIMESTAMP '2023-07-01'
) x ON TRUE
ORDER BY m.mbr_id;

SQL

복사

LATERAL이 항상 나쁜 것은 아니다. 이 사례에서는 외부 회원 9,999명에 대해 내부 Aggregate와 주문 인덱스 조회가 9,999회 반복됐다.

GOOD — 기간 내 주문을 한 번에 집계한 뒤 회원과 LEFT JOIN

SELECT m.mbr_id, m.nick_nm, x.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT o.mbr_id,
           SUM(o.ord_amt) AS ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
      AND  o.ord_dtm <  TIMESTAMP '2023-07-01'
    GROUP BY o.mbr_id
) x ON x.mbr_id = m.mbr_id
ORDER BY m.mbr_id;

SQL

복사

상반기 주문을 한 번 읽어 회원별로 GROUP BY한 후 전체 회원과 JOIN한다. 실제 계획은 HashAggregate + Merge Left Join으로, 이전의 예상 Hash Join과 달랐다.

실제 데이터 및 결과 검증

•

전체 회원 9,999명, 기간 내 주문 102,493건, 기간 내 주문이 있는 회원 9,688명.

•

기간 내 주문이 없는 311명도 두 SQL 모두 반환하며, ord_amt는 NULL이다.

•

두 SQL의 출력은 각각 9,999행으로 같고, 양방향 EXCEPT ALL에서 차이 0건이었다.

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

항목	BAD — LATERAL 반복 집계	GOOD — 선집계 후 JOIN
외부 회원 접근	9,999행	9,999행
주문 검색	회원별 인덱스 조회 9,999회	2023년 상반기 주문 102,493건 일괄 조회
주요 실행 노드	Nested Loop Left Join → Aggregate	HashAggregate → Sort → Merge Left Join
주문 인덱스	tr_ord_x54(mbr_id, ord_dtm)	tr_ord_x51(ord_dtm)
Shared Buffer Hit	122,811	1,673
실행시간 (반복 측정)	194.679ms	116.589ms

동일한 SQL을 교차 재실행했으며, 다른 측정에서도 BAD 198.909ms, GOOD 115.290ms였다. 처음 실행에서는 BAD 228.907ms, GOOD 133.246ms였다. 실행시간은 캐시·시스템 부하에 따라 달라지므로 각 값은 해당 실행의 실측치다.

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

Nested Loop Left Join (rows=9999 loops=1)
  Buffers: shared hit=122811
  -> Index Scan using ms_mbr_pk on ms_mbr m
       (rows=9999 loops=1)
  -> Aggregate (rows=1 loops=9999)
       -> Index Scan using tr_ord_x54 on tr_ord o
            (rows=10 loops=9999)
            Index Cond: (mbr_id = m.mbr_id
                         AND ord_dtm >= '2023-01-01'
                         AND ord_dtm < '2023-07-01')
Execution Time: 194.679 ms

Plain Text

복사

Index Scan의 rows=10은 루프당 평균을 반올림한 수치다. 주문 접근이 전체 10건이라는 뜻이 아니다.

GOOD — 실제 실행계획의 핵심 노드

Merge Left Join (rows=9999 loops=1)
  Buffers: shared hit=1673
  -> Index Scan using ms_mbr_pk on ms_mbr m
       (rows=9999 loops=1)
  -> Sort (rows=9688 loops=1)
       Sort Key: x.mbr_id
       -> HashAggregate (rows=9688 loops=1)
            Group Key: o.mbr_id
            Batches: 1  Memory Usage: 4241kB
            -> Index Scan using tr_ord_x51 on tr_ord o
                 (rows=102493 loops=1)
                 Index Cond: (ord_dtm >= '2023-01-01'
                              AND ord_dtm < '2023-07-01')
Execution Time: 116.589 ms

Plain Text

복사

노드 구조, 실제 행 수, Buffer Hit와 실행시간을 발췌했으며 자료형 변환 등 부가 표기는 생략했다.

왜 달라졌나? BAD는 주문 인덱스를 활용했지만 회원 수만큼 반복 접근해 Shared Buffer Hit가 122,811이었다. GOOD은 상반기 주문을 한 번 읽어 9,688개 회원 그룹으로 집계하고 회원과 결합했다. Shared Buffer Hit는 1,673으로 감소했다. 기존 인덱스만 사용했으며 새 인덱스는 생성하지 않았다.

핵심: 외부 집합이 크고 같은 범위의 주문을 전체 회원별로 집계해야 한다면, LATERAL의 반복 접근 대신 먼저 GROUP BY하고 JOIN하는 방식을 검토하자. 10-01은 소수 회원을 먼저 선별하는 정반대 사례로, 함께 비교하면 효과적이다.

주의할 예외

•

LEFT JOIN으로 회원을 모두 유지하기 때문에 주문이 없는 회원의 집계금액은 두 방식 모두 NULL이다. COALESCE(..., 0)를 요구한다면 양쪽에 같은 기준으로 적용해야 한다.

•

외부 회원 수가 적거나 회원별 주문 접근이 매우 효율적이면 LATERAL이 더 유리할 수 있다. 반복 횟수(loops)와 Buffer Hit를 실제 확인해야 한다.

•

선집계에는 HashAggregate 메모리와 JOIN·정렬 비용이 발생한다. 데이터 분포·메모리·실행계획에 따라 결과는 달라질 수 있다. SQL에 LATERAL이 쓰였다는 사실만으로 반드시 Nested Loop를 선택한다고 단정할 수 없다.

원본 규칙: 개편 전 07-09.

PART B. WITH와 MATERIALIZED의 평가 제어

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

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

BAD — 필요하지 않은 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 — 단순히 한 번 참조하는 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 — 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 및 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로 작성하고 실행계획을 확인하자.

주의할 예외

•

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

•

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

•

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

•

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

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

REMINDER 10-06. 중간 결과를 재사용하기 전에 조건부 집계를 검토하자

상황: 회원 M1765의 2023년 1월~2025년 1월 주문을 대상으로 전체 주문 건수와 픽업 완료 주문 건수(ord_st = 'PKUP')를 함께 조회한다. 일반 테이블 startdbpg.tr_ord를 실제 조회했다. 기존 M00019의 2023년 3월은 일반 테이블에서 주문이 없고, M0001의 2023년 3월은 31건이 전부 PKUP이므로, 두 건수가 실제로 다른 조건(102건/100건)으로 실습 범위를 바꿨다.

BAD — 같은 범위의 주문을 별도 서브쿼리에서 두 번 검색

SELECT
    (SELECT COUNT(*)
     FROM startdbpg.tr_ord o
     WHERE o.mbr_id = 'M1765'
       AND o.ord_dtm >= TIMESTAMP '2023-01-01'
       AND o.ord_dtm <  TIMESTAMP '2025-02-01') AS all_cnt,
    (SELECT COUNT(*)
     FROM startdbpg.tr_ord o
     WHERE o.mbr_id = 'M1765'
       AND o.ord_dtm >= TIMESTAMP '2023-01-01'
       AND o.ord_dtm <  TIMESTAMP '2025-02-01'
       AND o.ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

이 SQL에서 PostgreSQL은 실제로 별도 InitPlan 두 개를 만들었다. 첫 번째 COUNT(*)는 tr_ord_x54의 Index Only Scan으로 102건을 확인하고, 두 번째는 같은 인덱스의 Index Scan으로 102건 중 2건을 Filter로 제거했다. 두 번의 원본 인덱스 접근이 발생했다.

비교안 — MATERIALIZED로 중간 결과를 만든 뒤 두 번 재사용

WITH orders AS MATERIALIZED (
    SELECT o.ord_st
    FROM startdbpg.tr_ord o
    WHERE o.mbr_id = 'M1765'
      AND o.ord_dtm >= TIMESTAMP '2023-01-01'
      AND o.ord_dtm <  TIMESTAMP '2025-02-01'
)
SELECT
    (SELECT COUNT(*) FROM orders) AS all_cnt,
    (SELECT COUNT(*) FROM orders WHERE ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

원본 주문에 대한 Index Scan은 한 번(102건)만 수행하고, 구체화한 orders를 CTE Scan 2회로 다시 사용했다. 원본 테이블 재탐색은 줄지만, 중간 결과도 두 번 읽는다. 따라서 여기서는 MATERIALIZED를 무조건 GOOD으로 보지 않는다.

GOOD — 조건부 집계로 한 번에 계산

SELECT COUNT(*) AS all_cnt,
       COUNT(*) FILTER (
           WHERE o.ord_st = 'PKUP'
       ) AS pkup_cnt
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M1765'
  AND o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND o.ord_dtm <  TIMESTAMP '2025-02-01';

SQL

복사

같은 범위에서 필요한 지표 두 개를 구하는 업무라면 Aggregate 한 번으로 처리할 수 있다. 원본 테이블에 대한 Index Scan 1회 외에 CTE Scan이나 중복 서브쿼리 접근이 없다.

실제 데이터 및 결과 동등성

•

M1765의 해당 기간 주문은 102건이며 PKUP 100건, 다른 상태 2건이다.

•

세 SQL 모두 all_cnt=102, pkup_cnt=100인 1행을 반환했다.

•

양방향 EXCEPT ALL로 비교한 결과 차이 0건이다.

•

사용한 인덱스는 기존 tr_ord_x54(mbr_id, ord_dtm)이며 새 인덱스는 생성하지 않았다.

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

비교 항목	BAD: 독립 COUNT 2회	비교안: MATERIALIZED	GOOD: COUNT FILTER
원본 테이블 접근	Index Only Scan + Index Scan, 2회	Index Scan 1회	Index Scan 1회
CTE Scan	없음	2회	없음
주요 집계	Aggregate 2회	Aggregate 2회	Aggregate 1회
Shared Buffer Hit	52	47	47
실행시간 (최초 비교)	0.865ms	0.762ms	0.601ms
재실행 관찰 범위	0.661~0.728ms	0.691~0.713ms	0.574~0.654ms

실측 시간 차이가 매우 작고 실행별로 변동하므로, 이 사례에서 MATERIALIZED가 반복 COUNT보다 항상 빠르다고 단정할 수 없다. 비교의 핵심은 접근 구조와 Buffers다. 위 측정에서는 COUNT FILTER가 가장 단순했다.

BAD — 실제 실행계획 핵심 노드

Result (rows=1 loops=1)
  Buffers: shared hit=52
  InitPlan 1
    -> Aggregate (rows=1 loops=1)
         -> Index Only Scan using tr_ord_x54 on tr_ord o
              (rows=102 loops=1)
              Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
              Heap Fetches: 0
              Buffers: shared hit=5
  InitPlan 2
    -> Aggregate (rows=1 loops=1)
         -> Index Scan using tr_ord_x54 on tr_ord o_1
              (rows=100 loops=1)
              Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
              Filter: (ord_st = 'PKUP')
              Rows Removed by Filter: 2
              Buffers: shared hit=47
Execution Time: 0.865 ms

Plain Text

복사

MATERIALIZED — 실제 실행계획 핵심 노드

Result (rows=1 loops=1)
  Buffers: shared hit=47
  CTE orders
    -> Index Scan using tr_ord_x54 on tr_ord o
         (rows=102 loops=1)
         Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
  InitPlan 2
    -> Aggregate (rows=1 loops=1)
         -> CTE Scan on orders (rows=102 loops=1)
  InitPlan 3
    -> Aggregate (rows=1 loops=1)
         -> CTE Scan on orders orders_1 (rows=100 loops=1)
              Filter: (ord_st = 'PKUP')
              Rows Removed by Filter: 2
Execution Time: 0.762 ms

Plain Text

복사

COUNT FILTER — 실제 실행계획 핵심 노드

Aggregate (rows=1 loops=1)
  Buffers: shared hit=47
  -> Index Scan using tr_ord_x54 on tr_ord o
       (rows=102 loops=1)
       Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
Execution Time: 0.601 ms

Plain Text

복사

실제 계획의 노드 유형·행 수·loops·버퍼·시간을 유지하면서 PostgreSQL의 자료형 변환 표기와 날짜 경계값 등은 축약했다. FILTER 절은 집계 함수 내부에서 평가되므로 별도 Filter 노드가 없는 것이 정상이다.

핵심: 여러 번 참조할 중간 결과가 있다고 해서 MATERIALIZED부터 선택하지 말자. 먼저 하나의 GROUP BY 또는 COUNT(*) FILTER로 필요한 값을 한 번에 구할 수 있는지 확인하자. 중복 조회를 없앨 다른 방법이 없고 동일한 계산 결과를 여러 곳에서 재사용한다면 MATERIALIZED를 검토한다.

주의할 예외

•

COUNT(*) FILTER는 하나의 입력 범위에서 여러 집계를 수행할 때 유용하지만, 이후 소비자가 서로 다른 상세 행을 필요로 하는 경우까지 모두 대체하지는 못한다.

•

MATERIALIZED는 중간 결과 저장·CTE Scan 비용이 생기고, 외부 WHERE 조건이 CTE 내부로 전달되지 못할 수 있다(10-05 참고).

•

같은 원본 범위라도 서로 다른 인덱스 조건이 가능하다면, 두 번의 선택적인 인덱스 탐색이 하나의 구체화보다 유리한 경우가 있다. 계획과 Buffers를 확인한다.

•

COUNT(*) FILTER (WHERE ord_st='PKUP')에서 특정 주문상태에 해당하는 행만 집계되지만 그 조건이 자동으로 인덱스 검색 범위를 줄인다는 뜻은 아니다. 이 계획에서는 mbr_id, ord_dtm만 Index Cond에 포함됐다.

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

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

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

검토할 SQL / 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

복사

개선 또는 비교 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 값을 반환한다.

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

분석 후 점검

조건을 집계 인라인 뷰 내부로 전달할 필요가 있는가?

외부 입력이 적고 내부 인덱스가 적절한가?

LATERAL 반복 실행 횟수가 너무 크지는 않은가?

CTE를 한 번 참조하는가 여러 번 참조하는가?

MATERIALIZED가 외부 필터의 Pushdown을 차단하지 않는가?

중복 계산과 중간 결과 저장 중 무엇이 더 저렴한가?

SECTION 10. 고급 SQL 활용 전략

상세 페이지

PART A. 필요한 행에 조건을 공급하는 고급 SQL

REMINDER 10-01. 선별된 회원부터 JOIN하고 집계하자

REMINDER 10-03. 그룹별 최신 1건은 LATERAL + LIMIT으로 찾자

REMINDER 10-04. 회원이 많다면 LATERAL 반복 집계보다 선집계를 검토하자

PART B. WITH와 MATERIALIZED의 평가 제어

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

REMINDER 10-06. 중간 결과를 재사용하기 전에 조건부 집계를 검토하자

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

분석 후 점검

관련 원문과 추가 학습