[PostgreSQL] PostgreSQL Full Text Search

tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text

PostgreSQL 공식 문서를 정리한 포스팅입니다.

https://www.postgresql.org/docs/current/textsearch-intro.html

12.1. Introduction

 

Full Text Search란?

Full text search를 사용하면 쿼리의 조건을 만족하는 자연어 문서를 식별하고 연관성이 높은 문서를 찾을 수 있습니다.

 

일반 텍스트 검색의 LIKE와 같은 연산자를 사용해서 검색을 구현할 할 수 있지만 부족한 점이 있습니다.

 

1. 파생어를 쉽게 찾을 수 없습니다. (satisfy를 검색했더니 satisfies를 찾지 못 함)
2. 연관성에 따라 정렬할 수 없습니다. 일치하는 문서가 굉장히 많이 나올 수 있습니다.
3. 인덱스를 지원하지 않아 Full Scan을 해야 합니다.

Full text search는 문서를 미리 가공하여 인덱스를 저장하기 때문에 이러한 문제를 해결할 수 있습니다.

 

다음 과정을 거쳐 문서를 가공할 수 있습니다.

 

1. 파서를 통해 문서의 토큰화를 진행합니다. 커스텀 파서를 사용할 수 있습니다.
2. 토큰을 어휘소로 변환합니다.
3. 정규화를 통해 파생어를 쉽게 찾을 수 있습니다. 검색에 필요 없는 단어를 제거합니다.

Document란?

document는 Full text search에서 검색을 하는 단위입니다.

 

PostgresSQL에서 문서는 테이블 행 내의 텍스트 필드일 수도 있고 여러 개의 필드를 조합하여 만들 수도 있습니다.

 

즉, 문서는 어딘가에 통째로 저장된 형태가 아닐 수 있습니다.

 

문서 조합 예시

SELECT title || ' ' || author || ' ' || abstract || ' ' || body AS document 
FROM messages 
WHERE mid = 12; 

SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document 
FROM messages m, docs d 
WHERE m.mid = d.did AND m.mid = 12;

 

참고로 위 예시에서 하나의 field가 NULL이 되면 문서 자체가 NULL이 될 수  있기 때문에 coalesce를 사용해야 합니다.

 

Full text search를 하기 위해 각 문서는 미리 tsvector 형태로 가공하여 축소해야 합니다.

 

검색과 순위는 문서의 tsvector 형태에서 수행됩니다.

 

우리는 종종 tsvector를 문서라고 표현하기도 하지만 tsvector는 전체 문서의 압축된 형태라는 것을 기억해야 합니다.

 

Basic Text Matching

PostgresSQL에서 Full texet search는 @@ 연산자를 사용한 tsquery를 사용합니다.

 

만약 tsvector가 tsquery와 일치한다면 true를 반환하고 그렇지 않다면 false를 반환합니다.

 

다음과 같이 tsvector와 tsquery가 정해져있지 않고 어떠한 데이터 타입이 먼저 와도 상관없습니다.

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
 ?column?
----------
 t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
 ?column?
----------
 f

 

tsvector와 마찬가지로 tsquery도 단순 텍스트가 아니라 정해진 문법이 있습니다.

 

문법 참조

https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSQUERY

8.11. Text Search Types

 

to_tsquery, plainto_tsquery, phraseto_tsquery와 같은 함수를 통해 텍스트를 적절한 tsquery로 만들 수 있습ㄴ디ㅏ.

 

비슷하게 to_tsvector를 통해 문서를 파싱하고 정규화를 진행하여 문서를 tsvector로 만들 수 있습니다.

 

다음 쿼리는 문서를 tsvector로 만들고 tsquery를 통해 검색을 하는 쿼리입니다.

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
 ?column?
----------
 t

 

만약 문서를 tsvector로 만들지 않고 tsquery로 검색하면 어떻게 될까요?

 

다음은 일반 문서에 tsquery를 날리는 쿼리입니다.

 

SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat');
 ?column?
----------
 f

 

앞서 문서를 tsvector로 만드는 과정은 정규화를 포함한다고 했습니다.

 

정규화를 진행하는 과정에서 rats와 같은 단어는 rat으로 바뀌게 되는데 tsvector로 변환하지 않았기 때문에 rats라는 단어를 찾을 수 없게 된 것입니다.

 

full text search는 무조건 tsvector와 tsquery를 사용해야 하는 것은 아닙니다.

 

tsvector @@ tsquery
tsquery  @@ tsvector
text @@ tsquery
text @@ text

 

text @@ tsqery는 to_tsvector(x) @@ y와 동일하고 text @@ text는 to_tsvector(x) @@ plainto_tsquery(y)와 동일합니다.

 

tsquery 기초 문법

 

 tsquery의 기초 문법은 다음과 같습니다.

 

1. & (AND) : 모두 만족
2. |  (OR) : 적어도 하나 만족
3. !  (NOT) : 없어야 함
4. <-> (FOLLOWED BY) : 순서에 맞게 등장

FOLLOWED BY 문법에 대해 알아봅시다.

 

SELECT to_tsvector('fatal error') @@ to_tsquery('fatal <-> error');
 ?column?
----------
 t

SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal <-> error');
 ?column?
----------
 f

 

첫 번째는 fatal 다음에 error가 등장했기 때문에 true가 반환됩니다.

 

두 번째는 error 다음에 fatal이 등장했기 때문에 false가 반환됩니다.

 

<-> 연산자를 여러 번 사용할 수도 있습니다.

 

 'cat' <-> 'ate' <-> 'rat'

 

위 쿼리는 cat 다음에 ate 다음에 rat이 등장해야 합니다.

 

<2>는 중간에 정확히 하나의 다른 어휘가 존재해야 합니다.

 

 'cat' <-> 'ate' <2> 'rat'

 

