EXPLAIN 앞에서 무너지는 인덱스, 네 가지 몸통 찾기

인덱스를 만들어 놨는데 EXPLAIN에 Seq Scan이 찍힌다. 테이블은 몇 백만 행이고. 처음이면 인덱스가 안 붙었나, ANALYZE를 안 돌렸나부터 확인한다. 둘 다 아니라면 옵티마이저가 그 인덱스를 원천적으로 못 쓰는 쿼리 모양이다. 원인은 네 갈래로 좁혀진다. 순서를 모르고 뒤지면 흔한 함정 하나에 꼬박 몇 시간을 태운다.

함수가 컬럼을 감싸면 인덱스는 신경도 안 쓴다

로그 테이블에서 WHERE date_trunc('day', created_at) = '2026-08-14' 같은 조건은 흔하다. created_at에 인덱스를 만들어도 이 쿼리는 못 쓴다. 인덱스는 컬럼의 원본 값 기준으로 정렬돼 있고, date_trunc를 먹인 값은 그 정렬 순서와 무관하기 때문이다. 조회는 가공된 값으로 하는데, 탐색 시작점을 잡을 길이 없다. 대소문자 검색을 위해 lower(name)을 쓰는 경우도 같은 문제의 변형이다.

해결은 두 갈래. 하나는 WHERE created_at >= '2026-08-14' AND created_at < '2026-08-15'로 범위를 열어주는 것이고, 다른 하나는 date_trunc('day', created_at)에 그대로 표현식 인덱스를 걸어주는 것. 전자가 정석이고 후자는 가공 조건이 자주 나올 때 쓴다. 계획 수립 시 표현식이 인덱스 정의와 문자 그대로 일치해야만 사용되니, 공백 하나만 달라도 무시된다. 범위로 풀 때도 주의점이 있다. BETWEEN으로 묶으면 경계값 계산에서 밀리초 구간을 빼먹기 쉽다. >=와 < 조합이 안전한 이유가 거기 있다. '컬럼의 원형이 조건에 그대로 남는가'가 판정 기준이다.

타입이 어긋나면 컬럼 쪽에 캐스팅이 걸린다

WHERE id = '123'처럼 리터럴 타입이 컬럼 타입과 다르면 PostgreSQL은 어느 한쪽을 캐스팅해서 맞춘다. 상수 쪽을 컬럼 타입으로 바꾸는 캐스팅은 인덱스에 지장을 주지 않는다. 그러나 컬럼 쪽을 상수 타입으로 감싸면 앞의 함수 문제와 똑같아진다. 변환을 컬럼에 먹인 셈이니 정렬 순서가 사라진다.

방향을 결정하는 규칙이 '선호 타입(preferred type)'이라 눈에 잘 안 띈다. timestamptz가 선호 타입이기 때문에, timestamp 컬럼을 timestamptz 값과 비교하면 컬럼 쪽이 캐스팅돼 인덱스가 무너진다. varchar 컬럼과 text 리터럴이 만나는 스키마에서도 같은 방향 문제가 생긴다. WHERE status::text = 'active'처럼 컬럼에 명시적 캐스팅을 다는 실수도 같은 결이다.

이 패턴이 실제로 터지는 곳은 파라미터 바인딩 쪽이다. ORM이나 prepared statement가 리터럴을 varchar로 넘기면 서버는 그 타입을 기준으로 비교식을 구성하고, 컬럼 쪽에 캐스팅을 얹는다. EXPLAIN의 Filter에 (created_at)::date = ... 같은 캐스팅이 보이면 의심이 확정된다. JDBC라면 setString 대신 setBigDecimal, setTimestamp 류를 쓰는 식으로 바인딩 타입을 컬럼과 맞추는 게 해결책이다. 리터럴에 명시적 캐스팅을 붙여 '123'::bigint로 만든 뒤 Seq Scan이 사라지는지 확인하는 것도 같은 검증이다.

LIKE 앞에 %가 붙으면 B-tree는 손을 놓는다

부분 문자열 검색은 B-tree가 애초에 할 수 없는 일이다. LIKE '%게시글%'처럼 앞에 와일드카드가 붙는 순간 탐색 시작점이 서지 않으니까. 접두어 검색 LIKE '게시글%'조차 로케일이 C가 아닌 기본 collation에서는 사전 순서와 인덱스 정렬 순서가 어긋나 탐색이 안 되는 경우가 있다. 한글의 사전 순서와 인덱스의 바이트 순서가 일치하지 않기 때문이다. 한국어 환경에서 자주 겪는 함정이고, 대소문자를 무시하는 ilike 검색도 결국 lower로 풀려 같은 함정에 걸린다.

