만들어 둔 인덱스를 쿼리가 외면하는 순간들, EXPLAIN으로 사인을 읽는 법

PostgreSQL에서 인덱스를 만들어도 풀스캔이 뜨는 일은 흔하다. EXPLAIN 결과에 Seq Scan이 찍히는 순간, "인덱스가 왜 무시됐지" 하는 당혹감이 따라온다. 하지만 대부분의 경우 인덱스가 안 만들어진 게 아니다. 만들어 놓고도 쿼리가 외면하는 것뿐이다. 쿼리와 인덱스 사이의 연결을 끊는 패턴은 대체로 네 가지로 좁혀진다. 함수로 감싼 컬럼, 타입 불일치, LIKE의 선행 와일드카드, 그리고 낮은 선택도. 하나씩 실행 계획이 어떻게 왜곡되는지 보자.

컬럼에 함수가 붙는 순간 인덱스는 이론이 된다

WHERE lower(email) = 'foo@example.com' 같은 조건이 대표적이다. email 컬럼에 btree 인덱스를 만들어도 이 조건은 인덱스를 타지 않는다. 인덱스 안에는 함수를 거치기 전의 원본 값만 들어 있다. 플래너는 lower(email)이라는 표현식 전체를 인덱스에서 찾아야 하는데, 그런 표현식은 어디에도 없다. 결국 테이블 전체를 훑으며 매 행마다 함수를 호출한다.

이 상황의 해법은 함수 인덱스다. CREATE INDEX idx_email_lower ON users ((lower(email)));처럼 인덱스 표현식으로 함수를 넣으면, 조건의 표현식과 문자열 단위로 일치할 때 인덱스를 붙는다. 조건을 LOWER(email)이라고 대문자로 쓰는 건 정규화 때문에 대개 매칭되지만, lower(email || '')처럼 형태가 조금만 달라져도 플래너는 별개의 표현식으로 취급한다. 기능이 같아도 문자열이 다르면 인덱스는 죽는다.

함수 인덱스는 기본적으로 immutable 함수만 받는다는 제약도 있다. lower()는 문제없지만, now()처럼 호출마다 결과가 바뀌는 함수는 인덱스에 못 넣는다. 그리고 이 패턴의 진짜 교훈은 애플리케이션 쪽에 있다. 로그인마다 lower()를 붙여 조회해야 한다면, 저장 시점에 이메일을 소문자로 정규화하고 일반 btree 인덱스를 거는 편이 관리상 낫다. 함수 인덱스는 동작은 하지만, 조건과 인덱스 표현식을 두 곳에서 맞춰 유지해야 하는 비용을 남긴다. 조회 빈도가 낮은 곳이라면 WHERE email = lower($1)처럼 상수 쪽만 감싸는 방법도 고려해 볼 만하다.

타입 불일치는 Filter: 조건에 흔적을 남긴다

두 번째 패턴은 조건과 컬럼 타입이 어긋나는 경우다. 문자열끼리는 대체로 자비롭다. varchar 컬럼과 텍스트 리터럴의 비교는 봐주는 경우가 많다. 골치아픈 쪽은 숫자와 시간이다. integer 컬럼에 문자열 숫자를 넘기거나, timestamp 컬럼을 date와 직접 비교하면 암묵적 캐스팅이 개입한다. 문제는 캐스팅이 어느 쪽에 붙느냐다. 컬럼에 붙으면 그 순간 인덱스는 무너지고, 리터럴에 붙으면 무사하다.

EXPLAIN에서 사인을 읽는 법은 간단하다. Filter: 조건에 created_at::date 같은 표기가 컬럼 옆에 붙어 있다면, 그 컬럼은 이미 인덱스를 못 타는 상태가 된 것이다. 반대로 '2026-01-01'::date처럼 리터럴에 캐스팅이 붙어 있으면 정상이다. 해결은 대개 리터럴을 컬럼 타입에 맞추는 일로 끝난다. WHERE created_at >= DATE '2026-01-01'처럼 써 주면 컬럼이 감싸지지 않으니 인덱스가 살아난다. ORM이 이런 캐스팅을 은밀하게 넣는 경우도 있어서, SQL을 고쳐도 계획이 변하지 않으면 앱이 실제로 보내는 쿼리를 로깅해 비교해 보는 편이 빠르다.

LIKE에 앞쪽 %가 붙으면 btree는 답이 없다

세 번째, 가장 자주 목격하는 패턴이다. LIKE '%keyword%'는 앞에 %가 붙는 순간 btree 인덱스의 영역을 벗어난다. btree는 정렬된 범위 검색만 할 줄 알지, 중간에 끼어 있는 문자열을 찾을 방법은 없다. LIKE 'abc%'처럼 접두사가 고정이면 btree가 낀다. 그 차이 하나로 실행 계획이 갈린다.

