상황: GOLD 회원 전체를 대상으로, 회원마다 가장 최근 주문 한 건의 주문번호와 주문금액을 조회한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord로 검증했다. 최신 주문일시가 같으면 주문번호(ord_no)가 큰 주문을 선택한다.
BAD SQL — 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 SQL — 회원별 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 SQL — 핵심 실행계획 — 원래 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 SQL — 핵심 실행계획 — 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을 검토하자. 특히 외부 행이 상대적으로 적고, (외부 키, 정렬 키) 인덱스가 있으면 전체 그룹을 윈도 집계하거나 최댓값을 선집계하는 접근을 줄일 수 있다.
원본 규칙: 개편 전 07-04.
참고·예외
•
MAX(주문일시) + JOIN은 동률 주문을 모두 반환할 수 있으므로 한 건 선택과 요구사항이 다르다. ord_no DESC 같은 유일한 동률 기준을 명시해야 한다.
•
GOLD 회원 3,146명에 대해 LATERAL 내부 탐색이 3,146회 수행되므로 항상 빠른 것은 아니다. 회원 수가 훨씬 많거나 정렬 지원 인덱스가 없으면 반복 탐색·정렬 비용이 커진다. 10-04에서 반대 조건을 검토한다.
•
다른 인덱스·통계·데이터 분포·캐시 상태에서는 ROW_NUMBER나 선집계가 유리할 수 있다. 여기서 검증된 수치를 일반적인 속도 배율로 해석해서는 안 된다.
•
실제 ord_dtm NULL은 0건이다. NULL이 생길 수 있는 업무에서는 PostgreSQL의 DESC 기본 NULL 정렬 위치가 FIRST임을 고려해야 한다.