[PostgreSQL] pg_trgm GIN — `LIKE '%foo%'` 선행 와일드카드를 인덱스로

설치·접속: PostgreSQL 설치와 접속

부제: 로그·문서 테이블에서 LIKE '%검색어%'가 매번 풀스캔으로 느릴 때

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 일반 B-tree는 선행 와일드카드(%foo%)를 못 탄다. trigram GIN은 탄다
CREATE INDEX doc_gix ON trgm USING gin (doc gin_trgm_ops);

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM trgm WHERE doc LIKE '%postgres%';

-- BEFORE (인덱스 없음): 20만 행 전수 스캔
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on trgm (actual rows=400 loops=1)
         Filter: (doc ~~ '%postgres%'::text)
         Rows Removed by Filter: 199600
 Execution Time: 15.521 ms

-- AFTER (gin_trgm_ops): 인덱스로 후보 블록만
 Aggregate (actual rows=1 loops=1)
   ->  Bitmap Heap Scan on trgm (actual rows=400 loops=1)
         Recheck Cond: (doc ~~ '%postgres%'::text)
         ->  Bitmap Index Scan on doc_gix (actual rows=400 loops=1)
               Index Cond: (doc ~~ '%postgres%'::text)
 Execution Time: 0.396 ms

20만 행 테이블에서 LIKE '%postgres%'가 Seq Scan 15.5ms → Bitmap Index Scan 0.4ms로 바뀌었다. 약 39배. 보통 B-tree 인덱스는 LIKE 'foo%'(후행 와일드카드)까지만 탈 수 있고, 앞에 %가 붙으면('%foo%') 시작점을 못 잡아 무조건 풀스캔이다. pg_trgm의 GIN 인덱스(gin_trgm_ops)는 문자열을 연속 3글자(trigram) 조각으로 쪼개 인덱싱하므로, 부분 문자열 LIKE/ILIKE를 "이 trigram들을 포함하는 행"으로 바꿔 인덱스 스캔이 가능해진다.

같은 인덱스가 정규식(~, ~*)까지 가속한다. WHERE doc ~ 'postgres'도 동일하게 Bitmap Index Scan을 타서 0.6ms에 끝났다. 함정은 검색어가 3글자 미만이면 trigram이 안 나와 인덱스가 무력해진다는 것(예: LIKE '%ab%'는 도움이 약하다). 또 GIN 인덱스는 B-tree보다 빌드·쓰기 비용이 크므로, 쓰기가 잦고 부분 검색은 드문 테이블엔 과하다. "부분 문자열/정규식 검색이 자주, 테이블은 읽기 위주"일 때 값을 한다.

이렇게도 쓴다

정규식 검색도 같은 GIN 인덱스로 가속된다. 별도 인덱스 필요 없음.

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT count(*) FROM trgm WHERE doc ~ 'postgres';
-- → Bitmap Index Scan on doc_gix, Execution Time: 0.590 ms

 

대소문자 무시 부분 검색(ILIKE)도 gin_trgm_ops가 그대로 받는다.

SELECT * FROM trgm WHERE doc ILIKE '%PostgreSQL%';

 

유사도 검색(% 연산자)과 인덱스를 함께 쓴다. 오타까지 흡수. (조합: similarity)

SET pg_trgm.similarity_threshold = 0.3;
SELECT doc, similarity(doc, 'postgres') AS sml
FROM trgm WHERE doc % 'postgres' ORDER BY sml DESC LIMIT 10;

 

쓰기가 많고 정확도보다 인덱스 크기·빌드 속도가 중요하면 GiST를 쓴다. GIN은 검색이 빠르고 GiST는 갱신이 가볍다. (조합: GiST)

CREATE INDEX doc_gist ON trgm USING gist (doc gist_trgm_ops);

 

인덱스가 실제로 쓰이는지 못 미더우면 강제로 seq scan을 꺼서 대조한다.

SET enable_seqscan = off;   -- 세션 한정, 플랜 확인용
EXPLAIN SELECT * FROM trgm WHERE doc LIKE '%postgres%';