LIKE 検索等では通常のインデックス(btree)が使えない*1
…ということを意識していなかったので、
jcomi_db=# SET ENABLE_SEQSCAN=OFF; jcomi_db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM t_book_info WHERE book_title LIKE '%ラブひな%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Aggregate (cost=87.40..87.41 rows=1 width=0) (actual time=2.352..2.354 rows=1 loops=1) -> Seq Scan on t_book_info (cost=0.00..87.36 rows=14 width=0) (actual time=0.078..2.282 rows=14 loops=1) Filter: (book_title ~~ '%ラブひな%'::text) Rows Removed by Filter: 1535 Total runtime: 2.674 ms (5 行)
のように、Seq Scanになってしまう。
postgres=# SELECT VERSION(); version ------------------------------------------------------------------------------------------------------- PostgreSQL 9.3.2 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 32-bit (1 行) postgres=# CREATE EXTENSION pg_trgm; ERROR: 拡張機能の制御ファイル "/usr/pgsql-9.3/share/extension/pg_trgm.control" をオープンできませんでした: そのようなファイルやディレクトリはありません
すでにインストール済みの PostgreSQL 9.x に pg_trgm を追加する方法
自分は、PostgreSQL 9.3 を
の手順で、yum を使ってインストールしたために、コンパイル環境が残っておらず、ソースからダウンロードする必要があった。
$ wget http://ftp.postgresql.org/pub/source/v9.3.2/postgresql-9.3.2.tar.gz $ tar xvfz ./postgresql-9.3.2.tar.gz $ cd ./postgresql-9.3.2 $ eval ./configure `pg_config --configure`
インストール済みの PostgreSQL がコンパイルされた際の configure のオプションを、pg_config で取得して渡してやる。
$ make $ cd ./contrib/pg_trgm/ $ vi ./trgm.h
trgm.h 内で、KEEPONLYALNUM オプションを無効化しておかないと、日本語等の検索時はかえって効率が落ちてしまうらしい。
#define KEEPONLYALNUM
の箇所を
/* #define KEEPONLYALNUM */
のようにコメントアウトしておく。
$ make $ su # make install
これで、
jcomi_db=# CREATE EXTENSION pg_trgm; CREATE EXTENSION jcomi_db=# CREATE INDEX idx_t_book_info_book_title_trgm ON t_book_info USING gin (book_title gin_trgm_ops);
のような感じで、INDEX を作成してやると、
jcomi_db=# SET ENABLE_SEQSCAN=OFF; jcomi_db=# EXPLAIN ANALYZE SELECT COUNT(*) FROM t_book_info WHERE book_title LIKE '%ラブひな%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- ----- Aggregate (cost=55.27..55.28 rows=1 width=0) (actual time=0.254..0.255 rows=1 loops=1) -> Bitmap Heap Scan on t_book_info (cost=20.11..55.23 rows=14 width=0) (actual time=0.145..0.213 rows=14 loops=1) Recheck Cond: (book_title ~~ '%ラブひな%'::text) -> Bitmap Index Scan on idx_t_book_info_book_title_trgm (cost=0.00..20.11 rows=14 width=0) (actual time=0.097..0.097 rows=14 loop s=1) Index Cond: (book_title ~~ '%ラブひな%'::text) Total runtime: 0.443 ms (6 行)
のように、Bitmap Index Scan により、検索速度が向上する。
ただし、検索文字が2文字以下の場合はかえって遅くなる。
*1:詳細はテキスト検索の方法とインデックス — Let's Postgres
