SECTION 06. 서브쿼리 작성과 반복 접근

상세 페이지

SECTION 07에서 GROUP BY·FILTER를 학습한 다음, 스칼라 서브쿼리의 단일 행 규칙부터 확인하고 반복 접근을 개선한다. 외부 행 수와 집계 목적을 보고 조회와 집계의 중복을 줄이자.

권장 난이도: 중급 초반 · 진행 방식: BAD SQL → GOOD SQL → 핵심 한 문장.

예상 실행계획·고급 인덱스·조인 알고리즘은 강의 후반부로 옮겼다. 상세 예외와 원본 실습 근거는 SECTION 06 원문 백업에서 확인할 수 있다.

REMINDER 06-06. 스칼라 서브쿼리는 최대 한 행·한 컬럼만 반환한다

상황: 회원별 2023년 12월 주문금액을 SELECT 절의 스칼라 서브쿼리로 조회한다. 집계 함수를 생략해도 지금은 정상 실행되는 회원이 있지만, 주문이 여러 건인 회원을 만나면 오류가 발생한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord에서 해당 사례를 찾아 실행했다.

실제 주문 데이터 (2023-12-01 이상 ~ 2024-01-01 미만)

회원ID	12월 주문 건수	실제 주문금액	스칼라 서브쿼리
M0406	0건	없음	정상, NULL
M0036	1건	14,000	정상, 14000.000
M0039	2건	15,500 / 10,500	오류 발생

정상 실행 — 집계 함수가 없어도 서브쿼리가 0~1행을 반환하는 경우

