idx_scan=0 은 인덱스가 필요없다는 뜻이 아니다 — OR 한쪽이 인덱스를 잠근다

상황

전문검색 API 가 수초씩 걸린다는 제보를 받고 인덱스 사용 통계부터 봤습니다.

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
  FROM pg_stat_user_indexes WHERE relname = 'documents' ORDER BY idx_scan DESC;

 indexrelname            | idx_scan | size
-------------------------+----------+--------
 documents_pkey          | 50313530 | 3816 kB
 idx_documents_body_trgm |        0 | 278 MB   ← ?

본문 substring 검색용으로 만든 278MB 짜리 GIN 트라이그램 인덱스가 단 한 번도 스캔되지 않았습니다. 첫 반응은 "안 쓰는 인덱스니 지우자"였습니다. 그게 정확히 틀렸습니다.

문제 — OR 한쪽이 인덱스를 잠근다

문제의 쿼리는 이랬습니다.

WHERE (title ILIKE '%term%' OR body ILIKE '%term%')

body 에는 트라이그램 GIN 이 있고, title 에는 없었습니다. PostgreSQL 이 OR 로 묶인 조건을 인덱스로 처리하려면 모든 브랜치가 각각 인덱스로 커버돼야 BitmapOr 노드를 세울 수 있습니다. 한쪽이라도 인덱스가 없으면 그 브랜치를 위해 어차피 전체 힙을 훑어야 하고, 그러면 나머지 브랜치의 인덱스를 쓸 이유가 사라집니다. 플래너는 통째로 Seq Scan 을 고릅니다.

즉 idx_scan = 0 은 "이 인덱스가 불필요하다"가 아니라 "OR 때문에 잠겨 있다"는 지문이었습니다. 지웠다면 진짜 원인은 그대로 둔 채 되돌리기만 어려워졌을 것입니다.

EXPLAIN 으로 조건을 분리해서 재보면 구조가 드러납니다.

title ILIKE '%term%'            단독  → Seq Scan            137 ms
body  ILIKE '%term%'            단독  → Bitmap Index Scan  2274 ms
(title OR body) ILIKE '%term%'        → Seq Scan           3866 ms   ← 현행

여기서 두 번째 발견이 나왔습니다. 없는 인덱스(title)를 만들어도 최선이 2.3초입니다. 진짜 비용은 인덱스 부재가 아니라 body 의 TOAST 디토스트였습니다. 큰 텍스트 컬럼은 별도 TOAST 테이블에 압축 저장되는데, GIN 트라이그램은 lossy 라 후보 행을 전부 다시 꺼내 recheck 합니다. title 단독 검색이 인덱스 없이도 137ms 인 이유가 이것입니다 — 힙만 훑고 TOAST 를 건드리지 않습니다.

해결 — 인덱스를 더하는 대신 OR 를 쪼갠다

없는 인덱스는 만들되, 그것을 해결책이라고 부르지 않았습니다. 실제로 한 일은 OR 를 두 개의 독립 쿼리로 분리한 것입니다.

-- leg 1: 제목 — 인덱스 없이도 즉시 응답
SELECT id FROM documents WHERE title ILIKE $1 AND ... LIMIT $n;
 
-- leg 2: 본문 — 느리지만 격리됨
SELECT id FROM documents WHERE body ILIKE $1 AND ... LIMIT $n;

둘을 병렬로 실행하고 결과를 합집합으로 병합합니다. 제목 검색이 본문 검색을 기다리지 않게 되면서 체감 응답이 3,866ms → 137ms 가 됐습니다.

사용자가 실제로 겪던 문제는 "본문 검색이 느리다"가 아니라 "제목만 찾는데도 본문 때문에 4초를 기다린다" 였습니다. 같은 SQL 을 두 번 나눠 쓰는 게 중복처럼 보이지만, 느린 조건과 빠른 조건이 하나의 OR 로 묶여 있으면 항상 느린 쪽 속도로 수렴합니다.

덤으로, 분리하고 나면 각 leg 에 서로 다른 정책을 걸 수 있습니다. 본문 leg 에만 최소 검색어 길이 가드를 두는 식입니다.

함정 1 — 트라이그램은 3문자다

pg_trgm 은 이름 그대로 3문자 단위입니다. %term% 처럼 양끝이 와일드카드면 패턴에서 패딩을 쓸 수 없어, 리터럴 구간이 3자 미만이면 추출되는 트라이그램이 0개입니다. 인덱스가 있어도 못 탑니다.

'%보고서%' (3자) → Bitmap Index Scan
'%회의%'   (2자) → Seq Scan

한국어에서 이건 사소한 예외가 아닙니다. 자주 검색되는 명사 상당수가 2음절 한자어입니다 — 회의, 계약, 정산, 예산, 견적. 영문도 hr, qa 같은 2자 질의가 들어옵니다. 인덱스를 넣고 "해결됐다"고 종결하면, 실사용 질의의 상당 부분은 여전히 Seq Scan 을 탑니다.

CJK 2-gram 이 필요하면 pg_bigm 이 설계상 맞고, 그게 어려우면 검색엔진의 n-gram 색인으로 넘기는 편이 낫습니다.

함정 2 — 플래너는 디토스트 비용을 모른다

이 케이스에서 cost 추정치는 Seq Scan 11,133 / BitmapOr 7,828 이었습니다. 실제 소요는 3,866ms 대 2,274ms 였습니다. PostgreSQL 의 cost model 은 TOAST 접근 비용을 계산에 넣지 않기 때문에, 큰 텍스트 컬럼이 걸린 플랜에서는 추정과 실측이 크게 어긋납니다. 격차가 작으면 검색어 선택도에 따라 다시 Seq Scan 으로 되돌아갑니다. 인덱스를 넣어 얻는 개선이 확정적이지 않고 확률적이라는 뜻입니다.

큰 텍스트 컬럼을 다루는 쿼리는 cost 를 믿지 말고 EXPLAIN (ANALYZE, BUFFERS) 로 실측해야 합니다. Buffers 의 toast read 항목이 진짜 이야기를 해줍니다.

정리

• idx_scan = 0 을 보면 지우기 전에 그 컬럼이 OR 에 묶여 있는지 먼저 봅니다. 잠긴 인덱스일 수 있습니다.
• OR 브랜치는 전부 인덱스로 커버돼야 의미가 있습니다. 하나만 빠져도 전부 무효입니다.
• 느린 조건과 빠른 조건을 하나의 OR 로 묶지 않습니다. 분리하면 빠른 쪽이 먼저 응답합니다.
• 트라이그램은 3문자 — 2자 질의가 흔한 언어라면 인덱스만으로 끝나지 않습니다.
• 큰 텍스트 컬럼이 관련되면 cost 추정을 믿지 말고 BUFFERS 를 봅니다.

참고

• pg_trgm — PostgreSQL Documentation
• Combining Multiple Indexes (Bitmap scans)
• TOAST — PostgreSQL Documentation
• pg_bigm