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

Function Returns Description
similarity(text, text) real Returns a number that indicates how similar the two arguments are. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the two strings are identical).
show_trgm(text) text[] Returns an array of all the trigrams in the given string. (In practice this is seldom useful except for debugging.)
word_similarity(text, text) real Returns a number that indicates the greatest similarity between the set of trigrams in the first string and any continuous extent of an ordered set of trigrams in the second string. For details, see the explanation below.
show_limit() real Returns the current similarity threshold used by the % operator. This sets the minimum similarity between two words for them to be considered similar enough to be misspellings of each other, for example (deprecated).
set_limit(real) real Sets the current similarity threshold that is used by the % operator. The threshold must be between 0 and 1 (default is 0.3). Returns the same value passed in (deprecated).

첫 번째 문자열에서 트라이 그램 세트는 { “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

Operator Returns Description
text % text boolean Returns true if its arguments have a similarity that is greater than the current similarity threshold set by pg_trgm.similarity_threshold.
text <% text boolean Returns true if the similarity between the trigram set in the first argument and a continuous extent of an ordered trigram set in the second argument is greater than the current word similarity threshold set by pg_trgm.word_similarity_threshold parameter.
text %> text boolean Commutator of the <% operator.
text <-> text real Returns the “distance” between the arguments, that is one minus the similarity() value.
text <<-> text real Returns the “distance” between the arguments, that is one minus the word_similarity() value.
text <->> text real Commutator of the <<-> 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;

 



You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *