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

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

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

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 SQL — 회원·매장을 각각 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 SQL — 실제 실행계획

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

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회 측정한 값이며 반복 측정으로 재확인할 필요가 있다.