상황: 문자형 varchar(8) 컬럼 startdbpg.tr_ord.ord_ymd에서 20221221의 주문 855건을 조회한다. ord_ymd에는 tr_ord_x52 B-tree 인덱스가 있다.
컬럼 자료형 확인 SQL
SELECT column_name
,data_type
,character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'startdbpg'
AND table_name = 'tr_ord'
ORDER BY ordinal_position;
SQL
복사
•
startdbpg.tr_ord의 전체 컬럼명과 자료형을 조회한다. ord_ymd가 character varying(8)인지 확인하고 아래 BAD/GOOD SQL을 비교하자.
BAD SQL
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS order_cnt
FROM startdbpg.tr_ord o
WHERE o.ord_ymd::integer = 20221221;
SQL
복사
실측 실행계획
Finalize Aggregate (actual time=558.865..574.348 rows=1 loops=1)
Buffers: shared hit=15414 read=1902
-> Gather (actual time=558.688..574.339 rows=2 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=15414 read=1902
-> Partial Aggregate (actual time=550.312..550.313 rows=1 loops=2)
Buffers: shared hit=15414 read=1902
-> Parallel Seq Scan on tr_ord o (actual time=479.800..550.261 rows=428 loops=2)
Filter: ((ord_ymd)::integer = 20221221)
Rows Removed by Filter: 364736
Buffers: shared hit=15414 read=1902
Planning:
Buffers: shared hit=126
Planning Time: 0.597 ms
Execution Time: 574.576 ms
Plain Text
복사
•
인덱스 컬럼 자체를 숫자로 변환하면 기존 문자형 인덱스의 일반적인 탐색 조건을 사용할 수 없을 수 있다.
•
이번 실습에서는 Parallel Seq Scan + Filter가 실행되었고, 실행 버퍼는 shared hit=15414 read=1902였다.
GOOD SQL
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT COUNT(*) AS order_cnt
FROM startdbpg.tr_ord o
WHERE o.ord_ymd = '20221221';
SQL
복사
실측 실행계획
Aggregate (actual time=0.380..0.380 rows=1 loops=1)
Buffers: shared hit=5
-> Index Only Scan using tr_ord_x52 on tr_ord o (actual time=0.159..0.304 rows=855 loops=1)
Index Cond: (ord_ymd = '20221221'::text)
Heap Fetches: 0
Buffers: shared hit=5
Planning:
Buffers: shared hit=123
Planning Time: 16.167 ms
Execution Time: 3.741 ms
Plain Text
복사
•
비교값을 문자로 지정하자 Index Cond가 적용되어 tr_ord_x52를 통한 Index Only Scan이 실행되었다.
•
이번 실측에서는 Heap Fetches: 0, 실행 버퍼 shared hit=5였다.
핵심
•
검색 조건의 비교값은 컬럼의 자료형에 맞춰 작성하자.
•
형변환이 필요하면 가급적 인덱스 컬럼이 아니라 비교값 쪽에서 처리하자. 컬럼 가공은 인덱스 탐색을 어렵게 할 수 있다.
참고
•
ord_ymd = 20221221처럼 문자형 컬럼과 정수 리터럴을 직접 비교하면 이 예제에서는 SQLSTATE 42883 오류가 발생한다.
•
원문 실측 실행시간은 BAD 574.576ms, GOOD 3.741ms였다. 단일 측정값이며 실행 환경에 따라 달라진다.
•
표현식 인덱스가 따로 있다면 컬럼 형변환 시 접근 방식은 달라질 수 있다.
•
ord_ymd는 날짜를 문자로 채운 반정규화 컬럼이며, 원문 실습에서는 날짜 변경 시 자동 동기화되는 구조가 아니다.
원문 미검증 사례 — 별도 확인 필요
•
PREPARE bad_date(text, text)로 timestamp 컬럼을 비교하는 예제는 Supabase SQL 실행 도구에서 자료형 오류가 발생했다. 원문은 DBeaver에서 같은 SQL을 재검증할 필요가 있다고 명시한다.
•
자료형 미정(unknown) 문자열 리터럴은 컬럼 타입에 맞춰 해석될 수 있으므로, 따옴표가 있다는 사실만으로 자료형 불일치를 단정하지 않는다.