pg_trgm 설치하기

postgresql 추가모듈 중 pg_trgm을 이용하여 '%문자열%' like 검색시 인덱스 스캔을 실행할 수 있습니다.

설치하기 전의 옵티마이저는 아래와 같이 full scan 메소드를 선택합니다.

testdb=# explain analyze select * from test_table where test_column like '%TEXT%';
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on test_table  (cost=0.00..288431.08 rows=264 width=298) (actual time=952.905..1119.831 rows=6 loops=1)
   Filter: ((test_column)::text ~~ '%TEXT%'::text)
 Total runtime: 1119.854 ms
(3 rows)

위와 같은 경우의 인덱싱 지원을 위해
1. contrib모듈을 설치합니다.

$ make
$ make install

2. 해당 데이터베이스에 extension을 생성합니다.

testdb=# create extension pg_trgm;
CREATE EXTENSION
CREATE INDEX trgm_idx ON test_table USING gin (test_column gin_trgm_ops);
CREATE INDEX

설치 이후 동일한 SQL에 대하여 index scan 메소드가 선택된 것을 확인할 수 있었습니다.

testdb=# explain analyze select * from test_table where test_column like '%TEXT%';
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=22.42..1245.43 rows=313 width=298) (actual time=0.021..0.029 rows=6 loops=1)
   Recheck Cond: ((test_column)::text ~~ '%TEXT%'::text)
   ->  Bitmap Index Scan on trgm_idx  (cost=0.00..22.35 rows=313 width=0) (actual time=0.014..0.014 rows=6 loops=1)
         Index Cond: ((test_column)::text ~~ '%TEXT%'::text)
 Total runtime: 0.051 ms
(5 rows)

※제약사항 : 3글자 미만에 대한 패턴검색의 경우 인덱스 탐색비용이 급격히 증가, table full scan의 비용을 초과함.