REMINDER 08-05. 페이징 후 필요한 테이블만 JOIN하자

상황: 2023년 3월 주문의 6,001~6,030번째 행을 주문일시·회원ID·주문번호 순으로 표시하면서 매장명과 회원 닉네임도 함께 출력한다. 정렬·페이징 기준은 주문 컬럼으로만 결정되고, 매장명·회원명으로 필터링하지 않는다.

실습 일반 테이블

•

startdbpg.tr_ord: 주문(PK ord_no)

•

startdbpg.ms_shop: 매장(PK shop_id)

•

startdbpg.ms_mbr: 회원(PK mbr_id)

•

tr_ord.shop_id, tr_ord.mbr_id는 각각 매장·회원 테이블에 대한 FK다. 각 JOIN 대상의 PK는 유일하므로 주문 1건이 JOIN 때문에 여러 행으로 늘지 않는다.

BAD SQL — 매장·회원을 먼저 JOIN하고 마지막에 페이징

SELECT o.ord_no, o.ord_dtm, o.shop_id, o.mbr_id,
       o.ord_amt, s.shop_nm, m.nick_nm
FROM   startdbpg.tr_ord o
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
LEFT JOIN startdbpg.ms_mbr m
  ON   m.mbr_id = o.mbr_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
ORDER BY o.ord_dtm, o.mbr_id, o.ord_no
LIMIT 30 OFFSET 6000;

SQL

복사

실제 실행계획에서 30건만 반환하는데도, Nested Loop Left Join이 6,050행을 처리한 다음 Incremental Sort와 LIMIT/OFFSET을 적용했다. ms_mbr 쪽 Memoize는 6,050번 접근하되 캐시 히트 2,538회·미스 3,512회였고, 실제 회원 PK Index Scan은 3,512회 실행됐다.

GOOD SQL — 주문 30건을 먼저 확정하고 부가 JOIN

SELECT p.ord_no, p.ord_dtm, p.shop_id, p.mbr_id,
       p.ord_amt, s.shop_nm, m.nick_nm
FROM (
    SELECT o.ord_no, o.ord_dtm, o.shop_id,
           o.mbr_id, o.ord_amt
    FROM   startdbpg.tr_ord o
    WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
      AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
    ORDER BY o.ord_dtm, o.mbr_id, o.ord_no
    LIMIT 30 OFFSET 6000
) p
LEFT JOIN startdbpg.ms_shop s
  ON   s.shop_id = p.shop_id
LEFT JOIN startdbpg.ms_mbr m
  ON   m.mbr_id = p.mbr_id
ORDER BY p.ord_dtm, p.mbr_id, p.ord_no;

SQL

복사

인라인뷰에서 주문 30건을 먼저 확정하므로, ms_shop과 ms_mbr의 PK Index Scan은 각각 30회만 실행된다. 바깥쪽 ORDER BY는 최종 출력의 정렬 순서를 명시하기 위한 것이다.

실제 실행 결과 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

항목	BAD: JOIN 후 페이징	GOOD: 페이징 후 JOIN
최종 반환 행	30	30
tr_ord_x51 Index Scan 행 수	6,050	6,050
매장 PK Index Scan 실행	289회 (Memoize 경유)	30회
회원 PK Index Scan 실행	3,512회 (Memoize 경유)	30회
Buffers: shared hit	11,219	255
실행시간 (각 1회)	40.652ms	11.830ms

결과 동등성: BAD와 GOOD 모두 30건이며, 출력한 전체 컬럼에 대해 EXCEPT ALL 양방향 비교를 수행한 결과 차이 0건이다.

BAD SQL — 실제 실행계획

