상황: startdbpg.tr_ord는 주문일시(ord_dtm)에서 날짜만 추출한 YYYYMMDD 8자리 문자형 반정규화 컬럼 ord_ymd varchar(8)를 사용한다. ord_ymd에는 B-tree 인덱스 tr_ord_x52가 있다. 실제 주문 730,327건 중 2023년 4월 1일 주문은 729건이다.
BAD SQL — 문자형 날짜 컬럼을 숫자로 변환
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM startdbpg.tr_ord o
WHERE o.ord_ymd::numeric = 20230401;
SQL
복사
실측 실행계획 (BAD)
Finalize Aggregate (actual time=496.329..512.182 rows=1 loops=1)
Buffers: shared hit=7 read=648
-> Gather (actual time=495.923..512.173 rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=7 read=648
-> Partial Aggregate (actual time=485.842..485.844 rows=1 loops=2)
Buffers: shared hit=7 read=648
-> Parallel Index Only Scan using tr_ord_x52 on tr_ord o (actual time=229.812..485.798 rows=364 loops=2)
Filter: ((ord_ymd)::numeric = '20230401'::numeric)
Rows Removed by Filter: 364799
Heap Fetches: 0
Buffers: shared hit=7 read=648
Planning:
Buffers: shared hit=132
Planning Time: 4.199 ms
Execution Time: 512.870 ms
Plain Text
복사
GOOD SQL — 날짜를 저장된 문자 형식 그대로 비교
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS ord_cnt
FROM startdbpg.tr_ord o
WHERE o.ord_ymd = '20230401';
SQL
복사
실측 실행계획 (GOOD)
Aggregate (actual time=1.466..1.467 rows=1 loops=1)
Buffers: shared hit=5
-> Index Only Scan using tr_ord_x52 on tr_ord o (actual time=1.315..1.399 rows=729 loops=1)
Index Cond: (ord_ymd = '20230401'::text)
Heap Fetches: 0
Buffers: shared hit=5
Planning:
Buffers: shared hit=128
Planning Time: 0.703 ms
Execution Time: 1.645 ms
Plain Text
복사
실측 비교: 두 SQL 모두 729건을 반환한다. BAD는 tr_ord_x52를 사용한 Parallel Index Only Scan이지만 조건이 Index Cond가 아닌 Filter에 남아 인덱스를 넓게 스캔한다. BAD의 Rows Removed by Filter: 364799는 loops=2인 병렬 작업당 평균값이다. GOOD은 Index Cond로 필요한 날짜의 인덱스 항목만 탐색한다. 실행 버퍼는 BAD 655블록(hit=7, read=648), GOOD 5블록(hit=5)이었다. 실행시간 512.870ms와 1.645ms는 각각 실측 1회 결과로 캐시·부하 등에 따라 달라질 수 있다.
자료형 불일치 오류도 검증
SELECT COUNT(*) AS ord_cnt
FROM startdbpg.tr_ord o
WHERE o.ord_ymd = 20230401;
SQL
복사
PostgreSQL 실행 결과: SQLSTATE 42883: operator does not exist: character varying = integer. 오류를 해결하려고 ord_ymd::numeric으로 컬럼을 바꾸는 대신, 날짜 값을 '20230401'처럼 컬럼과 같은 문자형으로 비교해야 한다.
핵심
•
YYYYMMDD 형태로 저장한 날짜 문자열도 인덱스 컬럼 자체를 변환하지 말자. 비교값을 컬럼 형식에 맞추면 인덱스가 날짜 문자열의 원본 값으로 검색할 수 있다. 8자리로 통일된 유효한 YYYYMMDD 문자열은 날짜 순서와 문자열 정렬 순서가 일치하므로, 날짜 범위도 문자값으로 표현할 수 있다.
참고·예외
•
ord_ymd는 실제 DATE/TIMESTAMP 자료형이 아니라 날짜를 담은 문자형 컬럼이다. 이 반정규화 방식 자체를 일반적으로 권장하는 것은 아니다. 잘못된 날짜 문자열을 저장하지 않도록 검증이 필요하며, ord_dtm이 바뀌면 ord_ymd도 동기화해야 한다. 현재 데이터는 두 컬럼이 일치하지만 변경 시 자동 동기화 기능은 설정하지 않았다. 표현식 인덱스를 따로 설계하면 BAD의 실행계획이 달라질 수 있다.