Full Text Search

• 일반적인 단순한 문자열 검색과 구분되는 전문적인 텍스트 검색 • 다음과 같은 기능이 추가적으로 필요: • 토큰화, 정규화, 불용어 처리 등의 전처리 과정 • 역인덱스 등 정보 검색을 위한 자료 구조 • 검색 랭킹의 계산 등

검색어가 중간에 있는 경우

• SQL에서 title LIKE '%대한민국%'는 텍스트 어디든 대한민국이 들어가는 경우를 검색 • 인덱스는 ABC순, 가나다순으로 정렬되어 있어 특정한 표현으로 시작하는 경우는 빨리 찾을 수 있음 • 특정 표현이 중간에 들어가는 경우는 인덱스를 활용하지 못함 → 여전히 느림 • 인덱스가 있는 경우:

%%sql
EXPLAIN ANALYZE SELECT * FROM wikipages 
WHERE indexed_title LIKE '%대한민국%';

• 인덱스가 없는 경우:

%%sql
EXPLAIN ANALYZE SELECT * FROM wikipages 
WHERE title LIKE '%대한민국%';

tsvector

• 검색을 쉽게 하기 위해 텍스트를 어휘소(lexeme) 단위로 잘라서 위치와 함께 저장한 것

%%sql
SELECT to_tsvector('PostgreSQL is a powerful database');

• 기본적으로는 영어에 대한 전처리를 하도록 되어 있음. 설정을 simple로 바꾸면 빈 칸 단위로만 자름

%%sql
SELECT to_tsvector('simple', 'PostgreSQL is a powerful database');

• 검색어는 tsquery 형식을 사용, @@ 연산자로 일치 여부를 확인

%%sql
SELECT title FROM wikipages
WHERE to_tsvector('simple', title) @@ to_tsquery('simple', '대한민국');

tsvector 인덱스

• 검색할 때마다 tsvector로 변환하면 비효율적이므로 미리 변환된 컬럼을 만들고 인덱스를 설정

%%sql
ALTER TABLE wikipages ADD COLUMN tsvector_title tsvector;
UPDATE wikipages SET tsvector_title = to_tsvector('simple', title);
CREATE INDEX idx_wikipages_tsvector_title_gin ON wikipages 
USING GIN (tsvector_title);

• 검색

%%sql
SELECT title FROM wikipages 
WHERE tsvector_title @@ to_tsquery('simple', '대한민국');

GIN (Generalized Inverted Index)

• 검색 엔진의 역인덱스를 구현한 것 • 각 어휘소(lexeme)에 대한 인덱스 항목과 일치하는 위치의 압축된 목록을 포함 • 여러 단어를 검색하는 경우, 다음 GIN 인덱스를 사용하여 검색하는 단어가 포함된 행만 빠르게 추출 • 원문이 수정될 때마다 포함된 모든 어휘소의 역인덱스를 수정해야 하므로 업데이트가 느림

pg_trgm 확장

• 트라이그램(3-gram), 즉 3글자씩 잘라서 인덱싱 및 검색 • 띄어쓰기로 구분되지 않는 경우에도 빠르게 검색할 수 있음

%%sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
ALTER TABLE wikipages ADD COLUMN trgm_title TEXT;
UPDATE wikipages SET trgm_title = title;

CREATE INDEX idx_wikipages_trgm_title_gin ON wikipages 
USING GIN (trgm_title gin_trgm_ops);

• "대한민국"(4글자)의 검색 속도는 빨라짐

%%sql
EXPLAIN ANALYZE SELECT title FROM wikipages 
WHERE trgm_title LIKE '%대한민국%';

• 서울(2글자)의 검색 속도는 여전히 느림

%%sql
EXPLAIN ANALYZE SELECT title FROM wikipages 
WHERE trgm_title LIKE '%서울%';

형태소 분석된 컬럼의 인덱스 설정

• title_nouns 컬럼을 제목을 미리 형태소 분석 해서 명사만 추출해둔 컬럼 • simple로 tsvector로 변환 후 인덱스 설정

%%sql
ALTER TABLE wikipages 
    ADD COLUMN IF NOT EXISTS title_nouns_tsvector tsvector;
UPDATE wikipages 
    SET title_nouns_tsvector = to_tsvector('simple', title_nouns);
CREATE INDEX idx_wikipages_title_nouns_tsvector_gin ON wikipages
USING GIN (title_nouns_tsvector);

형태소 분석 후 인덱스 설정된 컬럼 검색

• "대한민국"(4글자) 빠르게 검색

%%sql
explain analyze SELECT title, title_nouns FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '대한민국');

• "서울"(2글자) 빠르게 검색

%%sql
explain analyze SELECT title, title_nouns FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '서울');

and, or

• AND

%%sql
SELECT title, title_nouns
FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '대한민국 & 역사')

• OR

%%sql
SELECT title, title_nouns
FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '대한민국 | 역사')

NOT, FOLLOWED BY

• NOT

%%sql
SELECT title, title_nouns
FROM wikipages
WHERE title_nouns_tsvector @@ 
    to_tsquery('simple', '대한민국 & !(헌법|형법|민법|상법)')

• FOLLOWED BY: 바로 다음에 이어 나오는 경우

%%sql
SELECT title, title_nouns
FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '대한민국 <-> 대통령')

<N>

• <->와 비슷하지만 바로 뒤가 아닌 N칸 뒤에 나오는 경우 • <->는 <1>과 같다 • 예: "대한민국 * 선거" 형태를 검색

%%sql
SELECT title, title_nouns
FROM wikipages
WHERE title_nouns_tsvector @@ to_tsquery('simple', '대한민국 <2> 선거')