ate와 rat 사이 하나의 다른 어휘가 존재해야 합니다.

 

phraseto_tsquery 함수를 사용하면 여러 어휘가 등장할 때 쉽게 쿼리를 만들 수 있습니다.

 

SELECT phraseto_tsquery('cats ate rats');
       phraseto_tsquery
-------------------------------
 'cat' <-> 'ate' <-> 'rat'

SELECT phraseto_tsquery('the cats ate the rats');
       phraseto_tsquery
-------------------------------
 'cat' <-> 'ate' <2> 'rat'

 

참고로 AND, OR, NOT 연산은 FOLLOWED BY와 함께 사용할 때와 그렇지 않을 때 약간 차이가 있다는 것을 이해해야 합니다.

 

!x는 일반적으로 x가 문서에 없어야 한다는 것을 의미하지만 !x <-> y는 x 바로 뒤에 y가 나오면 안된다는 것을 의미합니다.

 

텍스트 검색 설정

위 내용들은 간단한 텍스트 검색 예제입니다.

 

PostgreSQL에서는 Full text search에 더 다양한 기능을 제공하고 있ㄸ습니다.

 

특정 단어 인덱싱 건너 뛰기, 동의어 처리, 정교한 구문 분석과 같은 다양한 기능을 제공하고 있고 default_text_search_config 설정을 통해 자신의 고유한 설정을 만들 수도 있습니다.

 

자신만의 간단한 텍스트 검색 설정을 위해 데이터베이스 객체를 사용할 수 있습니다.

 

PostgreSQL에서는 네 가지 유형의 설정 관련 데이터베이스 객체를 제공합니다.

 

1. Text search parser : 문서의 토큰화를 진행하고 분류합니다.
2. Text search dictionary : 토큰을 정규화하고 불용어를 거부합니다.
3. Text search template : dictionary의 기본 기능을 제공합니다. (dictionary는 단순히 템플릿과 템플릿 파라미터를 정의합니다.)
4. Text search configuration : 파서와 파서가 생성한 토큰을 정규화하는데 사용할 dictionary 집합을 선택합니다.

parser와 template은 low level C 함수에서 빌드되기 때문에 데이터베이스에 설치할 때 슈퍼 유저 권한이 필요합니다.

 

dictionary와 configuration은 parser와 template을 매개변수화 하고 연결하기 때문에 특별한 권한이 필요하지 않습니다.

 

테이블과 인덱스

지금까지 간단한 문자열을 통해 Full text search를 하는 방법을 알아보았습니다.

 

이제 인덱스를 사용하여 데이터를 검색하는 방법을 알아 볼 차례입니다.

 

인덱스를 사용하지 않아도 full text search를 할 수 있습니다.

 

다음은 body 필드에서 friend를 포함하는 행을 검색하는 쿼리입니다.

SELECT title
FROM pgweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

 

인덱스가 없어도 잘 동작하지만 대부분의 앱에서 ad hoc search를 하는 경우를 제외하고 이러한 접근은 굉장히 느리다고 느낄 것입니다.

 

속도를 높이기 위해 일반적으로 인덱스를 사용하는 것이 좋습니다.

 

우리는 검색 성능을 높이기 위해 GIN 인덱스를 생성할 수 있습니다.

 

CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', body));

 

여기서 to_tsvector 함수에서 2가지 인자를 사용한 것을 확인할 수 있습니다.

 

text search 인덱스를 생성할 때 중요한 점은 반드시 첫 번째 인자에 해당하는 검색 구성을 명시해야 합니다.

 

만약 생략하게 될 경우 default_text_search_config를 따르는데 기본 설정이 바뀌었을 때 인덱스의 결과가 달라질 수 있습니다.

 

즉, 검색 일관성을 유지하기 위해 인덱스를 생성할 때 반드시 검색 구성을 명시해야 합니다.

 

검색을 할 때도 to_tsvector에서 검색 구성을 명시해야 해당 구성에 맞는 인덱스를 사용하게 됩니다.

 

인덱스를 생성할 때 문서를 tsvector로 만들어야 하는데 테이블의 여러 필드를 조합해서 만들어야 할 때도 있습니다.

 

이 때, 두 가지 방법이 존재합니다.

 

첫 번째, expression index 방식입니다.

CREATE INDEX pgweb_idx ON pgweb USING GIN (to_tsvector('english', title || ' ' || body));

 

그냥 직접 조합해서 인덱스를 생성하는 방법입니다.

 

위에서 소개한 방법과 동일합니다.

 

두 번째, separate column approach 방식입니다.

 

tsvector 필드를 하나 만들어서 인덱스를 생성하는 방법입니다.

 

stored generated column을 사용하면 여러 필드를 조합해서 만든 문서의 tsvector column을 생성할 수 있습니다.

 

원본 문서의 변경 사항이 발생했을 때 stored generated colum은 자동으로 동기화됩니다.

 

먼저 stored generated column으로 문서의 tsvector column을 생성합니다.

ALTER TABLE pgweb
    ADD COLUMN textsearchable_index_col tsvector
               GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

 

그리고 이 column을 기반으로 GIN 인덱스를 생성합니다.

CREATE INDEX textsearch_idx ON pgweb USING GIN (textsearchable_index_col);

 

이렇게 stored generated column을 사용하면 쉽게 full text search를 할 수 있습니다.

 

SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

 

이제 expression index 방식과 separate column 방식의 장단점을 알아봅시다.

 

구분	separate column	expression index
속도	미리 tsvector로 만든 컬럼을 쓰기 때문에 빠름	매번 tsvector로 만들어야 함
언어 설정 명시	O (default_text_search_config 사용)	X
디스크 사용	추가 공간 필요 (컬럼이 하나 더 추가되기 때문에)	공간 절약