以前投稿した記事にて PostgreSQL で大量データに対する中間一致検索(LIKE検索)するためのインデックス作成について簡単に触れました。そのとき、 pg_trgm (trigram, トリグラム)を利用するのが有力な候補であり、詳しくは別記事で、、、と書いたっきりでした。半年近く間が空いてしまって恐縮ですが、書くと言った以上、書かないと年が越せない気がするので、今更ながら当時調査した内容についてシェアします。概要
- 1. PostgreSQL の contrib である pg_trgm を使ったインデックスを作成する
- サイト「Let's postgres」の記事「テキスト検索の方法とインデックス」の「中間一致検索」の項で説明されているものです
- 2. 全文検索エンジン Senna を利用した textsearch_sennna を使ったインデックスを作成する
- textsearch-ja プロジェクトの「textsearch_sennna」に則ったものです
- 3. テキストを unigram で1文字ずつに分解して PostgreSQL の全文検索機能を利用する
- ブログ「PostgreSQL日記」の記事 「中間一致検索でインデックスを使う」で紹介されている方法をそのまま利用したものです
各手法の詳細と比較
| 手法 | 概要 | メリット | デメリット | |
| 1 | pg_trgm (v9.1 以上) | PostgreSQL v9.1 以上のみ。contrib の pg_trgm を使う。 ストリーミングレプリケーションに対応。 LC_CTYPE を ja_JP に指定する必要あり。 | LIKE文がそのまま使える。 レプリケーション対応。 PostgreSQL内で完結 | 3文字以上の検索じゃないとインデックス効かない。 インデックスのサイズが大きくなりがち。 |
| 2 | textsearch_senna + like_ops | Senna を利用した日本語用N-gram全文検索を LIKE 用オプションで使う。インデックスファイルが外部にできるのでレプリケーションに対応していない。 | LIKE文がそのまま使える。 | 外部プログラム Senna が必要。 レプリケーションに対応してないのでレプリケーションを利用して運用するなら難あり。 |
| 3 | unigram + GIN インデックス | tsvector, tsquery による全文検索を unigram に分解して使う。 function の登録が必要。 | PostgreSQL内で完結。 | LIKE文そのまま使えない。 検索文字列が長いほど遅い傾向。 インデックス作成遅い。 |
セットアップや実行の簡便さなど、それぞれに特徴がありますが、実際の検索パフォーマンスはどんなもんなんでしょうか。
具体的な検索速度の比較
環境:
検証には PostgreSQL 9.1 を利用します。マシン環境は OpenVZ 上の仮想OS (CentOS 5), Memory割り当て = 4GB, CPU = Xeon 2.13GHz × 4 といったスペックです。なお、 PostgreSQL の shared_buffers は 1GB に設定しています。
データ:
テキスト型のカラムを持つテーブルを作成し、100文字以下の日本語文字列を 1000万件入れます。具体的には、青空文庫から夏目漱石の「吾輩は猫である」のテキストをダウンロードし、100文字以下になるよう分解し、 1000万件まで繰り返し投入しました。(なので、全く同じ内容のレコードが重複しています)これを、3 つの手法のためにそれぞれ準備します。
なお、 pg_trgm のためのデータベースは、 LOCALE_CTYPE の指定を UTF8.ja_JP とします。
% CREATE DATABASE <db_name> ENCODING UTF8 LC_CTYPE UTF8.ja_JP
結果:
ほんのいくつかの検索文字列だけですが、3つの手法でインデックスはったカラムに対する LIKE 検索の応答時間の結果です。単位は ms (ミリ秒)です。
pg_trgm が 3文字以上の検索でしか有効でないので、3文字以上の文字列でのみ検索しています。あと、同じ文字数でもどれくらい検索結果が返ってくるものかによって、比較してみました。
赤字にしているのが、検索文字列に対して最速の結果を返したものです。「2回目」の列は、DBのキャッシュにのった結果が応答されているため基本的に高速ですが、参考まで。
考察:
・頻出する文字列による検索だと、どの検索もあまり早くない。シーケンシャルスキャンした場合と同等か、ヘタするとより遅い。・ユニークな文字列(eg. 「勤勉家」)だと、pg_trgm, textsearch_sennna が概ね同じ水準で比較的はやい。unigram による検索は、ひとつひとつの文字列がありふれたもの(eg. 「十二月」)だと、遅くなる傾向がうかがえる
・検索文字列が長い場合も、全体的に遅め。特に unigram は１文字ずつに分解するせいか、より遅い傾向がうかがえる(２回目も遅い)
まとめ
ストリーミングレプリケーションなどの運用が必要ないなら、 textsearch_senna は有力な選択肢かもしれません。
unigram による検索は検索クエリも通常の LIKE 検索とは異なるものにする必要がありますが、PostgreSQL の機能のみで利用可能なので、検索文字列がそれほど長くならない場合が大半なようであれば、候補になるでしょう。
といった感じで、実際に試してみると思ってた以上に各手法の特色があらわれてなかなか興味深いですね。今回の結果はあくまで、ある環境とデータ状況における結果例です。どの方法が最適か検討するにあたっては、上記の特色を運用や機能要件にあてはめてみた上で、実際に使用するデータによる検索も試してみるとよいでしょう。
