pg_trgm 을 이용한 전후위 like 검색

하나의 컬럼에 대한 전후위 like 검색 시 인덱스 여부와 상관없이 table full scan 을 수행하는데 postgresql 은 pg_trgm 이라는 contrib 모듈을 통해 해결할 수 있습니다.

 

모듈 설치 전 실행계획입니다. 인덱스를 포함하는 컬럼에 대해 전후위 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)

 

contrib 모듈을 설치하고 해당 컬름으로 GIN INDEX를 생성합니다.

$ make && make install
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o trgm_op.o trgm_op.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o trgm_gist.o trgm_gist.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o trgm_gin.o trgm_gin.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o pg_trgm.so trgm_op.o trgm_gist.o trgm_gin.o -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags  
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/sh ../../config/install-sh -c -m 755  pg_trgm.so '/usr/local/pgsql/lib/pg_trgm.so'
/bin/sh ../../config/install-sh -c -m 644 ./pg_trgm.control '/usr/local/pgsql/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./pg_trgm--1.0.sql ./pg_trgm--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/'
$ psql -p 5433 -d testdb -U pgdba
psql (9.2.8)
Type "help" for help.
testdb=# create extension pg_trgm;
CREATE EXTENSION
testdb=# CREATE INDEX trgm_idx ON test_table USING gin (test_column gin_trgm_ops);
CREATE INDEX

 

설치 후 인덱스 스캔으로 변경된 것을 확인할 수 있습니다.

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의 비용범위를 초과할정도로 인덱스 스캔비용이 급격히 증가하여 옵티마이져가 table seq scan을 강제하므로 글자수에 대한 제약사항 대처는 application레벨로 넘겨야 할 듯합니다.