상황: 2023년 1월 5일 주문에서 회원별 가장 큰 주문번호를 가진 주문 한 건과 회원 닉네임을 구한다. 실제 일반 테이블 startdbpg.tr_ord, startdbpg.ms_mbr를 사용했다. 해당 날짜 주문은 590건, 회원은 380명이고, 정상 결과는 회원별 1행씩 380건이다.
BAD SQL — 날짜를 먼저 제한해도 주문마다 상관 MAX를 반복
SELECT o.mbr_id, m.nick_nm,
o.ord_no, o.ord_dtm, o.ord_amt
FROM startdbpg.tr_ord o
JOIN startdbpg.ms_mbr m
ON m.mbr_id = o.mbr_id
WHERE o.ord_dtm >= TIMESTAMP '2023-01-05'
AND o.ord_dtm < TIMESTAMP '2023-01-06'
AND o.ord_no = (
SELECT MAX(x.ord_no)
FROM startdbpg.tr_ord x
WHERE x.ord_dtm >= TIMESTAMP '2023-01-05'
AND x.ord_dtm < TIMESTAMP '2023-01-06'
AND x.mbr_id = o.mbr_id
)
ORDER BY o.mbr_id;
SQL
복사
바깥쪽 주문을 하루로 제한했는데도, WHERE의 상관 서브쿼리가 후보 주문 590행 각각에 대해 MAX(ord_no)를 재계산했다. 같은 회원의 주문이 여러 건이면 그 회원의 최대 주문번호를 중복 계산하게 된다.
GOOD SQL — 해당 날짜 주문을 회원별로 한 번 집계한 후 필요한 행과 JOIN
WITH last_no AS (
SELECT x.mbr_id,
MAX(x.ord_no) AS ord_no
FROM startdbpg.tr_ord x
WHERE x.ord_dtm >= TIMESTAMP '2023-01-05'
AND x.ord_dtm < TIMESTAMP '2023-01-06'
GROUP BY x.mbr_id
)
SELECT o.mbr_id, m.nick_nm,
o.ord_no, o.ord_dtm, o.ord_amt
FROM last_no l
JOIN startdbpg.tr_ord o
ON o.ord_no = l.ord_no
JOIN startdbpg.ms_mbr m
ON m.mbr_id = o.mbr_id
ORDER BY o.mbr_id;
SQL
복사
날짜 범위의 590건을 한 번 읽고 회원별 MAX(ord_no)를 380건으로 선집계한다. 이후 주문 PK(ord_no)와 회원 PK(mbr_id)로 상세 정보에 접근한다.
실제 데이터 / 결과 검증
•
2023-01-05 주문 590건, 서로 다른 회원 380명.
•
BAD와 GOOD 모두 380건을 반환했다.
•
출력 컬럼 전체에 대해 EXCEPT ALL을 양방향으로 실행한 결과 차이 0건이다.
•
원문 BAD에는 바깥쪽 날짜 조건이 없었다. 이번에는 BAD에도 같은 날짜 조건을 추가해 후보 범위를 같게 만들고, 상관 집계 반복과 선집계의 차이에 집중했다. 원래 BAD가 더 넓은 범위를 검사할 수 있다는 사실과 별개의 비교다.
실제 실행계획·성능 비교 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))
항목 | BAD: 행별 상관 MAX | GOOD: 회원별 선집계 |
대상 날짜 주문 | 590건 | 590건 |
MAX(ord_no) 처리 | SubPlan 590회 실행 | HashAggregate 1회 |
집계 결과 회원 | 개별 주문의 조건 검사 후 380건 | 380개 그룹 |
최종 반환 행 | 380건 | 380건 |
Buffers: shared hit | 3,856 | 2,676 |
실행시간 (재측정 각 1회) | 9.703ms | 5.578ms |
BAD SQL — 실제 실행계획 (핵심 노드 발췌)
Sort (actual time=9.467..9.503 rows=380 loops=1)
Sort Key: o.mbr_id
Buffers: shared hit=3856
-> Nested Loop (actual time=0.268..8.845 rows=380 loops=1)
-> Index Scan using tr_ord_x51 on tr_ord o
(actual time=0.078..6.652 rows=380 loops=1)
Index Cond: (ord_dtm >= '2023-01-05' AND ord_dtm < '2023-01-06')
Filter: (ord_no = (SubPlan 1))
Rows Removed by Filter: 210
SubPlan 1
-> Aggregate (actual time=0.010..0.010 rows=1 loops=590)
-> Index Scan using tr_ord_x54 on tr_ord x
Index Cond: (mbr_id = o.mbr_id AND ord_dtm 범위)
-> Index Scan using ms_mbr_pk on ms_mbr m
Planning Time: 1.918 ms
Execution Time: 9.703 ms
Plain Text
복사
위 Index Cond는 비교 기간을 읽기 쉽게 줄여 쓴 발췌 표현이다. 원본 계획에는 PostgreSQL 자료형 변환·날짜 상수 등이 상세하게 출력된다.
GOOD SQL — 실제 실행계획 (핵심 노드 발췌)
Sort (actual time=5.280..5.337 rows=380 loops=1)
Sort Key: o.mbr_id
Buffers: shared hit=2676
-> Nested Loop (actual time=0.696..4.081 rows=380 loops=1)
-> Nested Loop (actual time=0.674..1.966 rows=380 loops=1)
-> HashAggregate (actual time=0.634..0.763 rows=380 loops=1)
Group Key: x.mbr_id
Batches: 1 Memory Usage: 73kB
-> Index Scan using tr_ord_x51 on tr_ord x
(actual time=0.038..0.268 rows=590 loops=1)
-> Index Scan using tr_ord_pk on tr_ord o
Index Cond: (ord_no = max(x.ord_no))
-> Index Scan using ms_mbr_pk on ms_mbr m
Planning Time: 2.841 ms
Execution Time: 5.578 ms
Plain Text
복사
실행계획 읽기: BAD는 tr_ord_x54(mbr_id, ord_dtm) 복합 인덱스가 있어도 SubPlan loops=590으로 같은 목적의 MAX 조회를 반복했다. GOOD은 tr_ord_x51(ord_dtm)로 기간 주문 590건을 읽은 다음 HashAggregate로 380개 그룹을 한 번에 만들었다. 이 사례는 인덱스 추가가 아니라 SQL 작성 방식의 변화에 의한 반복 작업 감소를 보여준다. 실행시간은 캐시·부하에 따라 달라지며 다른 재측정에서도 BAD 9.910ms, GOOD 4.870ms가 관찰됐다.
핵심
•
여러 행을 처리하면서 같은 그룹의 MAX를 상관 서브쿼리로 반복 계산한다면, 필요한 범위를 먼저 읽고 그룹별로 한 번 집계한 후 JOIN하자.
참고·예외
•
여기서 '마지막'은 그날 회원별 ord_no 최댓값이다. 가장 최근 주문일시와 같은 뜻이라는 보장은 없다.
•
ord_no가 PK이므로 최대 주문번호에 해당하는 주문 행은 하나뿐이다. 집계에 여러 값을 함께 출력하거나 1:N 관계에 JOIN할 때는 반환 행 수를 다시 확인해야 한다.
•
PostgreSQL 옵티마이저가 상관 서브쿼리를 효율적으로 처리하거나, 외부 후보가 아주 적은 경우에는 선집계가 더 빠르다고 단정할 수 없다.
•
09-05는 같은 업무를 ROW_NUMBER()로 해결하는 다른 패턴이다. 어느 방식이 빠른지는 별도 실행계획 비교가 필요하다.