Limit (actual time=40.158..40.271 rows=30 loops=1)
  Buffers: shared hit=11219
  ->  Incremental Sort (actual time=1.082..39.945 rows=6030 loops=1)
        Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
        Presorted Key: o.ord_dtm
        Buffers: shared hit=11219
        ->  Nested Loop Left Join (actual time=0.364..30.246 rows=6050 loops=1)
              Buffers: shared hit=11207
              ->  Nested Loop Left Join (actual time=0.067..8.589 rows=6050 loops=1)
                    Buffers: shared hit=671
                    ->  Index Scan using tr_ord_x51 on tr_ord o
                          (actual time=0.038..2.137 rows=6050 loops=1)
                          Buffers: shared hit=93
                    ->  Memoize (actual time=0.001..0.001 rows=1 loops=6050)
                          Cache Key: o.shop_id
                          Hits: 5761  Misses: 289
                          ->  Index Scan using ms_shop_pk on ms_shop s
                                (actual time=0.003..0.003 rows=1 loops=289)
              ->  Memoize (actual time=0.003..0.003 rows=1 loops=6050)
                    Cache Key: o.mbr_id
                    Hits: 2538  Misses: 3512
                    Buffers: shared hit=10536
                    ->  Index Scan using ms_mbr_pk on ms_mbr m
                          (actual time=0.004..0.004 rows=1 loops=3512)
Planning Time: 2.174 ms
Execution Time: 40.652 ms

Plain Text

복사

GOOD SQL — 실제 실행계획

Nested Loop Left Join (actual time=11.193..11.627 rows=30 loops=1)
  Buffers: shared hit=255
  ->  Nested Loop Left Join (actual time=11.155..11.313 rows=30 loops=1)
        Buffers: shared hit=165
        ->  Limit (actual time=11.118..11.176 rows=30 loops=1)
              Buffers: shared hit=105
              ->  Incremental Sort (actual time=0.190..10.853 rows=6030 loops=1)
                    Sort Key: o.ord_dtm, o.mbr_id, o.ord_no
                    Presorted Key: o.ord_dtm
                    Buffers: shared hit=105
                    ->  Index Scan using tr_ord_x51 on tr_ord o
                          (actual time=0.040..2.207 rows=6050 loops=1)
                          Buffers: shared hit=93
        ->  Index Scan using ms_shop_pk on ms_shop s
              (actual time=0.004..0.004 rows=1 loops=30)
              Buffers: shared hit=60
  ->  Index Scan using ms_mbr_pk on ms_mbr m
        (actual time=0.010..0.010 rows=1 loops=30)
        Buffers: shared hit=90
Planning Time: 1.769 ms
Execution Time: 11.830 ms

Plain Text

복사

위 실행계획은 핵심 연산을 발췌했다. 인덱스 조건과 일부 중간 노드는 생략했으므로 SQL 실행 시 EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)로 전문을 확인할 수 있다.

실행계획 해석: 두 SQL 모두 OFFSET 6000 때문에 주문 인덱스에서 6,050행을 읽어야 했다. GOOD은 OFFSET 비용을 없앤 것이 아니라, 30건만 확정한 이후 부가 JOIN을 수행해 수천 번의 반복 조회를 줄인 것이다.

핵심

•

정렬·페이징 기준이 기준 테이블의 컬럼으로만 결정된다면 필요한 N건을 먼저 확정하고, 화면에 필요한 부가 정보를 그 N건에 대해서만 JOIN하자.

참고·예외

•

매장명 또는 회원등급 등의 JOIN 테이블 컬럼으로 필터링하거나 정렬한다면 JOIN 이후에 페이지를 결정해야 하므로 이 방식으로 무작정 변환하면 결과가 달라질 수 있다.

•

부가 JOIN 대상이 PK·유니크 키가 아닌 1:N 관계라면 중복이 생기므로 두 SQL의 결과가 같다고 보장할 수 없다.

•

LEFT JOIN은 매장·회원 정보가 누락돼도 주문을 유지한다. 외래키·NULL 여부와 INNER JOIN 전환 가능성은 별도 판단한다.

•

PostgreSQL 옵티마이저가 원래 SQL의 JOIN을 효율적으로 처리하는 경우도 있으므로 실제 실행계획을 검증해야 한다. 실행시간은 각 1회 실측 결과로 일반화하지 않는다.