一行入魂

PostgreSQLのcontribについてくるpg_trgmで全文検索してみました。
9.3からマルチバイトが使えるようになったので9.3beta1で試してみました。(9.3以前でもヘッダーを修正すれば使えるらしい)

適当なデータがほしかったので、疑似個人情報データ生成サービスからメールと住所のオプションをつけて5000件で生成してみました。
personal_infomation.csvが得られます。/tmp/personal_infomation.csvに置きました。一行目のヘッダーは邪魔なのでテキストエディターで消しました。

pg_trgmはソースからコンパイルしました。
9.3beta1をそもそもソースからコンパイルしているので、contrib/pg_trgmに移動してmake installすれば入ります。後はコマンドでインストール完了。

CREATE EXTENSION pg_trgm;

テーブル作成、データ読み込み、インデックスの作成をします。

CREATE TABLE w_user (
  id int
  ,kj text
  ,kn text
  ,sex text
  ,tel text
  ,mail text
  ,zip text
  ,address1 text
  ,address2 text
  ,address3 text
  ,address4 text
  ,address5 text
  ,birthday text
);

COPY w_user FROM '/tmp/personal_infomation.csv' FORMAT csv;

CREATE INDEX index_w_user_kn ON w_user USING gist(kn gist_trgm_ops);

では、試してみます。
まずindexが効いていないカラムで実験。

explain select * from w_user where kj like '%田中%'
Seq Scan on w_user (cost=0.00..178.50 rows=50 width=161)
Filter: (kj ~~ '%田中%'::text)

次にindexが効いているカラム。

explain select * from w_user where kn like '%タナカ%'
Index Scan using index_w_user_kn on w_user (cost=0.15..8.17 rows=1 width=161)
Index Cond: (kn ~~ '%タナカ%'::text)

20倍くらい早いですね。
pg_trgmは3文字で切ってindexを作っているそうなので、短くするとどうなるか見てみます。

explain select * from w_user where kn like '%タナ%'
Index Scan using index_w_user_kn on w_user (cost=0.15..8.17 rows=1 width=161)
Index Cond: (kn ~~ '%タナ%'::text)

explain select * from w_user where kn like '%タ%'
Bitmap Heap Scan on w_user (cost=34.44..160.59 rows=812 width=161)
Recheck Cond: (kn ~~ '%タ%'::text)
-> Bitmap Index Scan on index_w_user_kn (cost=0.00..34.24 rows=812 width=0)
Index Cond: (kn ~~ '%タ%'::text)

2文字でもindexが効いています。
1文字だとほとんどシーケンシャルスキャンと変わらないです。

ということで1文字以上の検索なら20倍も早いので、likeで検索する場合は使っておいたほうがよさそうです。
pg_trgmはsennaと違って外部ライブラリに頼らないのでインストールが楽なのもいいですね。