접두어 검색은 text_pattern_ops 연산자 클래스로 B-tree를 만들어 해결한다. 부분 문자열 검색은 pg_trgm 확장의 GIN 인덱스를 쓰는데, 인덱스도 크고 쓰기 비용도 붙는다. 검색어가 두세 글자면 trigram이 노이즈에 약해져 정확도가 떨어지고, 옵티마이저가 풀스캔을 고르는 경우도 있다. 최소 길이 제한을 걸지 않으면 이 인덱스는 반쯤 죽은 자원이다. 몇 백만 행을 넘어가는 검색은 여기서 인덱스 튜닝을 그치고 외부 검색 엔진 이전을 검토할 시점이기도 하다.

옵티마이저가 '그냥 훑는 게 낫겠다'고 판단하는 경우

인덱스가 유효한데도 풀스캔이 나오는 마지막 갈래는 순수한 비용 계산이다. 백만 행짜리 테이블에서 status가 'active'인 행이 90%라면, WHERE status = 'active' 조회는 인덱스를 탈 수 있어도 안 탄다. 거의 전 테이블을 읽을 건데 인덱스 접근이라는 우회를 하는 편이 오히려 비싸기 때문이다. 선택도가 낮다는 말이 곧 '인덱스가 손해'라는 뜻이다. 이건 버그도 실수도 아니다. 옵티마이저는 틀리지 않았다.

이 판단은 통계 수치를 먹고 산다. 대량 적재 후 ANALYZE를 안 돌렸으면 예상 행 수와 실제 행 수가 크게 갈리고, 그 상태로 나온 풀스캔 결정은 신뢰할 수 없다. EXPLAIN ANALYZE로 예상과 실제 행 수를 나란히 보는 것부터다. 통계가 맞는데도 인덱스를 안 타면 그때야 비용 판단 문제고, random_page_cost나 effective_cache_size를 손대는 건 그다음 일이다. 값이 두세 개만 반복되는 컬럼이라면 조건을 좁혀 부분 인덱스를 만드는 편이 싸다. distinct 값이 3개뿐인 컬럼에 1억 행이 쌓여 있다면 그 컬럼의 인덱스는 처음부터 글러먹은 격이다.

EXPLAIN으로 원인을 좁히는 순서

Seq Scan 아래의 Filter부터 본다. 함수나 캐스팅이 보이면 첫 두 갈래, LIKE면 세 번째, 아무것도 안 보이면 비용 판단이다. 캐스팅은 눈에 잘 안 띈다. 필터 표현에 ::가 있는지, 파서가 만들어낸 캐스팅이 붙어 있는지를 직접 확인해야 한다.

Seq Scan on logs  (cost=0.00..1542.00 rows=6 width=124)
  Filter: ((created_at)::date = '2026-08-14'::date)

이 한 줄이면 충분하다. (created_at)::date는 컬럼에 캐스팅이 얹혔다는 직접 증거다.

가장 쓸모 있는 스위치가 하나 있다. SET enable_seqscan = off;를 걸고 다시 EXPLAIN을 도는 것. 이 상태에서도 인덱스가 안 나오면 쿼리 구조가 원인이고, 갑자기 인덱스가 나타나면 조건은 유효한데 비용 판단이 원인이다. 진단용일 뿐 운영에 남겨두면 안 되니, 비교 후 원래 값으로 되돌린다.

의심 순서도 테이블 규모로 정해진다. 십만 행 수준이면 타입 불일치부터, 백만 행이 넘으면 함수 래핑과 선택도 문제를 같이 본다. 검증은 하나다. 의심되는 조건을 고친 뒤 Seq Scan이 Index Scan으로 바뀌는지 확인한다. 바뀌면 원인 판정이 끝났고, 안 바뀌면 다음 갈래로 넘어간다. 두 갈래가 겹쳐 있는 경우가 흔하니, 하나 고쳤다고 멈추면 안 된다.

인덱스를 만드는 일보다 쿼리의 모양을 바꾸는 일이 먼저다. 네 갈래 중 어느 것도 인덱스 추가만으로는 풀리지 않는다. 함수를 벗기고, 타입을 맞추고, LIKE 앞의 %를 지우는 것. 그렇게 해서 옵티마이저가 풀스캔 대신 Index Scan을 고르는 순간이 와야 성능 얘기의 시작점에 선 것이다.

댓글

댓글을 읽어오는 중입니다.