REMINDER 06-02. 반복 집계는 선집계 JOIN과 JOIN 후 GROUP BY를 비교하자

상황: 전체 회원의 2023년 12월 주문금액 합계를 조회한다. 회원에게 해당 기간 주문이 없어도 회원은 출력하고, 합계는 NULL로 유지한다. 실제 일반 테이블 startdbpg.ms_mbr과 startdbpg.tr_ord에서 세 SQL을 실행해 결과와 실행계획을 검증했다.

BAD SQL — 회원별 스칼라 서브쿼리 반복 집계

SELECT m.mbr_id, m.nick_nm,
       (SELECT SUM(o.ord_amt)
        FROM   startdbpg.tr_ord o
        WHERE  o.mbr_id = m.mbr_id
          AND  o.ord_dtm >= TIMESTAMP '2023-12-01'
          AND  o.ord_dtm <  TIMESTAMP '2024-01-01') ord_amt
FROM   startdbpg.ms_mbr m;

SQL

복사

전체 회원 9,999명을 조회하면서, 주문 집계 서브쿼리를 회원별로 반복 실행한다.

GOOD SQL 1 — 주문 선집계 후 LEFT JOIN

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

SQL

복사

12월 주문만 골라 회원별로 미리 집계한 뒤 전체 회원과 연결한다. 이번 실습에서는 주문 20,359건을 집계해 9,614명의 회원별 합계를 만든다.

GOOD SQL 2 — LEFT JOIN 후 GROUP BY (이번 사례의 우선 추천안)

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

SQL

복사

조회한 회원과 12월 주문을 곧바로 JOIN한 뒤 회원별로 합산한다. 기간 조건을 ON에 둬야 주문이 없는 회원 385명도 보존된다. 이번 예제에서는 간단하고 직관적인 GOOD 2를 우선 추천하지만, 두 방식의 우열은 JOIN 구조와 데이터에 따라 바뀐다.

실제 데이터 및 결과 검증

•

전체 회원: 9,999명

•

2023년 12월 주문: 20,359건 / 주문한 회원: 9,614명

•

해당 기간 주문이 없는 회원: 385명, 세 SQL 모두 ord_amt=NULL

•

BAD, GOOD 1, GOOD 2 모두 9,999행 반환

•

EXCEPT ALL 양방향 비교: BADGOOD 1, GOOD 1GOOD 2 차이 0건

실측 성능 비교 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

항목	BAD — 스칼라 반복	GOOD 1 — 선집계 후 JOIN	GOOD 2 — JOIN 후 집계
결과 행 수	9,999	9,999	9,999
실행시간 (각 1회)	11,619.773ms	52.714ms	48.296ms
Buffers: shared hit	668,001	347	347
주요 실행계획	SubPlan → Aggregate (9,999회)	HashAggregate → Hash Left Join	Hash Right Join → HashAggregate
집계 대상	회원마다 주문 재조회	주문 20,359건을 1회 집계	JOIN 결과 20,744행을 1회 집계

BAD SQL — 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm,
       (SELECT SUM(o.ord_amt)
        FROM   startdbpg.tr_ord o
        WHERE  o.mbr_id = m.mbr_id
          AND  o.ord_dtm >= TIMESTAMP '2023-12-01'
          AND  o.ord_dtm <  TIMESTAMP '2024-01-01') ord_amt
FROM   startdbpg.ms_mbr m;

SQL

복사

Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=1631.856..11617.295 rows=9999 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=668001
  SubPlan 1
    ->  Aggregate (actual time=1.157..1.157 rows=1 loops=9999)
          Buffers: shared hit=667959
          ->  Index Scan using tr_ord_fk01 on tr_ord o (actual time=0.841..1.153 rows=2 loops=9999)
                Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
                Filter: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
                Rows Removed by Filter: 71
                Buffers: shared hit=667959
Planning:
  Buffers: shared hit=196
Planning Time: 5.023 ms
Execution Time: 11619.773 ms

Plain Text

복사

GOOD SQL 1 — 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm, a.ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT o.mbr_id, SUM(o.ord_amt) ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.ord_dtm >= TIMESTAMP '2023-12-01'
      AND  o.ord_dtm <  TIMESTAMP '2024-01-01'
    GROUP BY o.mbr_id
) a
  ON   a.mbr_id = m.mbr_id;

