調べた経緯

データベースの内容を全文検索したいという要望は定期的にあるので、常に情報は探しているんだけど、PostgreSQLに関しては調べたことなかったので色々調べてみた。

Senna

Senna 組み込み型全文検索エンジン - Senna 組み込み型全文検索エンジン

これがスタンダードだった時期もあるんだろうけど、結局PostgreSQL9.x系にインストール出来なかった。
textsearch_sennaを使って動かそうとしたんだけど、そもそも公式サイトで配布されている圧縮ファイルをダウンロードしても、ちゃんとインストール出来ない。
CVSでソースコードをダウンロードして・・・という手順だと動くとMLに書かれていたので、チャレンジしてみたけど結局ダウンロード出来なかった。
ダウンロード出来たとしても、Senna本体が2010年からリリースされてないみたいだから、はっきりいって今更感が強く不安あり。

これからの新しいシステムでSennaを選択する積極的な理由はあまり無い気がする。
ということで見送り。

Groonga

groonga

Sennaの後継らしい。
Sennaが開発されてないと思ったら、こっちを頑張っていたのね。
どうもまだまだPostgreSQLではあんまり実績無さそうかな。
textsearch_groongaも0.1がリリースされた後、音沙汰無い感じだし、ちょっと厳しいかも。
Groongaを使うんなら、単体で動かしたほうが良さげってことで、こちらも見送り。

unigram + index(以下unigram)

参考サイト
中間一致検索でインデックスを使う PostgreSQL日記/ウェブリブログ

導入が関数用意するだけというお手軽度ナンバー1。
再コンパイルも必要なければレプリケーションとかも気にしなくても良いのは素敵だわー。
つうことで、今回の調査対象入り。

pg_bigm

pg_bigm

pg_trgmの弱点である3文字以下でも早いというのがこれ。
今回の大本命!でめちゃくちゃ期待してる!
もちろん調査対象

pg_trgm

pg_trgm

こいつの存在は前から知っていて、個人的にはコレで良いんじゃないか?って思っていたんだけども、「2文字以下じゃインデックス効かない」というのが非常にネック。
日本語は2文字以下の単語も結構あるからねえ。
個人的にはそんぐらい良いだろって思ってるんだけど、まあ、一抹の不安はあるよねってことで、今回調査対象。

テストデータの用意

青空文庫にある夏目漱石の長編15本のテキストデータを400文字で分割してテーブルに格納。
実際に格納してみると5000レコードぐらいにしかならなかったので、10万件を超えるまでぐるぐる回してみた。
なので、重複データありまくり。

テーブルの用意

CREATE TABLE natsume (id serial, text text);

unigram用の関数用意

参考サイトと内容は全く一緒、関数名を変更しただけ。

to_tsquery_ungm()

CREATE OR REPLACE FUNCTION to_tsquery_ungm(text) RETURNS tsquery AS $$
  select to_tsquery(array_to_string(regexp_split_to_array($1, E'\\s*'),'&'));
$$ language sql;

to_tsvector_ungm()

CREATE OR REPLACE FUNCTION to_tsvector_ungm(text) RETURNS tsvector AS $$
  select to_tsvector('simple', array_to_string(regexp_split_to_array($1, E'\\s*'),' '));
$$ immutable language sql;

unigram用のIndex作成

CREATE INDEX natsume_text ON natsume USING gin (to_tsvector_ungm(name));

pg_bigm用のIndex作成

CREATE INDEX natsume_text ON natsume USING gin (text gin_bigm_ops);

pg_trim用のIndex作成

CREATE INDEX natsume_text ON natsume USING gin (text gin_trgm_ops);

それぞれのディスクサイズ

table or index name normal unigram bigm trgm
natsume 135 MB - - -
natsume_text(Index) - 97 MB 285 MB 363 MB
  • natsumeはテーブル自体のサイズなので、全てのテーブルで同じ
  • normalにはIndexが張られてないので無し

pg_trim&pg_bigm用SELECT文

SELECT * FROM natsume WHERE text LIKE '%ほげほげ%';

unigram用SELECT文

SELECT * FROM natsume WHERE to_tsquery_ungm('ほげほげ') @@ to_tsvector_ungm(text);

その他留意点

  • さくらのVPSの1Gプランで実施
  • DebianのSqueezeのBackportsでインストールされるPostgreSQL9.1を利用
  • SQLを発行する前にPostgreSQLのキャッシュをクリアするようにした

unigram用SELECT文

SELECT count(*) FROM natsume;
  • 検索用のSQLを発行する前に、全件の件数を取得するSQLを流すようにした(INDEXを使う検索は軒並み遅く、速度のばらつきも酷かったので)

