Postgresql FullTextSearch - 2017수정

디비에서 간단하게 문자열 검색을 할 때의 쿼리는 다음과 같다.

select * from {table} where {column} like ‘%검색어%’;

데이터 양 적거나/조회량이 적거나 해서 속도가 큰 문제가 되지 않을때는 이렇게 해도 충분하다.

 

like검색을 하면 무조건 full scan이 발생한다. 데이터가 늘어날수록 속도가 엄청나게 느려진다.

 

PostgreSQL Full Text Indexing

RDBMS에서도 문자열 검색이 필요할 때가 있는데 검색은 보통 루씬에 의존하는 방향으로 트렌드가 변하다 보니 FullTextSearch 쪽 기술은 싹이 트기도 전에 말라버려서… 영어쪽 기술은 충분히 지원되지만 한국어 지원은 잘 안된다.

인덱싱

인덱싱을 하면 한 문장이 형태소나 띄어쓰기 등 정해진 기준대로 단어를 분리해서 인덱스를 잡는다.

원래 문장 : ‘피자 맛있다. 햄버거는 몸에 안좋다.’

tsvector1=’피자’:1 ‘맛있다’:2 ‘햄버거는’:3 ‘ 몸에’:4:’안좋다’

tsvector2=’피자’:1 ‘맛있다’:2 ‘햄버거’:3 ‘ 몸’:4:’안좋다’ 5 ’는’:3 ‘에’

형태소 분석기가 돌아간다면 tsvector2의 결과가 나오겠지만 별다른 설정없이 처리하면 1과같은 결과가 나오게 된다. 이 경우에는 ‘햄버거’를 검색하면 결과가 안 나올 수 있다.

‘서울특별시 종로구 흥인동’ 과 같은 원문이라면 별다른 처리 없이 인덱싱을 해도 검색에 많은 도움이 된다.

한개 컬럼만 인덱싱

title 컬럼만 인덱싱 CREATE INDEX idx_gin_cadastral_title ON public.board USING GIN(to_tsvector(‘english’, title)); CREATE INDEX idx_gist_cadastral_title on public.board USING GIST(to_tsvector(‘english’, title));

gin, gist 인덱스를 두개 다 만든다.

여러컬럼 인덱싱 - tsvector 컬럼 추가

ALTER TABLE public.t_board ADD COLUMN tsvectors TSVECTOR; UPDATE public.t_board SET tsvectors = TO_TSVECTOR(‘english’, title || ‘ ‘ || content || ‘ ‘ || category) CREATE INDEX idx_gin_board_tsvectors on public.t_board USING GIN(tsvectors);

여러컬럼을 동시에 검색해야 하는 경우가 많으니.. 두번째 방법이 주로 사용된다.

검색

SELECT * FROM public.t_board WHERE tsvectors @@ to_tsquery(‘keyword’); SELECT * FROM public.t_board WHERE tsvectors @@ to_tsquery(‘keyword1&keyword2&keyword3’); SELECT * FROM public.t_board WHERE tsvectors @@ to_tsquery(‘keyword1|keyword2|keyword3’);

참고사이트

https://antjanus.com/blog/tutorials/using-postgresql-as-a-search-engine/ https://www.postgresql.org/docs/9.1/static/textsearch-controls.html http://postgresql.kr/tag/to_tsvector https://github.com/i0seph/textsearch_ko http://www.postgresql.org/docs/9.1/static/textsearch-indexes.html

Full-Text Search in PostgreSQL - A Gentle Introduction