pg_trgm
PostgreSQL 추가 모듈인 pg_trgm 을 쓰면 %문자열% 처럼 앞뒤에 와일드카드가 붙은 LIKE 검색에도 인덱스를 태울 수 있습니다. pg_trgm 은 유사한 문자열을 빠르게 찾는 인덱스 연산자 클래스와, 트라이그램 일치를 기준으로 영숫자 텍스트의 유사도를 판정하는 함수·연산자를 제공합니다.
트라이그램(trigram)은 문자열에서 뽑아낸 연속된 세 글자입니다. 두 문자열이 공유하는 트라이그램 개수를 세면 유사도를 측정할 수 있습니다. 이 단순한 아이디어는 여러 자연 언어에서 단어 유사도를 재는 데 효과적으로 동작합니다. 아래 함수·연산자·기본값은 PostgreSQL 18 문서를 기준으로 합니다.
NOTE — pg_trgm 은 트라이그램을 뽑을 때 영숫자가 아닌 문자를 무시합니다. 그리고 각 단어 앞에 공백 두 개, 뒤에 공백 한 개가 붙은 것으로 간주합니다. 그래서
cat의 트라이그램 집합은{" c"," ca","cat","at "}이고,foo|bar의 트라이그램 집합은{" f"," fo","foo","oo "," b"," ba","bar","ar "}입니다.
pg_trgm 함수
| 함수 | 반환형 | 설명 |
|---|---|---|
similarity(text, text) |
real | 두 인자가 얼마나 비슷한지 나타내는 값을 반환합니다. 0(완전히 다름)에서 1(완전히 같음) 사이입니다. |
show_trgm(text) |
text[] | 주어진 문자열의 모든 트라이그램을 배열로 반환합니다. 실무에서는 디버깅 말고 쓸 일이 드뭅니다. |
word_similarity(text, text) |
real | 첫 인자의 트라이그램 집합과, 둘째 인자의 순서 있는 트라이그램 집합에서 잘라낸 연속 구간 사이의 최대 유사도를 반환합니다. |
strict_word_similarity(text, text) |
real | word_similarity() 와 같지만 구간 경계를 단어 경계에 맞춥니다. 첫 문자열과, 둘째 문자열의 연속된 단어들 사이의 최대 유사도를 반환합니다. |
show_limit() |
real | % 연산자가 쓰는 현재 유사도 임계값을 반환합니다. deprecated 이며 SHOW pg_trgm.similarity_threshold 를 쓰라고 안내합니다. |
set_limit(real) |
real | % 연산자가 쓰는 유사도 임계값을 설정하고 같은 값을 반환합니다. deprecated 이며 SET pg_trgm.similarity_threshold 를 쓰라고 안내합니다. |
word_similarity('word', 'two words') 는 0.8 을 반환합니다. 첫 문자열의 트라이그램 집합은 {" w"," wo","wor","ord","rd "} 이고, 둘째 문자열의 순서 있는 트라이그램 집합은 {" t"," tw","two","wo "," w"," wo","wor","ord","rds","ds "} 입니다. 둘째 문자열에서 가장 비슷한 연속 구간이 {" w"," wo","wor","ord"} 이므로 유사도는 0.8 입니다. 이 값은 첫 문자열과 둘째 문자열의 부분 문자열 사이 최대 유사도로 이해하면 대체로 맞습니다. 단 이 함수는 구간 경계에 패딩을 붙이지 않습니다. 그래서 어긋난 단어 경계를 빼면, 둘째 문자열에 더 들어 있는 글자 수는 계산에 넣지 않습니다.
strict_word_similarity('word', 'two words') 는 0.571429 를 반환합니다. 구간을 단어 경계에 맞추므로 words 한 단어가 선택되고, 그 트라이그램 집합은 {" w"," wo","wor","ord","rds","ds "} 입니다. 단어 전체와의 유사도를 찾을 때는 strict_word_similarity(), 단어의 일부와의 유사도를 찾을 때는 word_similarity() 가 맞습니다.
pg_trgm 연산자
| 연산자 | 반환형 | 설명 |
|---|---|---|
text % text |
boolean | 두 인자의 유사도가 pg_trgm.similarity_threshold 보다 크면 true 입니다. |
text <% text |
boolean | 첫 인자의 트라이그램 집합과, 둘째 인자의 순서 있는 트라이그램 집합에서 잘라낸 연속 구간의 유사도가 pg_trgm.word_similarity_threshold 보다 크면 true 입니다. |
text %> text |
boolean | <% 의 교환 연산자입니다. |
text <<% text |
boolean | 둘째 인자에 단어 경계에 맞는 연속 구간이 있고, 그 구간과 첫 인자 트라이그램 집합의 유사도가 pg_trgm.strict_word_similarity_threshold 보다 크면 true 입니다. |
text %>> text |
boolean | <<% 의 교환 연산자입니다. |
text <-> text |
real | 두 인자 사이의 거리, 곧 1 - similarity() 입니다. |
text <<-> text |
real | 1 - word_similarity() 입니다. |
text <->> text |
real | <<-> 의 교환 연산자입니다. |
text <<<-> text |
real | 1 - strict_word_similarity() 입니다. |
text <->>> text |
real | <<<-> 의 교환 연산자입니다. |
GUC 파라미터
- pg_trgm.similarity_threshold (real)
%연산자가 쓰는 유사도 임계값입니다. 0과 1 사이여야 하고 기본값은 0.3 입니다. - pg_trgm.word_similarity_threshold (real)
<%·%>연산자가 쓰는 단어 유사도 임계값입니다. 0과 1 사이여야 하고 기본값은 0.6 입니다. - pg_trgm.strict_word_similarity_threshold (real)
<<%·%>>연산자가 쓰는 엄격 단어 유사도 임계값입니다. 0과 1 사이여야 하고 기본값은 0.5 입니다.
임계값은 GUC 로 직접 조정합니다. set_limit()·show_limit() 은 deprecated 이므로 아래 형태를 씁니다.
SET pg_trgm.similarity_threshold = 0.5;
SHOW pg_trgm.similarity_threshold;
인덱스 지원
pg_trgm 은 GiST·GIN 인덱스 연산자 클래스를 제공합니다. 텍스트 칼럼에 인덱스를 만들면 위의 유사도 연산자뿐 아니라 LIKE·ILIKE·~·~*·= 질의도 트라이그램 기반 인덱스 검색으로 처리할 수 있습니다. 기본 빌드에서 유사도 비교는 대소문자를 구분하지 않습니다. 부등호 비교 연산자는 지원하지 않고, 등호 비교에서는 일반 B-tree 인덱스만큼 효율적이지 않을 수 있습니다.
예제:
CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);
-- 또는
CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);
gist_trgm_ops 는 트라이그램 집합을 비트맵 시그니처로 근사합니다. 시그니처 길이는 siglen 옵션으로 정하며 기본값은 12바이트, 유효 범위는 1~2024바이트입니다. 시그니처가 길수록 인덱스에서 훑는 비율과 힙 페이지 접근이 줄어 검색이 정확해지지만 인덱스가 커집니다.
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops(siglen=32));
이 시점에서 t 칼럼에 유사도 검색용 인덱스가 생깁니다. 전형적인 검색은 다음과 같습니다.
SELECT t, similarity(t, 'word') AS sml
FROM test_trgm
WHERE t % 'word'
ORDER BY sml DESC, t;
이 쿼리는 word 와 임계값 이상으로 비슷한 값을 유사도가 높은 순으로 반환합니다. 단어 단위로 찾고 싶으면 <% 나 <<% 를 씁니다.
SELECT t, strict_word_similarity('word', t) AS sml
FROM test_trgm
WHERE 'word' <<% t
ORDER BY sml DESC, t;
가까운 몇 건만 필요하면 거리 연산자로 정렬하는 형태가 유리합니다.
SELECT t, t <-> 'word' AS dist
FROM test_trgm
ORDER BY dist LIMIT 10;
이 거리 순 정렬은 GiST 인덱스로는 효율적으로 처리되지만 GIN 인덱스로는 처리되지 않습니다. GiST 와 GIN 중 무엇을 고를지는 두 인덱스 방식의 일반적인 성능 특성에 달려 있습니다.
LIKE·정규식 검색에서는 검색 문자열이 왼쪽에 고정되어 있지 않아도 됩니다.
SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';
SELECT * FROM test_trgm WHERE t ~ '(foo|bar)';
WARNING — LIKE 든 정규식이든, 패턴에서 뽑아낼 트라이그램이 없으면 인덱스 전체를 훑는 스캔으로 떨어집니다. 트라이그램은 연속된 세 글자 단위이고 영숫자가 아닌 문자는 무시되므로, 너무 짧은 패턴은 인덱스의 이점을 얻지 못합니다.
설치
- 쓰는 메이저 버전의
contrib패키지를 설치합니다. PGDG 저장소의 패키지 이름에는 메이저 버전 번호가 붙습니다. 현재 지원되는 메이저 버전은 14부터 18까지입니다.
$ dnf install postgresql18-contrib
- pg_trgm 을 쓸 데이터베이스에 확장을 설치합니다.
CREATE EXTENSION pg_trgm;
pg_trgm 은 shared_preload_libraries 에 등록할 필요가 없습니다. 또 trusted 확장이라 슈퍼유저가 아니어도 해당 데이터베이스에 CREATE 권한이 있으면 설치할 수 있습니다.