인덱스를 만들었는데 쿼리 플래너가 안 쓰는 이유

문제 발생

자주 조회하는 컬럼에 인덱스를 만들었는데도, EXPLAIN으로 확인해보니 여전히 풀 테이블 스캔이 발생하고 있었습니다.

CREATE INDEX idx_users_email ON users(email);

-- 그런데 이 쿼리는 인덱스를 안 씀
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

원인 분석

쿼리 옵티마이저(플래너)는 인덱스가 존재한다고 무조건 쓰는 게 아니라, 실제로 그 인덱스를 쓰는 게 더 빠를지 비용을 계산해서 판단합니다. 인덱스가 무시되는 흔한 원인은 다음과 같습니다.

1. 컬럼에 함수를 씌움: LOWER(email) = ...처럼 컬럼 자체가 아니라 함수의 결과를 비교하면, 일반 인덱스는 원본 컬럼 값 기준으로 정렬되어 있어 이 비교에 쓸 수 없습니다.
2. 타입 불일치: 컬럼이 문자열인데 조건절에 숫자를 넣거나, 반대의 경우 암묵적 타입 변환이 일어나면서 인덱스를 못 타는 경우가 있습니다.
3. 낮은 선택도(selectivity): 예를 들어 status 컬럼에 active/inactive 단 두 값만 있고 90%가 active라면, WHERE status = 'active'는 인덱스를 타도 결국 테이블 대부분을 읽어야 해서 옵티마이저가 오히려 풀 스캔이 더 빠르다고 판단할 수 있습니다 — 이건 옵티마이저가 똑똑하게 판단한 결과이지 버그가 아닙니다.
4. 통계 정보가 오래됨: 테이블 데이터가 많이 바뀌었는데 옵티마이저가 참조하는 통계(테이블/인덱스 통계)가 갱신되지 않으면 잘못된 판단을 할 수 있습니다.
5. LIKE 패턴이 앞에서부터 와일드카드로 시작: LIKE '%example%'처럼 접두사가 고정되지 않은 패턴은 일반 B-tree 인덱스로 효율적으로 찾을 수 없습니다.

해결 방안

1. 함수를 컬럼에 씌워야 한다면 함수 기반 인덱스(expression index)를 만듭니다.

CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- 이제 WHERE LOWER(email) = ... 가 이 인덱스를 탈 수 있음

2. EXPLAIN(MySQL/MariaDB) 또는 EXPLAIN ANALYZE(PostgreSQL)로 실제 실행 계획을 항상 직접 확인합니다 — 인덱스가 있다는 사실만으로 안심하지 않습니다.
3. 통계 정보를 최신으로 유지합니다(ANALYZE TABLE 등 DB별 명령) — 대량 데이터 변경 후에는 특히 중요합니다.
4. 선택도가 낮은 컬럼은 단독 인덱스보다, 자주 함께 조회되는 다른 컬럼과 묶은 복합 인덱스가 더 효과적인 경우가 많습니다.
5. LIKE 검색이 잦다면 일반 인덱스 대신 전문 검색(full-text search) 인덱스나 트라이그램 인덱스(PostgreSQL의 pg_trgm) 같은 전용 구조를 검토합니다.

공식 문서

• PostgreSQL Docs: Using EXPLAIN
• MySQL Docs: Optimization and Indexes