중간 부분 검색이 꼭 필요하면 인덱스 종류를 바꿔야 한다. pg_trgm 확장이 만드는 trigram 인덱스는 %keyword%도 인덱스로 줄여 준다.

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING gin (name gin_trgm_ops);

trigram 인덱스는 문자열을 세 글자 단위로 쪼개 저장한다. 그래서 검색어가 세 글자보다 짧으면 도움이 반쪽이다. 두 글자 검색이 유독 느리게 느껴진다면 trigram 인덱스의 특성 탓일 가능성이 크다. ILIKE를 쓰면 대소문자 구분이 없어지는 대신 trigram 인덱스가 붙는 조건도 함께 바뀐다. 여기까지 오면 인덱스만으로 끝나는 문제가 아니다. 검색어 최소 길이를 서비스 정책으로 정하는 등, 애플리케이션 단의 협조가 같이 따라와야 한다.

선택도가 낮으면 풀스캔이 정답이다

마지막 패턴은 앞의 셋과 성격이 다르다. 버그도 실수도 아니라, 플래너가 계산한 결과가 그냥 그렇다는 경우다. 인덱스를 타도 테이블의 20~30%를 읽어야 하는 상황이라면, 무작위 페이지 접근이 섞이는 인덱스 스캔은 순차 스캔보다 비싸다. 플래너는 통계 기반의 비용 추정으로 둘 중 싼 쪽을 고른다. 선택도가 낮은 컬럼에 만들어 둔 인덱스는 그래서 아예 고려 대상에서 빠진다.

예를 들어 상태 컬럼 값이 두 개뿐인데 하나가 행의 90%를 차지한다고 하자. 그 값을 찾는 쿼리는 인덱스가 있어도 풀스캔을 고른다. 이 판단은 대체로 정확하다. 거의 전 테이블을 읽는 것과 비슷한데 굳이 인덱스 페이지를 왕복할 이유가 없다. 문제가 있다면 인덱스를 만들 때가 아니라, 이 컬럼으로 질의를 좁히려는 설계 자체다. 값의 분포가 기울어진 컬럼은 인덱스의 대상이 아니다.

인덱스를 살리고 싶다면 두 가지 길이 있다. 조건에 다른 제약을 덧붙여 선택도를 높이거나, 커버링 인덱스로 index-only scan을 노리는 것이다. 보통 전자가 먼저다. 여러 조건을 AND로 묶으면 플래너가 각 조건의 선택도를 종합해 판단하기 때문에, 단독으로는 버려졌던 인덱스가 전체 계획에서 유용해진다.

통계가 낡아서 저평가되는 경우도 있다. 대량 삭제나 배치 이후 ANALYZE가 안 돌면, 플래너는 한참 전의 분포로 비용을 계산한다. 인덱스를 의심하기 전에 ANALYZE 한 줄부터 돌려 볼 일이다. 분포 자체는 그대로인데 계획이 이상하다면 그때가 통계 설정을 점검할 타이밍이다.

EXPLAIN에서 사인을 읽는 순서

원인을 좁히는 절차는 정해져 있다. 첫 단추는 계획 전체에서 Seq Scan이 붙은 지점을 찾는 것이다. 조인이 걸린 쿼리라면 어느 테이블이 풀스캔하는지부터 짚어야 한다. 여러 테이블 중 하나만 풀스캔하고 나머지는 인덱스를 탄다면, 그 하나에만 문제가 있다고 보면 된다.

그다음 그 테이블의 조건을 읽는다. Filter:에 함수, 캐스팅, 와일드카드가 보이면 거기가 범인이다. Index Cond:가 존재하는데도 풀스캔이면, 조건 문제가 아니라 비용 판단 문제다. 이 경우 선택도 쪽으로 시선을 돌려야 한다. EXPLAIN은 실행 계획만 보여줄 뿐 실제 시간은 안 알려준다. 확인이 필요하면 EXPLAIN (ANALYZE, BUFFERS)로 돌려야 한다. 추정 rows와 actual rows가 크게 어긋나면 인덱스 문제가 아니라 통계 문제다. 추정이 수 배로 빗나간다면 autovacuum 설정을 점검할 순서다.

한 가지 경고를 덧붙이자. 원인 확인을 위해 enable_seqscan = off 같은 플래너 설정을 뒤집어 가며 실험하는 습관은 반쪽이다. 인덱스를 강제로 태워도 결과가 느릴 수 있다. 강제했는데 빨라졌다면, 플래너의 비용 계산이 틀렸다는 뜻이니 random_page_cost나 통계 설정을 봐야 한다. 강제해도 느리다면 처음부터 그 쿼리에 인덱스가 필요 없었던 것이다. 이 구분이 핵심이다. 플래너가 인덱스를 버리는 이유는 대개 인덱스가 느리기 때문이지, 플래너가 멍청하기 때문이 아니다.

댓글

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