[PostgreSQL] pg_trgm 유사도·오타 검색으로 LIKE '%..%'를 인덱스에 태우기

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

부제: 검색창에서 name ILIKE '%mongo%' 같은 양쪽 와일드카드 LIKE를 쓰는데 매번 풀스캔이 걸리거나, 사용자가 "postgre"처럼 오타·부분만 쳐도 근접한 걸 찾아주고 싶을 때

-- CREATE EXTENSION pg_trgm; 이후
-- similarity(): 두 문자열의 트라이그램 유사도(0~1)
SELECT name, similarity(name, 'postgre') AS sim FROM m4_names ORDER BY sim DESC LIMIT 5;
-- % 연산자: 유사도가 임계값(기본 0.3) 이상인가
SELECT name FROM m4_names WHERE name % 'postgres';

     name     |    sim     
--------------+------------
 PostgreSQL   |  0.5833333
 Postgres Pro | 0.53846157
 PostGIS      | 0.45454547
 MySQL        |          0
 MongoDB      |          0

     name     
--------------
 PostgreSQL
 PostGIS
 Postgres Pro

pg_trgm은 문자열을 3글자 조각(트라이그램)으로 쪼개 겹치는 비율로 비슷한 정도를 잰다. 그래서 similarity()로 점수를 매기거나 % 연산자로 "충분히 비슷한 것"만 거른다. 진짜 강점은 인덱스다 — 양쪽 와일드카드 ILIKE '%..%'는 B-tree로는 절대 못 타는데, gin_trgm_ops GIN 인덱스를 걸면 이게 인덱스 검색으로 바뀐다. 함정: 트라이그램이라 2글자 이하 검색어나 매우 짧은 문자열은 정확도가 뚝 떨어진다.

인덱스 효과를 EXPLAIN으로 확인한다. 없을 땐 Seq Scan.

EXPLAIN ANALYZE SELECT count(*) FROM m4_names_big WHERE name ILIKE '%mongo%';

 ->  Seq Scan on m4_names_big  (... actual time=0.213..1.830 ...)
       Filter: (name ~~* '%mongo%'::text)
       Rows Removed by Filter: 4375

CREATE INDEX idx_names_trgm ON m4_names_big USING gin (name gin_trgm_ops);
EXPLAIN ANALYZE SELECT count(*) FROM m4_names_big WHERE name ILIKE '%mongo%';

 ->  Bitmap Heap Scan on m4_names_big  (... actual time=0.054..0.292 ...)
       Recheck Cond: (name ~~* '%mongo%'::text)
       ->  Bitmap Index Scan on idx_names_trgm
             Index Cond: (name ~~* '%mongo%'::text)

이렇게도 쓴다

오타를 거리순으로 정렬 — <->는 (1 - 유사도), 가까울수록 비슷하다.

SELECT name, name <-> 'mysqI' AS dist FROM m4_names ORDER BY dist LIMIT 3;

 

임계값 조정 — 너무 많이/적게 걸리면 세션 단위로 바꾼다.

SET pg_trgm.similarity_threshold = 0.5;   -- % 연산자 기준 상향
SELECT name FROM m4_names WHERE name % 'postgres';

 

정규식·부분일치도 같은 GIN 인덱스로 가속된다 — ~*, ILIKE 모두.

SELECT name FROM m4_names_big WHERE name ~* 'maria' LIMIT 3;

 

"가장 비슷한 것 N개" 자동완성 — 거리순 정렬 + LIMIT. (조합: 근접 정렬)

SELECT name FROM m4_names ORDER BY name <-> 'postgre' LIMIT 3;

 

단어 단위 유사도 word_similarity — 긴 문자열 속 한 단어 매칭에 강하다.

SELECT word_similarity('post', 'Postgres Pro') AS ws;   -- 부분 단어 기준 점수