風柳メモ

ソフトウェア・プログラミング関連の覚書が中心

PostgreSQL 9.x に pg_trgm を後から追加

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 を

CentOS 6.5 導入時のメモ(続き: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文字以下の場合はかえって遅くなる。