PostgreSQL : tsvector & GIN index (feat. %LIKE% 비교 분석)

들어가며

키워드 기반 조회 및 분류 작업을 할 때, 많은 개발자들이 LIKE %keyword% 연산자를 사용하곤 한다. 이 방식은 직관적이고 구현 난이도가 낮으나, 심각한 성능 저하를 일으 킬 수 있다.

 

이번 포스팅에선 이러한 단점을 극복하고 검색 성능을 획기적으로 향상시킬 수 있는 방법인 tsvector 와 GIN 인덱스 의 사용에 대해 알아 볼 것이다.

tsvector 란?

tsvector 는 PostgreSQL 에서 Full Text Search 를 위해 제공하는 특별한 데이터이다. 일반 텍스트 형태의 문서를 검색에 적합한 형태로 가공한 것으로, 고유한 lexeme들의 정렬된 목록을 제공한다.

 

lexemes 란 형태 변화에 따라 변형되는 단어들의 뿌리 형태이다. 예를 들어 walk 는 여러 형태의 단어다 있지만, (walks, walked, walking) 그 뿌리 형태인 walk 가 바로 lexeme 이다.

 

 

다음과 같은 코드를 tsvector 화 시킨다면,

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;

 

다음과 같은 결과가 나온다

 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

 

이 tsvector 는 정규화된 각 단어의 lexime 뿐만 아니라, 해당 단어가 등장하는 위치 또한 기록한다.

다음과 같은 형태로 말이다.

 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4

tsvector 는 PostgreSQL 의 Full Text Search 를 위한 데이터 타입이다. %LIKE% 연산 또한 가능하지만, 이 기능은 인덱스를 사용할 수 없고, 느리다는 단점이 있다.

주요 API

tsvector : 텍스트를 검색 가능한 형태로 변환한 자료형

tsquery : 검색 질의를 표현하는 자료형

to_tsvector() : 일반 text -> tsvector 로 변환

to_tsquery() : 검색어를 tsquery 로 변환

@@ : tsvector 와 tsquery 를 매칭하는 연산자

GIN index : Generalized Inverted Index

• Many to Many 혹은 복합 값을 가진 컬럼( tsvector ) 에 대해 빠른 검색을 가능하게 해주는 인덱스이다.
• 핵심 아이디어는, key 가 주어졌을때, 해당 key 를 포함하는 레코드 목록을 빠르게 조회하는 것이다.
• 기본적으로 B-Tree 와 유사하게 구성되지만, 다른점은, 각 리프노드가 단일 값이 아닌 해당 key 를 포함하는 ID 목록을 가지게 된다.

tsvector 만으로는 빠른 검색을 보장하지 않는다. 그렇기 때문에, tsvector 컬럼에 인덱스가 없다면, 검색시 테이블의 모든 행을 순차적으로, full table scan 을 해야 하기 때문에 느릴 수 밖에 없다.

 

하지만, GIN index 와 함께 사용할 경우, tsvector 컬럼의 검색 속도를 획기적으로 증가 시킬 수 있다.

 

특정 key 값 - 해당 key 가 존재하는 row num 을 기록한다. 이후, 쿼리가 들어올 경우, & 연산에 따라 검색된 목록을 얻어 사용자에게 반환한다.

실험 %LIKE% vs tsvector + GIN index

100,000 건의 기사 데이터에 대해, 기사의 제목 + 요약 에 대한 tsvector 컬럼을 생성하여, 없을때의 LIKE 연산자와의 비교 분석이다.

 

우선 다음과 같이 index, tsvector, trigger 와 function 을 생성하자.

-- 1. tsvector 타입 컬럼 추가
ALTER TABLE articles ADD COLUMN IF NOT EXISTS body_tsv tsvector;

-- 2. GIN Index 생성
CREATE INDEX articles_body_gin ON articles USING gin(body_tsv);


-- 3. tsvector 자동 업데이트 함수 정의
CREATE OR REPLACE FUNCTION articles_tsv_trigger()  
RETURNS trigger AS $$  
BEGIN  
  NEW.body_tsv :=  
    to_tsvector(  
      'simple',  
      coalesce(NEW.title,'') || ' ' || coalesce(NEW.summary,'')  
    );  
RETURN NEW;  
END $$ LANGUAGE plpgsql;

-- 4. INSERT 또는 UPDATE 시 함수를 실행하는 트리거 생성
CREATE TRIGGER article_tsv_trg  
    BEFORE INSERT OR UPDATE ON articles  
    FOR EACH ROW EXECUTE FUNCTION article_tsv_trigger();

 

 

LIKE 연산자 FULL TABLE SCAN

EXPLAIN SELECT * FROM articles a WHERE (a.title LIKE '%1%' OR a.summary LIKE '%1%');

 

• Seq Scan
• 예상 비용: 8690.24
• 이 방식은 컬럼 시작부분이 아닌 중간과 끝에 와일드카드가 사용되어 인덱스를 전혀 사용할 수 없다. 즉, Full Table Scan 이 발생하게 되며, 이는 데이터 양이 많아질 수록 비용이 선형적으로 증가하게 된다.

GIN Index, tsvector 사용

 

EXPLAIN SELECT * FROM articles WHERE body_tsv @@ to_tsquery('simple', '1');

• Bitmap Index Scan 을 통해 tsvector 컬럼에 생성된 GIN index 를 통해 검색 키워드가 포함된 row 의 위치 목록만 반환한다.
• Bitmap Heap Scan : 위에서 얻은 위치 목록을 기반으로 실제 테이블에서 필요한 row 만 접근
• index 검색 비용 : 19.6
• 레코드 접근 비용 : 1590
• Full Text Search 로 검색 성능이 극대화 된다