SQL

복사

Hash Left Join (actual time=43.293..50.120 rows=9999 loops=1)
  Hash Cond: ((m.mbr_id)::text = (a.mbr_id)::text)
  Buffers: shared hit=347
  ->  Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=0.083..2.448 rows=9999 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=42
  ->  Hash (actual time=43.130..43.133 rows=9614 loops=1)
        Buckets: 16384 (originally 8192)  Batches: 1 (originally 1)  Memory Usage: 545kB
        Buffers: shared hit=305
        ->  Subquery Scan on a (actual time=28.718..38.208 rows=9614 loops=1)
              Buffers: shared hit=305
              ->  HashAggregate (actual time=28.717..36.393 rows=9614 loops=1)
                    Group Key: o.mbr_id
                    Batches: 1  Memory Usage: 3985kB
                    Buffers: shared hit=305
                    ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.079..8.087 rows=20359 loops=1)
                          Index Cond: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
                          Buffers: shared hit=305
Planning:
  Buffers: shared hit=314
Planning Time: 3.948 ms
Execution Time: 52.714 ms

Plain Text

복사

GOOD SQL 2 — 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm,
       SUM(o.ord_amt) ord_amt
FROM   startdbpg.ms_mbr m
LEFT JOIN startdbpg.tr_ord o
  ON   o.mbr_id = m.mbr_id
 AND   o.ord_dtm >= TIMESTAMP '2023-12-01'
 AND   o.ord_dtm <  TIMESTAMP '2024-01-01'
GROUP BY m.mbr_id, m.nick_nm;

SQL

복사

HashAggregate (actual time=41.569..46.234 rows=9999 loops=1)
  Group Key: m.mbr_id
  Batches: 1  Memory Usage: 4113kB
  Buffers: shared hit=347
  ->  Hash Right Join (actual time=6.126..26.340 rows=20744 loops=1)
        Hash Cond: ((o.mbr_id)::text = (m.mbr_id)::text)
        Buffers: shared hit=347
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.074..7.326 rows=20359 loops=1)
              Index Cond: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
              Buffers: shared hit=305
        ->  Hash (actual time=5.929..5.931 rows=9999 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 583kB
              Buffers: shared hit=42
              ->  Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=0.036..2.507 rows=9999 loops=1)
                    Heap Fetches: 0
                    Buffers: shared hit=42
Planning:
  Buffers: shared hit=334
Planning Time: 3.024 ms
Execution Time: 48.296 ms

Plain Text

복사

실행계획 읽기: BAD는 tr_ord_fk01(mbr_id)로 회원별 주문을 찾아 날짜를 Filter로 걸렀으며, loops=9999, 회원당 평균 약 71건이 날짜 조건에서 제외되는 반복 접근이 발생했다. GOOD 1과 GOOD 2는 tr_ord_x51(ord_dtm)에서 12월 주문 20,359건만 조회한 후 집계를 수행한다. GOOD 1은 주문을 회원별 9,614행으로 줄인 뒤 JOIN하고, GOOD 2는 LEFT JOIN 결과 20,744행(20,359건 + 미주문 회원 385명)을 회원별로 집계한다.

핵심

•

외부 행 수가 많고 반복 집계가 발생한다면, 선집계 후 JOIN과 JOIN 후 GROUP BY를 모두 검토하자. 이 실측 사례에서는 두 GOOD 모두 반복 접근을 크게 줄였고, 업무 표현이 단순한 GOOD 2를 우선 추천한다.

참고·예외

•

소수 회원만 조회한다면 스칼라 서브쿼리가 더 유리할 수 있다(06-03). JOIN 대상이 여러 1:N 관계라면 JOIN 후 집계에서 합계·건수가 증폭될 수 있으므로 선집계를 우선 고려해야 한다(05-02). 모든 회원을 보존하려면 주문 날짜 조건을 ON에 두어야 하며, WHERE로 옮기면 주문 없는 회원이 사라진다(05-04). 본 실행시간은 각각 1회 측정이므로 52.714ms와 48.296ms의 작은 차이로 두 GOOD의 성능 우열을 일반화하지 않는다.