PostgreSQL에서 LIKE 검색 속도를 높여주는 pg_trgm 활용해보기

게시일 : 2019년 10월 31일    
# PostgreSQL # pg_trgm

PostgreSQL에서 LIKE 검색 속도를 높여주는 pg_trgm을 활용해본다.

PostgreSQL 10.9 버전(docker)에서 테스트

사용 데이터

Kaggle의 Amazon reviews: Kindle Store Category 데이터를 활용하였다. 긴 텍스트가 들어있는 reviewText 컬럼을 활용한다.

pg_trgm이란

Trigram을 기반으로 텍스트 검색 속도를 높여주는 extension이다. FULL TEXT SEARCH와는 차이점이 있지만 간단하게 구현할 수 있는 장점이 있다.

Trigram 기반이기 때문에 LIKE + ‘ ‘ 안에 (%를 제외한) 3글자 이상부터 적용 가능하다는 것을 주의해야 한다.

자세한 설명 : pg_trgm - PostgreSQL

아무 설정없는 QUERY

Seq Scan 즉, Full Table Scan을 수행한다.

기본 SQL

-- " "는 reviewText라는 컬럼명을 인식시키기 위함(대문자가 문제인 듯..)
EXPLAIN ANALYZE SELECT no, "reviewText" FROM amazon_reviews WHERE "reviewText" ILIKE '%nice';

ILIKE + ‘nice%’ / ‘%nice’ / ‘%nice%’에 대해 결과를 확인해본다.

1. ‘nice%’와 ‘%nice’

nice%와 %nice의 쿼리 결과는 비슷한 속도를 보인다.

Gather  (cost=1000.00..149090.85 rows=9900 width=486) (actual time=5.229..3411.057 rows=1687 loops=1)"
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on amazon_reviews  (cost=0.00..147100.85 rows=4125 width=486) ...
        Filter: ("reviewText" ~~* 'nice%'::text)
        Rows Removed by Filter: 326977
Planning time: 1.156 ms
Execution time: 3411.280 ms

2. ‘%nice%’

이 경우는 병렬 처리도 수행되지 않는 특징을 보인다.

Seq Scan on amazon_reviews  (cost=0.00..154247.65 ...) (actual time=2.373..11019.827 rows=53715 loops=1)
  Filter: ("reviewText" ~~* '%nice%'::text)
  Rows Removed by Filter: 928903
Planning time: 2.224 ms
Execution time: 11023.618 ms

LIKE와 INDEX

일반적인 B-Tree INDEX의 경우, ‘nice%’인 경우에만 사용할 수 있다. index를 LEFT-TO-RIGHT 방식으로 생성하는 특징 때문이라고 한다.

또한, B-Tree INDEX의 경우, 짧은 text 또는 numeric타입 컬럼에 적용해야 효과적이다.

LONG TEXT의 B-Tree INDEX를 적용하려면 다음과 같은 오류가 나올 수도 있다.

ERROR: index row requires XXXX bytes, maximum size is 8191

LONG TEXT의 경우, GIST와 GIN INDEX를 주로 활용하는데 검색 측면에서는 GIN INDEX가 속도가 더 빠르다고 한다.

이 두 INDEX를 적용하려면 tsvector or tsquery 타입에 대해 알아야 한다. 언어적 특성도 포함되므로 고려할 점이 많다.

pg_trgm은 이를 쉽게 적용할 수 있도록 도와준다.

pg_trgm 설정 방법

CREATE EXTENSION pg_trgm;

CREATE INDEX idx_text ON amazon_reviews USING GIN("reviewText" gin_trgm_ops);
-- 이 데이터는 약 4분 정도 소요되었다.

pg_trgm 적용 후 QUERY

Bitmap Heap Scan 즉, GIN INDEX를 활용하여 검색 속도가 빨라진다.

1. ‘nice%’와 ‘nice%’

이전보다 대략 2-3 배 정도 빨라진다.

Bitmap Heap Scan on amazon_reviews  (cost=140.92..31148.26 ...) (actual time=42.861..1284.783 rows=1687 loops=1)
  Recheck Cond: ("reviewText" ~~* 'nice%'::text)
  Rows Removed by Index Recheck: 54845
  Heap Blocks: exact=38374
  ->  Bitmap Index Scan on idx_text  (cost=0.00..138.44 ...) (actual time=36.123..36.123 rows=56532 loops=1)
        Index Cond: ("reviewText" ~~* 'nice%'::text)
Planning time: 1.085 ms
Execution time: 1285.029 ms

2. ‘%nice%’

이전보다 대략 7-8 배 정도 빨라진다.

Bitmap Heap Scan on amazon_reviews  (cost=573.52..111274.87 ...) (actual time=28.608..1471.702 rows=53715 loops=1)
  Recheck Cond: ("reviewText" ~~* '%nice%'::text)
  Rows Removed by Index Recheck: 8416
  Heap Blocks: exact=40974
  ->  Bitmap Index Scan on idx_text  (cost=0.00..558.63 ...) (actual time=20.722..20.722 rows=62131 loops=1)
        Index Cond: ("reviewText" ~~* '%nice%'::text)
Planning time: 1.765 ms
Execution time: 1474.519 ms

결과 종합 (단위 : ms)

QUERY 기본 pg_trgm 결과
%nice(%) 3411.057 1284.783 2-3 배 빨라짐
%nice% 11019.827 1471.702 7-8 배 빨라짐

참고 : Compound columns 즉, 두 개 이상의 TEXT 컬럼을 합쳐서 index 구성도 가능하다. (아래 reference 2 참고)

References :