結果(キャッシュをクリアした場合)

keyword count normal unigram bigm trgm
15732 370.910 238.492 542.179 371.306
1314 365.886 26.803 60.567 359.069
京都 2196 323.250 211.473 94.935 320.353
家人 54 368.295 273.826 16.874 382.033
吾輩は 2466 369.603 196.628 155.279 146.749
勤勉家 36 375.764 16.284 24.112 35.588
猫である 216 317.620 243.783 803.984 440.723
吾輩はここで 18 380.825 336.531 195.874 101.458
人間というもの 90 349.595 104,698.142 715.815 355.207
  • 単位はms
  • normalは何もINDEXがないテーブル
  • 時間は5回処理した平均時間

結果その2(キャッシュをクリアしなかった場合)

keyword count normal unigram bigm trgm
15732 375.654 79.903 89.522 356.367
1314 384.900 8.066 8.046 364.984
京都 2196 323.976 21.468 10.226 335.592
家人 54 367.345 120.684 0.347 394.304
吾輩は 2466 366.956 41.094 14.611 18.039
勤勉家 36 362.231 5.889 0.290 0.499
猫である 216 306.679 42.277 19.732 7.332
吾輩はここで 18 361.725 271.502 12.907 1.768
人間というもの 90 323.270 105,300.982 36.325 10.505
  • 単位はms
  • normalは何もINDEXがないテーブル
  • 時間はキャッシュが効かない1回目を除く2~6回目に処理した平均時間

感想

インストールについて

  • 手軽さは unigram > pg_bigm > pg_trgm
  • PostgreSQLをパッケージでインストールしている場合、pg_trgmは再コンパイルしなくちゃいけないので面倒
  • pg_bigmはソースからインストールする必要あるが、PostgreSQL自体はパッケージでインストールしたものでも可

unigramについて

  • 不得意な検索キーワードになると恐ろしく遅くなることがある
  • 全体的にそれ程効果が無い気がする
  • あまり使われない文字を使う名詞には強そう
  • SELECT文のWHERE句の表記が独特なので、後から追加する場合はアプリケーション側の変更も必要なのはマイナス要素。

pg_bigmについて

  • 1文字でも2文字でもインデックスが効く
  • トータルで一番無難な結果を出しているような気がする
  • 検索する文字列が長くなるにつれて、pg_trgmより遅くなっていく

pg_trgmについて

  • pg_trgmは3文字以上の検索ではやはり一番速い
  • pg_trgmは2文字以下の場合、シーケンシャル検索になる
  • 一番安定している気がする

その他

  • シーケンシャルな検索は文字数がいくつだろうが、どんな文字列だろうがあまり速度は変わらない
  • ひらがなや文章といった検索に関しては、N-Gramな検索の皆さんは軒並み苦手
  • インデックスを効かせている検索は、2回目以降は劇的な速さを見せるので、用途によっては非常に効果的

まとめ

日本語の全文検索関連のプロジェクトを見ているとpg_bigmがどの程度メンテナンスされていくのか不安。
その半面pg_trgmの方は標準に含まれている拡張なので、保守性は上かなと思う。
元々ソースからインストールしている様な人なら、pg_trgmで良い気がする。
個人的には、いうほどシーケンシャルな検索も遅くない感触なので、pg_trgmの欠点ってそれ程でもないなというのが率直な感想。
まあ、10万件程度だからかもしれないけどね。

pg_bigmもそんなに悪くない結果だと思うし、サーバ性能がもっとリッチなら1文字2文字にもインデックスが効きも良くなると思う。
導入も後からインストール出来るので、pg_trgmより個人的には気軽。
個人で使うならこっちかな。

あと、「これらのツールを使うとめちゃくちゃ速くなった!」と解説しているサイトは、個人的にPostgreSQLのキャッシュをクリアしてないんじゃないか?という疑惑を持った。
50倍とか100倍の速度とかってのは、余程キーワードとの相性が良い場合しか出せない効果だと思うし、もし相性が悪い場合はシーケンシャル検索よりも遅くなる、それも結構な頻度で。
それか僕のやり方がちょっとおかしいのかな・・・。
件数が10万件というのは、このスペックだと妥当だと思う。
100万件入れようとしたら、1日以上かけても終わらなかったし(特にtrgmが遅かった)。
Indexを作成するのにコストがかかるだよなー。

まあ、どのツールもそれ程万能的な効果は期待できないかなというのが、素直な感想。
けど、pg_bigmとpg_trgm、そしてunigram+indexと選択肢があることがありがたいね。
何より開発に携わっている人達にはとにかく感謝、感謝。

参考サイト