pg_trgm

pg_trgm

postgresql 추가모듈 중 pg_trgm을 이용하여 ‘%문자열%’ like 검색시 인덱스 스캔을 실행할 수 있습니다. (3글자 미만에 대한 패턴검색의 경우 인덱스 탐색비용이 급격히 증가, table full scan의 비용을 초과함.) pg_trgm 모듈은 유사한 문자열을 빠르게 검색 할 수있는 색인 연산자 클래스뿐만 아니라 trigram 일치를 기반으로 영숫자 텍스트의 유사성을 결정하는 함수와 연산자를 제공합니다.

트라이 그램은 문자열에서 가져온 세 개의 연속 문자 그룹입니다. 우리는 그들이 공유하는 트라이 그램의 수를 세어 두 문자열의 유사성을 측정 할 수 있습니다. 이 간단한 아이디어는 많은 자연 언어에서 단어의 유사성을 측정하는 데 매우 효과적이라는 것을 알 수 있습니다.

참고 : pg_trgm은 문자열에서 트라이 그램을 추출 할 때 단어가 아닌 문자 (영숫자가 아닌 문자)를 무시합니다. 각 단어는 문자열에 포함 된 트라이 그램 세트를 결정할 때 접두사 두 개와 접미사 한 개가있는 것으로 간주됩니다. 예를 들어, 문자열 “cat”의 트라이 그램 세트는 “c”, “ca”, “cat”및 “at”입니다. 문자열 “foo | bar”의 트라이 그램 세트는 “f”, “fo”, “foo”, “oo”, “b”, “ba”, “bar”및 “ar”입니다.

pg_trgm function

첫 번째 문자열에서 트라이 그램 세트는 { “w”, “wo”, “ord”, “wor”, “rd”}입니다. 두 번째 문자열에서 순서가 지정된 trigram은 { “t”, “tw”, “two”, “wo”, “w”, “wo”, “wor”, “ord”, “rds”, “ds입니다. “}. 두 번째 문자열에서 정렬 된 trigram 집합의 가장 비슷한 범위는 { “w”, “wo”, “wor”, “ord”}이며 유사도는 0.8입니다. 이 함수는 첫 번째 문자열과 두 번째 문자열의 하위 문자열 간의 유사성이 가장 큰 것으로 이해 될 수있는 값을 반환합니다. 그러나이 함수는 범위의 경계에 패딩을 추가하지 않습니다. 따라서 일치하지 않는 단어 경계를 제외하고 두 번째 문자열에 존재하는 추가 문자 수는 고려되지 않습니다.

pg_trgm operator

GUC Parameters

• 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).

Index Support

pg_trgm 모듈은 GiST 및 GIN 인덱스 연산자 클래스를 제공하여 매우 빠른 유사성 검색을 위해 텍스트 열에 인덱스를 생성 할 수 있습니다. 이러한 인덱스 유형은 위에 설명 된 유사성 연산자를 지원하며 LIKE, ILIKE, ~ 및 ~ * 쿼리에 대한 trigram 기반 인덱스 검색을 추가적으로 지원합니다. (이 인덱스는 동등성이나 간단한 비교 연산자를 지원하지 않으므로 일반 B-tree 인덱스도 필요할 수 있습니다.)

ex>

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

or

CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);

이 시점에서 t 열에 유사성 검색에 사용할 수있는 색인이 생깁니다. 일반적인 검색어는

SELECT t, similarity(t, 'word') AS sml
  FROM test_trgm
  WHERE t % 'word'
  ORDER BY sml DESC, t;

가장 좋음에서 최악으로 정렬 된 단어와 충분히 유사한 텍스트 열의 모든 값을 반환합니다. 인덱스는 매우 큰 데이터 세트에서도 빠른 작업을 수행하는 데 사용됩니다.

위 쿼리의 변형은

SELECT t, t <-> 'word' AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

이것은 GIN 인덱스가 아닌 GiST 인덱스에 의해 매우 효율적으로 구현 될 수 있습니다.

설치

$ yum install postgresql10-contrib

버전에 맞는 contrib를 yum을 이용해 설치합니다.

postgresql.conf 파일을 열어 수정합니다.

shared_preload_libraries = 'pg_trgm'

pg_trgm을 사용할 DB에 extension을 설치 합니다.

create extension pg_trgm;