SELECT m.mbr_id,
       (SELECT 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
WHERE  m.mbr_id IN ('M0406', 'M0036')
ORDER BY m.mbr_id;

SQL

복사

실제 실행 결과

mbr_id | ord_amt
-------+----------
M0036  | 14000.000
M0406  | NULL

Plain Text

복사

•

M0036: 서브쿼리가 1행을 반환해 주문금액이 정상 표시된다.

•

M0406: 서브쿼리가 0행을 반환해 스칼라 값은 NULL이 된다.

•

중요: M0036에 현재 주문이 1건 있다는 것은 미래에도 1건이라는 보장이 아니다. 주문이 추가되면 같은 SQL이 갑자기 실패할 수 있다.

오류 발생 — 집계 함수 없이 서브쿼리가 2행 이상을 반환하는 경우

SELECT m.mbr_id,
       (SELECT 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
WHERE  m.mbr_id = 'M0039';

SQL

복사

실제 실행 오류

ERROR: more than one row returned by a subquery used as an expression
SQLSTATE: 21000

Plain Text

복사

실제 M0039의 해당 월 주문은 2건이다.

ord_no | ord_amt
-------+----------
499212 | 15500.000
499752 | 10500.000

Plain Text

복사

GOOD — 업무 목적이 '주문금액 합계'라면 SUM으로 단일 값 반환

SELECT m.mbr_id,
       (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
WHERE  m.mbr_id = 'M0039';

SQL

복사

실제 실행 결과: M0039 / 26000.000. SUM은 해당 월 주문이 몇 건이든 집계 결과 한 행·한 컬럼을 반환하며, 0건이면 합계는 NULL이다.

다른 요구사항 — '가장 최근 주문 1건의 금액'이라면

SELECT m.mbr_id,
       (SELECT 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'
        ORDER BY o.ord_dtm DESC, o.ord_no DESC
        LIMIT 1) ord_amt
FROM   startdbpg.ms_mbr m
WHERE  m.mbr_id = 'M0039';

SQL

복사

실제 실행 결과: M0039 / 10500.000. 마지막 주문의 판정 기준을 ord_dtm DESC, ord_no DESC로 명확하게 지정했다. 오류를 피하려고 무작정 LIMIT 1을 붙여서는 안 된다. 최신 한 건이 업무 목적일 때만 사용한다.

핵심: 스칼라 서브쿼리는 반드시 최대 1행·1컬럼을 반환해야 한다. 집계 함수가 없다고 무조건 오류가 나는 것도 아니고, 현재 정상 실행된다고 안전한 것도 아니다. 관련 데이터가 나중에 늘어나도 1행을 보장할 수 있는지 확인하자.

예외·주의: 서브쿼리 조건이 ord_no처럼 PK·UK에 의해 유일한 행을 지정하면 집계 함수 없이도 안전할 수 있다. 반면 일반적인 회원ID나 기간 조건은 주문이 여러 건일 수 있으므로 단일 행을 보장하지 않는다. 다수 주문의 합계가 목적이면 SUM 등의 집계를, 최신 한 건이 목적이면 결정적인 ORDER BY ... LIMIT 1을 사용한다. 전체 회원의 반복 집계 성능 문제는 06-02에서 별도로 다룬다. 이 항목은 성능보다 잠재적 런타임 오류 예방이 목적이다.

REMINDER 06-01. 같은 테이블에 반복 접근하는 스칼라 서브쿼리를 줄이자

상황: 2023년 1월 1일부터 10일까지의 주문을 조회하며 회원 닉네임·휴대전화와 매장명·테이블 수를 표시한다. 실제 일반 테이블 startdbpg.tr_ord, startdbpg.ms_mbr, startdbpg.ms_shop로 검증했다.

BAD — 동일한 키로 회원·매장을 각각 두 번씩 검색

SELECT o.ord_no, o.ord_dtm,
       (SELECT m.nick_nm
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = o.mbr_id) nick_nm,
       (SELECT m.mobl_no
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = o.mbr_id) mobl_no,
       (SELECT s.shop_nm
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = o.shop_id) shop_nm,
       (SELECT s.table_qty
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = o.shop_id) table_qty
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-01-11';

SQL

복사

SELECT 절에서 회원 테이블을 두 번, 매장 테이블을 두 번 조회한다. 이 예제의 실제 실행계획에서는 각 스칼라 서브쿼리가 외부 주문 6,031건마다 반복 실행되었다.

GOOD — 회원·매장을 각각 LEFT JOIN 한 번으로 처리

SELECT o.ord_no, o.ord_dtm,
       m.nick_nm, m.mobl_no,
       s.shop_nm, s.table_qty
FROM   startdbpg.tr_ord o
LEFT JOIN startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-01-11';

SQL

복사

회원·매장 컬럼을 JOIN으로 함께 가져온다. 스칼라 서브쿼리는 일치 행이 없을 때 NULL을 반환하므로, 같은 의미를 유지하기 위해 LEFT JOIN으로 작성했다. ms_mbr.mbr_id와 ms_shop.shop_id가 실제 PK라서 JOIN으로 주문 행이 증폭되지 않는다.

실제 결과 동등성 검증

•

주문 기간에 해당하는 원본 주문: 6,031건.

•

BAD 결과: 6,031건, GOOD 결과: 6,031건.

•

두 결과에 EXCEPT ALL을 양방향으로 적용해 비교한 차이: 모두 0건.

•

해당 기간 주문의 회원 ID·매장 ID NULL은 각각 0건. PK·FK 관계도 실제 데이터베이스에서 확인했다.

실제 실행계획 비교

항목	BAD: 스칼라 서브쿼리 4개	GOOD: LEFT JOIN 2개
결과 행 수	6,031건	6,031건
주요 실행계획	Index Scan • SubPlan 1~4	Hash Left Join 2개
회원 테이블 접근	스칼라 서브쿼리 2개 × loops=6031	회원 9,999건으로 Hash 구성 1회
매장 테이블 접근	스칼라 서브쿼리 2개 × loops=6031	매장 300건으로 Hash 구성 1회
Buffers: shared hit	60,403	229
실행시간(각 1회 측정)	101.110ms	14.358ms

BAD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm,
       (SELECT m.nick_nm
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = o.mbr_id) nick_nm,
       (SELECT m.mobl_no
        FROM   startdbpg.ms_mbr m
        WHERE  m.mbr_id = o.mbr_id) mobl_no,
       (SELECT s.shop_nm
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = o.shop_id) shop_nm,
       (SELECT s.table_qty
        FROM   startdbpg.ms_shop s
        WHERE  s.shop_id = o.shop_id) table_qty
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-01-11';

SQL

복사

Index Scan using tr_ord_x51 on tr_ord o (actual time=0.240..100.379 rows=6031 loops=1)
  Index Cond: ((ord_dtm >= '2023-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-11 00:00:00'::timestamp without time zone))
  Buffers: shared hit=60403
  SubPlan 1
    ->  Index Scan using ms_mbr_pk on ms_mbr m (actual time=0.004..0.004 rows=1 loops=6031)
          Index Cond: ((mbr_id)::text = (o.mbr_id)::text)
          Buffers: shared hit=18093
  SubPlan 2
    ->  Index Scan using ms_mbr_pk on ms_mbr m_1 (actual time=0.004..0.004 rows=1 loops=6031)
          Index Cond: ((mbr_id)::text = (o.mbr_id)::text)
          Buffers: shared hit=18093
  SubPlan 3
    ->  Index Scan using ms_shop_pk on ms_shop s (actual time=0.003..0.003 rows=1 loops=6031)
          Index Cond: ((shop_id)::text = (o.shop_id)::text)
          Buffers: shared hit=12062
  SubPlan 4
    ->  Index Scan using ms_shop_pk on ms_shop s_1 (actual time=0.002..0.003 rows=1 loops=6031)
          Index Cond: ((shop_id)::text = (o.shop_id)::text)
          Buffers: shared hit=12062
Planning:
  Buffers: shared hit=218
Planning Time: 1.230 ms
Execution Time: 101.110 ms

Plain Text

복사

GOOD 실제 실행계획

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT o.ord_no, o.ord_dtm,
       m.nick_nm, m.mobl_no,
       s.shop_nm, s.table_qty
FROM   startdbpg.tr_ord o
LEFT JOIN startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-01-11';

SQL

복사

Hash Left Join (actual time=5.514..13.690 rows=6031 loops=1)
  Hash Cond: ((o.shop_id)::text = (s.shop_id)::text)
  Buffers: shared hit=229
  ->  Hash Left Join (actual time=5.295..10.687 rows=6031 loops=1)
        Hash Cond: ((o.mbr_id)::text = (m.mbr_id)::text)
        Buffers: shared hit=225
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.067..1.915 rows=6031 loops=1)
              Index Cond: ((ord_dtm >= '2023-01-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-11 00:00:00'::timestamp without time zone))
              Buffers: shared hit=93
        ->  Hash (actual time=5.087..5.089 rows=9999 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 680kB
              Buffers: shared hit=132
              ->  Seq Scan on ms_mbr m (actual time=0.016..1.866 rows=9999 loops=1)
                    Buffers: shared hit=132
  ->  Hash (actual time=0.188..0.189 rows=300 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 25kB
        Buffers: shared hit=4
        ->  Seq Scan on ms_shop s (actual time=0.018..0.081 rows=300 loops=1)
              Buffers: shared hit=4
Planning:
  Buffers: shared hit=386
Planning Time: 2.138 ms
Execution Time: 14.358 ms

Plain Text

복사

실행계획 읽기: BAD에는 SubPlan 1~4가 각각 loops=6031로 나타나 동일 회원·매장을 반복 조회했다. GOOD에서는 회원과 매장을 한 번씩 스캔해 Hash를 구성한 후 Hash Left Join으로 연결했다. 이 실측 사례에서는 GOOD이 더 적은 공유 버퍼 접근과 짧은 실행시간을 보였다.

핵심: 같은 키로 동일 테이블의 여러 컬럼을 반복 검색한다면, JOIN으로 묶어서 불필요한 반복 접근을 줄일 수 있다.

예외·주의: 스칼라 서브쿼리가 항상 비효율적인 것은 아니다. 외부 행 수가 아주 적거나 인덱스 접근이 유리하면 스칼라 방식이 나을 수 있고, PostgreSQL이 다른 실행계획을 선택할 수도 있다. 또한 LEFT JOIN 대상 키가 유일하지 않으면 결과 행 수가 증가하므로 PK·UK를 확인해야 한다. 실행시간은 각각 1회 측정한 값이며 반복 측정으로 재확인할 필요가 있다.

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

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

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

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 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 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 실제 실행계획

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 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 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의 성능 우열을 일반화하지 않는다.

REMINDER 06-04. 기간별 반복 집계는 조건부 집계로 통합하자

상황: 전체 회원별로 2022년 12월과 2023년 12월의 주문금액을 비교한다. 회원에게 해당 기간 주문이 없어도 회원 행은 보존하고 합계는 NULL로 표현한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord에서 검증했다.

BAD — 두 기간을 회원마다 각각 스칼라 서브쿼리로 집계

SELECT m.mbr_id,
       (SELECT SUM(o.ord_amt)
        FROM   startdbpg.tr_ord o
        WHERE  o.mbr_id = m.mbr_id
          AND  o.ord_dtm >= TIMESTAMP '2022-12-01'
          AND  o.ord_dtm <  TIMESTAMP '2023-01-01') amt_2212,
       (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') amt_2312
FROM   startdbpg.ms_mbr m;

SQL

복사

실제 실행계획에서 SubPlan 1과 SubPlan 2가 각각 loops=9999로 반복됐다. 주문을 회원별로 재검색하면서 날짜 조건은 Filter로 적용했다.

GOOD — 범용 CASE WHEN 조건부 집계로 통합 (주 예제)

SELECT m.mbr_id, a.amt_2212, a.amt_2312
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT o.mbr_id,
           SUM(CASE
                 WHEN o.ord_dtm >= TIMESTAMP '2022-12-01'
                  AND o.ord_dtm <  TIMESTAMP '2023-01-01'
                 THEN o.ord_amt
               END) amt_2212,
           SUM(CASE
                 WHEN o.ord_dtm >= TIMESTAMP '2023-12-01'
                  AND o.ord_dtm <  TIMESTAMP '2024-01-01'
                 THEN o.ord_amt
               END) amt_2312
    FROM   startdbpg.tr_ord o
    WHERE  (o.ord_dtm >= TIMESTAMP '2022-12-01'
        AND o.ord_dtm <  TIMESTAMP '2023-01-01')
       OR  (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

복사

서로 다른 두 기간의 주문을 검색한 후 회원별 GROUP BY 한 번으로 두 금액을 계산한다. CASE에는 ELSE 0을 넣지 않는다. 그래야 해당 기간 주문이 없는 경우 기존 SUM의 NULL 의미가 유지된다. LEFT JOIN은 두 기간 모두 주문이 없는 회원을 보존한다.

보충 — PostgreSQL에서 사용할 수 있는 FILTER 표현

-- 위 GOOD의 인라인 뷰 SELECT 안에서 CASE 집계 두 개 대신 사용 가능
SELECT o.mbr_id,
       SUM(o.ord_amt) FILTER (
           WHERE o.ord_dtm >= TIMESTAMP '2022-12-01'
             AND o.ord_dtm <  TIMESTAMP '2023-01-01'
       ) amt_2212,
       SUM(o.ord_amt) FILTER (
           WHERE o.ord_dtm >= TIMESTAMP '2023-12-01'
             AND o.ord_dtm <  TIMESTAMP '2024-01-01'
       ) amt_2312
FROM   startdbpg.tr_ord o
WHERE  (o.ord_dtm >= TIMESTAMP '2022-12-01'
    AND o.ord_dtm <  TIMESTAMP '2023-01-01')
   OR  (o.ord_dtm >= TIMESTAMP '2023-12-01'
    AND o.ord_dtm <  TIMESTAMP '2024-01-01')
GROUP BY o.mbr_id;

SQL

복사

FILTER (WHERE ...)는 PostgreSQL 전용으로 만들어진 문법이 아니라 SQL 표준의 집계 조건 문법이지만 DBMS별 지원 차이가 있다. 따라서 범용 작성 규칙의 주 예제는 SUM(CASE WHEN ... THEN ... END)로 제시하고, PostgreSQL의 간결한 대안으로 FILTER를 소개한다. 위 보충 SQL은 GOOD 내부의 집계 부분을 대체하는 것으로, 전체 회원 조회 시에는 GOOD과 동일하게 회원 테이블에 LEFT JOIN해야 한다.

실제 데이터와 결과 동등성

•

전체 회원: 9,999명

•

2022년 12월 주문: 20,524건, 해당 월 주문 회원 9,709명

•

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

•

2022년 12월 집계 NULL 회원: 290명, 2023년 12월 집계 NULL 회원: 385명

•

BAD, GOOD(CASE), FILTER 전체 조회: 각각 9,999행

•

EXCEPT ALL 양방향 비교: BADGOOD, GOODFILTER 모두 차이 0건 (FILTER는 동일 인라인 뷰와 LEFT JOIN 구조로 비교)

실제 실행계획 비교

항목	BAD: 스칼라 2개	GOOD: CASE WHEN	보충: FILTER
실행시간(각 1회)	8,702.181ms	78.400ms	75.095ms
Buffers: shared hit	1,335,948	642	642
주요 노드	SubPlan 2개 × loops=9999	BitmapOr → HashAggregate → Hash Left Join	BitmapOr → HashAggregate → Hash Left Join
결과 회원 수	9,999	9,999	9,999

BAD 실제 실행계획

Index Only Scan using ms_mbr_pk on ms_mbr m (actual time=939.274..8698.185 rows=9999 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=1335948
  SubPlan 1
    ->  Aggregate (actual time=0.819..0.819 rows=1 loops=9999)
          Buffers: shared hit=667959
          ->  Index Scan using tr_ord_fk01 on tr_ord o (actual time=0.383..0.816 rows=2 loops=9999)
                Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
                Filter: ((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone))
                Rows Removed by Filter: 71
                Buffers: shared hit=667959
  SubPlan 2
    ->  Aggregate (actual time=0.047..0.047 rows=1 loops=9999)
          Buffers: shared hit=667959
          ->  Index Scan using tr_ord_fk01 on tr_ord o_1 (actual time=0.033..0.045 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: 7.202 ms
Execution Time: 8702.181 ms

Plain Text

복사

GOOD (CASE) 실제 실행계획

Hash Left Join (actual time=67.751..74.893 rows=9999 loops=1)
  Hash Cond: ((m.mbr_id)::text = (a.mbr_id)::text)
  Buffers: shared hit=642, temp read=76 written=146
  ->  Index Only Scan using ms_mbr_pk on ms_mbr m (actual time=0.114..2.486 rows=9999 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=30
  ->  Hash (actual time=67.528..67.533 rows=9709 loops=1)
        Buckets: 16384  Batches: 1  Memory Usage: 605kB
        Buffers: shared hit=612, temp read=76 written=146
        ->  Subquery Scan on a (actual time=46.741..63.430 rows=9709 loops=1)
              Buffers: shared hit=612, temp read=76 written=146
              ->  HashAggregate (actual time=46.740..61.718 rows=9709 loops=1)
                    Group Key: o.mbr_id
                    Batches: 5  Memory Usage: 4721kB  Disk Usage: 728kB
                    Buffers: shared hit=612, temp read=76 written=146
                    ->  Bitmap Heap Scan on tr_ord o (actual time=3.730..11.788 rows=40883 loops=1)
                          Recheck Cond: (((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone)) OR ((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)))
                          Heap Blocks: exact=508
                          Buffers: shared hit=612
                          ->  BitmapOr (actual time=3.642..3.644 rows=0 loops=1)
                                Buffers: shared hit=104
                                ->  Bitmap Index Scan on tr_ord_x51 (actual time=2.395..2.395 rows=20524 loops=1)
                                      Index Cond: ((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone))
                                      Buffers: shared hit=52
                                ->  Bitmap Index Scan on tr_ord_x51 (actual time=1.246..1.246 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=52
Planning:
  Buffers: shared hit=314
Planning Time: 5.598 ms
Execution Time: 78.400 ms

Plain Text

복사

FILTER 실행계획 보충: 동일한 BitmapOr → Bitmap Heap Scan → HashAggregate → Hash Left Join 형태로 실행했고, Execution Time: 75.095 ms, Buffers: shared hit=642였다. 단일 측정에서는 CASE 측 HashAggregate가 Batches: 5로 디스크 임시 블록을 사용했고, FILTER 측은 Batches: 1이었다. 이 차이나 약 3ms의 시간 차이만으로 두 문법의 성능 우위를 일반화해서는 안 된다.

핵심: 동일 테이블의 여러 기간에 대해 반복 집계가 발생한다면, SUM(CASE WHEN ... THEN ... END)로 집계를 통합하는 방법을 고려하자. PostgreSQL에서는 FILTER로도 표현할 수 있다.

예외·주의: CASE와 FILTER 모두 SUM에 조건에 맞는 행이 없을 때 NULL을 반환하도록 작성했다. ELSE 0이나 COALESCE(...,0)는 결과 의미를 바꾸므로 업무 요구에 맞을 때만 사용한다. 외부 회원 수가 적고 회원별 인덱스가 효율적인 경우에는 개별 집계가 더 나을 수 있다. 실행시간은 각 1회 측정값이며 반복 측정을 통한 최종 성능 판단이 필요하다.

개발자가 기억할 체크리스트

같은 테이블의 같은 키를 여러 번 검색하지 않는가?

외부 결과가 많다면 선집계를 검토했는가?

외부 결과가 한 건이면 스칼라 방식도 비교했는가?

존재 확인은 EXISTS로 표현할 수 있는가?

스칼라 서브쿼리는 단일 행·컬럼을 반환하는가?

이 섹션의 한 줄 요약: 서브쿼리 자체가 문제가 아니라 같은 조회를 불필요하게 반복하는 것이 문제다. 외부 행 수와 집계 목적을 확인해 단순하고 읽기 쉬운 SQL로 바꾸자.