상황: 회원 M1765의 2023년 1월~2025년 1월 주문을 대상으로 전체 주문 건수와 픽업 완료 주문 건수(ord_st = 'PKUP')를 함께 조회한다. 일반 테이블 startdbpg.tr_ord를 실제 조회했다. 기존 M00019의 2023년 3월은 일반 테이블에서 주문이 없고, M0001의 2023년 3월은 31건이 전부 PKUP이므로, 두 건수가 실제로 다른 조건(102건/100건)으로 실습 범위를 바꿨다.
BAD SQL — 같은 범위의 주문을 별도 서브쿼리에서 두 번 검색
SELECT
(SELECT COUNT(*)
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M1765'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-02-01') AS all_cnt,
(SELECT COUNT(*)
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M1765'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-02-01'
AND o.ord_st = 'PKUP') AS pkup_cnt;
SQL
복사
이 SQL에서 PostgreSQL은 실제로 별도 InitPlan 두 개를 만들었다. 첫 번째 COUNT(*)는 tr_ord_x54의 Index Only Scan으로 102건을 확인하고, 두 번째는 같은 인덱스의 Index Scan으로 102건 중 2건을 Filter로 제거했다. 두 번의 원본 인덱스 접근이 발생했다.
비교안 — MATERIALIZED로 중간 결과를 만든 뒤 두 번 재사용
WITH orders AS MATERIALIZED (
SELECT o.ord_st
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M1765'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-02-01'
)
SELECT
(SELECT COUNT(*) FROM orders) AS all_cnt,
(SELECT COUNT(*) FROM orders WHERE ord_st = 'PKUP') AS pkup_cnt;
SQL
복사
원본 주문에 대한 Index Scan은 한 번(102건)만 수행하고, 구체화한 orders를 CTE Scan 2회로 다시 사용했다. 원본 테이블 재탐색은 줄지만, 중간 결과도 두 번 읽는다. 따라서 여기서는 MATERIALIZED를 무조건 GOOD으로 보지 않는다.
GOOD SQL — 조건부 집계로 한 번에 계산
SELECT COUNT(*) AS all_cnt,
COUNT(*) FILTER (
WHERE o.ord_st = 'PKUP'
) AS pkup_cnt
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M1765'
AND o.ord_dtm >= TIMESTAMP '2023-01-01'
AND o.ord_dtm < TIMESTAMP '2025-02-01';
SQL
복사
같은 범위에서 필요한 지표 두 개를 구하는 업무라면 Aggregate 한 번으로 처리할 수 있다. 원본 테이블에 대한 Index Scan 1회 외에 CTE Scan이나 중복 서브쿼리 접근이 없다.
실제 데이터 및 결과 동등성
•
M1765의 해당 기간 주문은 102건이며 PKUP 100건, 다른 상태 2건이다.
•
세 SQL 모두 all_cnt=102, pkup_cnt=100인 1행을 반환했다.
•
양방향 EXCEPT ALL로 비교한 결과 차이 0건이다.
•
사용한 인덱스는 기존 tr_ord_x54(mbr_id, ord_dtm)이며 새 인덱스는 생성하지 않았다.
실제 실행계획 비교 — EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
비교 항목 | BAD: 독립 COUNT 2회 | 비교안: MATERIALIZED | GOOD: COUNT FILTER |
원본 테이블 접근 | Index Only Scan + Index Scan, 2회 | Index Scan 1회 | Index Scan 1회 |
CTE Scan | 없음 | 2회 | 없음 |
주요 집계 | Aggregate 2회 | Aggregate 2회 | Aggregate 1회 |
Shared Buffer Hit | 52 | 47 | 47 |
실행시간 (최초 비교) | 0.865ms | 0.762ms | 0.601ms |
재실행 관찰 범위 | 0.661~0.728ms | 0.691~0.713ms | 0.574~0.654ms |
실측 시간 차이가 매우 작고 실행별로 변동하므로, 이 사례에서 MATERIALIZED가 반복 COUNT보다 항상 빠르다고 단정할 수 없다. 비교의 핵심은 접근 구조와 Buffers다. 위 측정에서는 COUNT FILTER가 가장 단순했다.
BAD SQL — 실제 실행계획 핵심 노드
Result (rows=1 loops=1)
Buffers: shared hit=52
InitPlan 1
-> Aggregate (rows=1 loops=1)
-> Index Only Scan using tr_ord_x54 on tr_ord o
(rows=102 loops=1)
Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
Heap Fetches: 0
Buffers: shared hit=5
InitPlan 2
-> Aggregate (rows=1 loops=1)
-> Index Scan using tr_ord_x54 on tr_ord o_1
(rows=100 loops=1)
Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
Filter: (ord_st = 'PKUP')
Rows Removed by Filter: 2
Buffers: shared hit=47
Execution Time: 0.865 ms
Plain Text
복사
MATERIALIZED — 실제 실행계획 핵심 노드
Result (rows=1 loops=1)
Buffers: shared hit=47
CTE orders
-> Index Scan using tr_ord_x54 on tr_ord o
(rows=102 loops=1)
Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
InitPlan 2
-> Aggregate (rows=1 loops=1)
-> CTE Scan on orders (rows=102 loops=1)
InitPlan 3
-> Aggregate (rows=1 loops=1)
-> CTE Scan on orders orders_1 (rows=100 loops=1)
Filter: (ord_st = 'PKUP')
Rows Removed by Filter: 2
Execution Time: 0.762 ms
Plain Text
복사
COUNT FILTER — 실제 실행계획 핵심 노드
Aggregate (rows=1 loops=1)
Buffers: shared hit=47
-> Index Scan using tr_ord_x54 on tr_ord o
(rows=102 loops=1)
Index Cond: (mbr_id='M1765' AND ord_dtm 기간)
Execution Time: 0.601 ms
Plain Text
복사
실제 계획의 노드 유형·행 수·loops·버퍼·시간을 유지하면서 PostgreSQL의 자료형 변환 표기와 날짜 경계값 등은 축약했다. FILTER 절은 집계 함수 내부에서 평가되므로 별도 Filter 노드가 없는 것이 정상이다.
핵심
•
여러 번 참조할 중간 결과가 있다고 해서 MATERIALIZED부터 선택하지 말자. 먼저 하나의 GROUP BY 또는 COUNT(*) FILTER로 필요한 값을 한 번에 구할 수 있는지 확인하자. 중복 조회를 없앨 다른 방법이 없고 동일한 계산 결과를 여러 곳에서 재사용한다면 MATERIALIZED를 검토한다.
원본 규칙: 개편 전 08-02.
참고·예외
•
COUNT(*) FILTER는 하나의 입력 범위에서 여러 집계를 수행할 때 유용하지만, 이후 소비자가 서로 다른 상세 행을 필요로 하는 경우까지 모두 대체하지는 못한다.
•
MATERIALIZED는 중간 결과 저장·CTE Scan 비용이 생기고, 외부 WHERE 조건이 CTE 내부로 전달되지 못할 수 있다(10-05 참고).
•
같은 원본 범위라도 서로 다른 인덱스 조건이 가능하다면, 두 번의 선택적인 인덱스 탐색이 하나의 구체화보다 유리한 경우가 있다. 계획과 Buffers를 확인한다.
•
COUNT(*) FILTER (WHERE ord_st='PKUP')에서 특정 주문상태에 해당하는 행만 집계되지만 그 조건이 자동으로 인덱스 검색 범위를 줄인다는 뜻은 아니다. 이 계획에서는 mbr_id, ord_dtm만 Index Cond에 포함됐다.