상황: 닉네임이 Mo로 시작하는 회원을 검색한다. 실습 테이블은 startdbpg.ms_mbr 9,999건, 조건에 맞는 회원은 400건이다. nick_nm은 varchar(100)이고, 기존 B-tree 인덱스는 ms_mbr_x51(nick_nm) INCLUDE (mbr_id)이다.
이번 실습의 핵심: LEFT()를 LIKE 'Mo%'로 바꾸는 것만으로는 충분하지 않을 수 있다. 컬럼 Collation과 인덱스 Collation이 접두사 검색에 맞아야 Index Cond가 생성된다.
BAD SQL — LEFT 함수로 컬럼 가공
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm
FROM startdbpg.ms_mbr m
WHERE LEFT(m.nick_nm, 2) = 'Mo';
SQL
복사
실측 실행계획 (C Collation 적용 후에도 동일한 문제)
Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=1.330..2.563 rows=400 loops=1)
Filter: ("left"((nick_nm)::text, 2) = 'Mo'::text)
Rows Removed by Filter: 9599
Heap Fetches: 0
Buffers: shared hit=42
Planning:
Buffers: shared hit=82
Planning Time: 0.593 ms
Execution Time: 2.697 ms
Plain Text
복사
•
인덱스를 사용했지만 Index Cond가 없다. Index Only Scan으로 인덱스 항목 9,999건을 확인하고 9,599건을 Filter에서 제거했다.
•
컬럼을 가공한 조건 때문에 Collation을 바꾼 뒤에도 인덱스 범위 탐색으로 전환되지 않았다.
중간 단계 — LIKE로 수정했지만 Index Cond가 없다
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm
FROM startdbpg.ms_mbr m
WHERE m.nick_nm LIKE 'Mo%';
SQL
복사
실측 실행계획 (변경 전: 기본 Collation en_US.UTF-8)
Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=0.917..1.822 rows=400 loops=1)
Filter: ((nick_nm)::text ~~ 'Mo%'::text)
Rows Removed by Filter: 9599
Heap Fetches: 0
Buffers: shared hit=42
Planning:
Buffers: shared hit=80
Planning Time: 0.465 ms
Execution Time: 1.941 ms
Plain Text
복사
•
LIKE 'Mo%'는 접두사 검색을 표현하는 올바른 SQL이지만, 이 환경의 기본 Collation과 일반 B-tree 인덱스 조합에서는 Index Cond 없이 전체 인덱스 항목을 필터링했다.
•
실제로 SQL 조건에만 m.nick_nm COLLATE "C" LIKE 'Mo%'를 적용해도 기존 기본 Collation 인덱스를 이용한 범위 탐색은 되지 않았다(Rows Removed by Filter: 9599, shared hit=42). 컬럼/인덱스의 Collation이 핵심이다.
실습 TIP — 대상 컬럼만 COLLATE "C"로 변경
실제로 Supabase PostgreSQL 17에서 실행한 DDL
ALTER TABLE startdbpg.ms_mbr
ALTER COLUMN nick_nm TYPE varchar(100) COLLATE "C";
SQL
복사
•
nick_nm 한 컬럼만 변경했다. mbr_id 등 다른 컬럼의 Collation은 변경하지 않았다.
•
nick_nm의 변경 전 Collation은 기본값(en_US.UTF-8)이었고, 변경 후 C로 확인했다. 기존 ms_mbr_x51 인덱스도 새 Collation에 맞춰 재구성되어 그대로 활용할 수 있다.
•
변경 후 통계 수집을 위해 ANALYZE startdbpg.ms_mbr;도 실행했다.
컬럼 Collation 확인 SQL
SELECT column_name
,data_type
,collation_name
FROM information_schema.columns
WHERE table_schema = 'startdbpg'
AND table_name = 'ms_mbr'
AND column_name = 'nick_nm';
SQL
복사
실측 결과: nick_nm | character varying | C
GOOD SQL — LIKE + C Collation으로 인덱스 범위 탐색
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT m.mbr_id, m.nick_nm
FROM startdbpg.ms_mbr m
WHERE m.nick_nm LIKE 'Mo%';
SQL
복사
실측 실행계획 (C Collation 적용·ANALYZE 후)
Index Only Scan using ms_mbr_x51 on ms_mbr m (actual time=0.050..0.177 rows=400 loops=1)
Index Cond: ((nick_nm >= 'Mo'::text) AND (nick_nm < 'Mp'::text))
Filter: ((nick_nm)::text ~~ 'Mo%'::text)
Heap Fetches: 0
Buffers: shared hit=5
Planning:
Buffers: shared hit=106
Planning Time: 0.569 ms
Execution Time: 0.309 ms
Plain Text
복사
•
Index Cond 생성: nick_nm >= 'Mo'부터 nick_nm < 'Mp'까지 접두사에 해당하는 인덱스 구간만 탐색한다.
•
Filter가 남아 있어도 문제는 아니다. 범위로 좁힌 후보가 원래 LIKE 'Mo%' 조건을 만족하는지 재확인하는 조건이다. Rows Removed by Filter: 9599가 사라진 것이 중요하다.
•
조회 결과는 400건으로 동일, 실행 버퍼는 42 → 5블록으로 줄었다.
실측 비교
SQL / 인덱스 조건 | Index Cond | Rows Removed by Filter | 실행 버퍼 | 실행시간 |
LEFT(nick_nm, 2) = 'Mo' / C | 없음 | 9,599 | hit 42 | 2.697ms |
nick_nm LIKE 'Mo%' / 기본 Collation | 없음 | 9,599 | hit 42 | 1.941ms |
nick_nm LIKE 'Mo%' / C | 있음 | 0 | hit 5 | 0.309ms |
핵심
•
접두사 검색은 컬럼을 가공하지 말고 LIKE '문자열%'로 작성하자.
•
하지만 LIKE로만 바꿔서는 충분하지 않을 수 있다. 인덱스가 해당 접두사 검색을 범위 탐색하도록 Collation 또는 인덱스 연산자 클래스를 확인하자.
•
Index Only Scan이라는 노드명보다 Index Cond, Rows Removed by Filter, Buffers를 함께 확인하는 것이 중요하다.
참고·예외
•
C Collation 변경은 문자열의 정렬·비교 규칙에도 영향을 준다. 서비스의 모든 컬럼에 일괄 적용하는 권장 설정이 아니다. 실제 운영 DB에서는 기존 쿼리의 정렬·비교 의미, 인덱스 재구성 및 잠금 영향을 검토해야 한다.
•
컬럼 Collation을 바꾸기 어렵다면 기존 Collation을 유지한 채 별도 varchar_pattern_ops 인덱스를 만드는 방법도 있다. 이번 실습에서는 추가 인덱스를 생성하지 않고 nick_nm만 변경했다.
•
LIKE '%Mo%'는 접두사 검색이 아니므로 이번 B-tree 범위 탐색 방식과 다르다.
•
측정값은 단일 실행 결과다. 실행시간 차이를 항상 같은 배율의 성능 개선으로 일반화하지 않는다. 다만 Index Cond의 생성과 버퍼 감소는 실측으로 확인했다.