[PostgreSQL] tsvector / tsquery 전문검색으로 문서 본문을 키워드로 뒤지기

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

부제: 문서 본문에서 "index와 scan이 둘 다 들어간 글"을 찾는데, ILIKE '%index%' AND ILIKE '%scan%'은 어형 변화(indexing/indexed)를 못 잡고 느릴 때

-- to_tsvector: 본문을 어간 추출된 검색용 벡터로. @@ 로 tsquery 와 매칭
SELECT id, title FROM m4_docs
WHERE to_tsvector('english', body) @@ to_tsquery('english','index & scan');
-- tsvector 는 어형을 어간으로 정규화 (indexing/indexed -> index)
SELECT to_tsvector('english','indexes indexing indexed scans');

 id |             title             
----+-------------------------------
  1 | PostgreSQL performance tuning
  4 | Query optimization basics

      to_tsvector       
------------------------
 'index':1,2,3 'scan':4

전문검색은 두 타입으로 돌아간다. tsvector는 본문을 어간으로 정규화한 단어 목록(indexing·indexed가 모두 index로), tsquery는 &(AND)·|(OR)·!(NOT)로 짠 검색식이다. @@로 둘을 매칭하면 어형 변화와 불용어(the·how 등)를 알아서 처리한다. LIKE와 결정적으로 다른 점 두 가지 — 어간 정규화로 검색 품질이 좋고, GIN 인덱스가 제대로 붙는다. 함정: 사전('english')을 벡터와 쿼리 양쪽에 똑같이 맞춰야 한다. 한글은 별도 사전 설정이 필요하니 'simple'부터 시작한다.

이렇게도 쓴다

OR/NOT 조합 검색 — replication 또는 vacuum이 나오는 글.

SELECT id, title FROM m4_docs
WHERE to_tsvector('english',body) @@ to_tsquery('english','replication | vacuum');

 

관련도 순 정렬 — ts_rank로 점수 매겨 정렬. (조합: 랭킹)

SELECT title, ts_rank(to_tsvector('english',body), to_tsquery('english','index')) AS rank
FROM m4_docs ORDER BY rank DESC LIMIT 3;

 

검색어를 본문에서 하이라이트 — ts_headline이 <b>로 감싸준다.

SELECT ts_headline('english', body, to_tsquery('english','index & scan'))
FROM m4_docs WHERE id=1;

 

구글식 따옴표·연산자 문법 그대로 받기 — websearch_to_tsquery.

SELECT id, title FROM m4_docs
WHERE to_tsvector('english',body) @@ websearch_to_tsquery('english','"index scan"');

 

생성 컬럼 + GIN 인덱스로 대량 문서 검색 가속. (조합: generated column + GIN)

ALTER TABLE m4_docs_big ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;
CREATE INDEX idx_docs_tsv ON m4_docs_big USING gin (tsv);
EXPLAIN ANALYZE SELECT count(*) FROM m4_docs_big WHERE tsv @@ to_tsquery('english','replication');

 ->  Bitmap Heap Scan on m4_docs_big  (... rows=1000 ...)
       ->  Bitmap Index Scan on idx_docs_tsv
             Index Cond: (tsv @@ '''replic'''::tsquery)