상황: 회원별 최근 주문일자를 YYYYMMDD 문자열로 출력한다. 실제 일반 테이블 startdbpg.tr_ord에서 주문이 2,150건 있는 회원 M0035를 선택했다. 기존의 M00100은 주문이 0건이어서 인덱스 비교 실습에 적합하지 않다.
BAD SQL — 모든 주문일시를 문자열로 변환한 후 MAX
SELECT MAX(TO_CHAR(o.ord_dtm, 'YYYYMMDD')) AS last_ord_ymd
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M0035';
SQL
복사
MAX의 대상이 원본 TIMESTAMP가 아니라 TO_CHAR의 결과 문자열이다. 따라서 원본 ord_dtm의 B-tree 정렬 순서를 이용해 최신 1건만 읽는 최댓값 최적화를 적용하기 어려워진다.
GOOD SQL — 원본 컬럼으로 MAX를 먼저 수행하고 최종 결과만 변환
SELECT TO_CHAR(MAX(o.ord_dtm), 'YYYYMMDD') AS last_ord_ymd
FROM startdbpg.tr_ord o
WHERE o.mbr_id = 'M0035';
SQL
복사
MAX(ord_dtm)는 원본 날짜·시각 컬럼의 최댓값을 찾는다. 조건과 정렬을 지원하는 인덱스가 있으면 PostgreSQL이 LIMIT 1 기반의 최댓값 검색으로 최적화할 수 있다.
실제 인덱스 구성 및 생성
기존 인덱스에는 tr_ord_fk01(mbr_id)와 tr_ord_x51(ord_dtm)가 각각 존재하지만, (mbr_id, ord_dtm) 복합 인덱스는 없었다. 실제 스키마에서 50번대 x51, x52, x53 이름 사용을 확인한 후 다음 인덱스를 실제로 생성했다.
CREATE INDEX IF NOT EXISTS tr_ord_x54
ON startdbpg.tr_ord (mbr_id, ord_dtm);
SQL
복사
•
생성 결과: startdbpg.tr_ord_x54 생성 성공
•
실제 정의: CREATE INDEX tr_ord_x54 ON startdbpg.tr_ord USING btree (mbr_id, ord_dtm)
•
인덱스 크기: 22MB (조회 시점)
•
데이터 입력·수정 시 인덱스 유지 비용과 저장 공간이 추가되므로 실습 목적과 운영 목적의 인덱스 추가는 구분해야 한다.
실제 데이터 / 결과 동등성
•
회원 M0035: 총 2,150건; 마지막 주문일시 2025-01-25 07:16:00
•
BAD 결과: 20250125
•
GOOD 결과: 20250125
•
추가 검증 M0039: 138건, BAD·GOOD 모두 20250119
•
주문이 없는 M00100: BAD·GOOD 모두 NULL
인덱스 생성 전 실행계획 — 실제 실측
항목 | BAD | GOOD |
스캔 방식 | tr_ord_fk01(mbr_id) Index Scan | tr_ord_x51(ord_dtm) Index Scan Backward + 회원 Filter |
스캔에서 읽은 행 수 | 2,150 | 1 (필터 제거 75행) |
Shared Buffer Hit | 2,060 | 7 |
실행시간 (각 1회) | 12.570ms | 2.357ms |
기존 날짜 단일 인덱스에서도 GOOD은 MAX(ord_dtm) 최적화가 일어나 날짜 인덱스를 역방향으로 읽고 회원 조건에 일치하는 최신 주문 1건을 찾았다. 다만 회원 조건이 인덱스 선두 키에 없어서 다른 회원 주문 75행을 필터링했다.
인덱스 생성 후 실행계획 — 실제 실측 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))
항목 | BAD: MAX(TO_CHAR(...)) | GOOD: TO_CHAR(MAX(...)) |
사용 인덱스 | tr_ord_x54 | tr_ord_x54 |
스캔 방식 | Index Only Scan | Index Only Scan Backward |
인덱스 스캔 반환 행 수 | 2,150 | 1 |
Heap Fetches | 0 | 0 |
Buffers: shared hit | 12 | 4 |
실행시간 (재실행 각 1회) | 2.387ms | 0.447ms |
생성 직후의 캐시 워밍·동시 부하 영향을 줄이기 위해 인덱스 접근 후 재실행한 측정값이다. 다른 반복 실행에서는 BAD 2.771ms, GOOD 0.655ms도 관찰됐다. 실제 시간은 변동하므로 배율은 고정되지 않는다.
BAD SQL — 인덱스 생성 후 실제 실행계획
Aggregate (actual time=2.244..2.245 rows=1 loops=1)
Buffers: shared hit=12
-> Index Only Scan using tr_ord_x54 on tr_ord o
(actual time=0.072..0.538 rows=2150 loops=1)
Index Cond: (mbr_id = 'M0035'::text)
Heap Fetches: 0
Buffers: shared hit=12
Planning Time: 0.746 ms
Execution Time: 2.387 ms
Plain Text
복사
GOOD SQL — 인덱스 생성 후 실제 실행계획
Result (actual time=0.207..0.208 rows=1 loops=1)
Buffers: shared hit=4
InitPlan 1
-> Limit (actual time=0.106..0.107 rows=1 loops=1)
Buffers: shared hit=4
-> Index Only Scan Backward using tr_ord_x54 on tr_ord o
(actual time=0.105..0.106 rows=1 loops=1)
Index Cond: ((mbr_id = 'M0035'::text) AND (ord_dtm IS NOT NULL))
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.995 ms
Execution Time: 0.447 ms
Plain Text
복사
실행계획 읽기: BAD는 복합 인덱스를 사용하더라도 해당 회원의 2,150행을 읽고 각 ord_dtm을 TO_CHAR로 변환해 최대 문자열을 집계했다. GOOD은 mbr_id 동등 조건으로 인덱스 구간을 지정하고 ord_dtm을 역방향으로 읽어 첫 1건에서 멈췄다. 중요한 차이는 인덱스 사용 여부 자체가 아니라 읽어야 하는 인덱스 행 수다.
핵심
•
MAX/MIN의 대상 컬럼을 불필요하게 가공하지 말자. 최댓값을 찾을 때는 원본 컬럼을 사용하고, 문자 형식 등 출력용 변환은 집계가 끝난 후 적용하자. 해당 검색 조건과 집계 컬럼을 지원하는 인덱스가 있으면 최소 접근으로 끝날 수 있다.
참고·예외
•
이 사례에서는 TIMESTAMP를 YYYYMMDD 문자열로 표현하는 최신 일자가 목적이므로 두 쿼리의 결과가 동일했다. 다른 문자열 형식이나 비단조 함수·타임존 변환·자료형 처리에서는 MAX(f(x)) = f(MAX(x))가 성립한다고 단정하면 안 된다.
•
ord_dtm IS NULL 데이터가 없는 실제 표본에서 검증했다. 주문 0건이면 두 방식 모두 집계 결과 NULL이다.
•
Index Only Scan에서 Heap Fetches = 0인 것은 현재 visibility map 등 접근 조건에 따른 결과이며 언제나 보장되는 것은 아니다.
•
복합 인덱스가 없어도 다른 인덱스를 통해 최댓값 최적화가 가능할 수 있다. 인덱스는 추가 공간·쓰기 비용이 있으므로 필요성을 실제 SQL 빈도·실행계획으로 판단한다.