ホーム / Tiny Tiny RSS /

Tiny Tiny RSSの検索機能でインデックスを使う(PostgreSQL)

2014/11/09

pg_bigm PostgreSQL Tiny Tiny RSS

Google Reader難民からfeedlyを経て、最近はTiny Tiny RSS(TT-RSS)を使っています。 自前サーバでの運用で、DBMSにはPostgreSQLを使っています(TT-RSSはMySQLでも使えますが、PostgreSQLがおすすめだそうです)。 TT-RSSにはフィードエントリの検索機能がついており、タイトルや内容に含まれる文字列からの検索ができます。 検索にはPostgreSQLの中間一致検索が使われるのですが、この中間一致検索でインデックスが効くようにしてみます。

方針・概要

TT-RSSの検索ボックスにキーワードの羅列を入力すると、フィードエントリのタイトルカラムと内容カラムに対する中間一致検索となります。 これらのカラムにインデックスを張り、検索時に使われるようにします。 他にも、日付やスターの有無といったことによる検索もできるようですが、今回は考慮しません。

検索時のクエリは、column LIKE '%WORD%'といったLIKE演算子による中間一致検索です。 この場合、PostgreSQLでは、通常のB-Treeインデックスは使われません('WORD%'といった前方一致検索であれば使われることもあります)。 中間一致検索でも使えるインデックスのためのpg_trgmというモジュールがcontribパッケージとしてついてくるのですが、今回は使いません。 検索ワードが2文字('%楽天%'みたいなの)以下の場合、pg_trgmのインデックスは使えないためです。 また、日本語(マルチバイト文字)でもpg_trgmを使えるようにするためには、PostgreSQLのソース中のマクロ変数をいじってリビルドする必要があります。

今回は、pg_trgmの代わりにpg_bigmを使います。 pg_bigmも中間一致検索(というか、全文検索)向けのモジュールで、検索ワードが2文字以下でも使えるようになっていたりと、日本語との親和性が高いです。

pg_bigmのインストール

公式のドキュメント通りに進めれば、特に困ることはありません。

pg_bigmバイナリのインストール・構築

Red Hat Enterprise Linux系のOSであれば、rpmパッケージが公式で用意されています。 Debian wheezyについては非公式にdeb化しました。 これらのパッケージを、rpmなりdpkgなりでインストールすれば良いです。

いずれのOSでもなければ、公式のドキュメントを参考にビルド、インストールが必要です。

pg_bigmの設定・DBへの登録

postgresql.confのshared_preload_librariesに、pg_bigmを指定します。 また、必要であれば、pg_bigmの設定もpostgresql.confに追記します。 ただ、追記するパラメータとして意味があるのはpg_bigm.gin_key_limitぐらいだと思います。 これは、性能に関するチューニングパラメータの類いで、どれくらいの値が良いのかは、よく分かりません。

postgresql.confを書き換えたら、PostgreSQLを再起動します。

再起動したら、PostgreSQLの管理ユーザでTT-RSSのDBに入り、pg_bigmモジュールを登録します。 
$ psql -U postgres dbname

=# CREATE EXTENSION pg_bigm;
CREATE EXTENSION
=# \dx pg_bigm
                                 List of installed extensions
  Name   | Version | Schema |                           Description
---------+---------+--------+------------------------------------------------------------------
 pg_bigm | 1.1     | public | text similarity measurement and index searching based on bigrams
(1 row)

pg_bigmインデックスの作成

ttrss_entriesテーブルのtitleカラムとcontentカラムに対して、pg_bigmを使ったインデックスを張ります。 TT-RSSは、upper()関数を使って大文字小文字を無視するようにしているようなので、これも考慮した関数インデックスとします。 
CREATE INDEX ttrss_entries_title_content_idx
  ON ttrss_entries USING gin
  (upper(title) gin_bigm_ops, upper(content) gin_bigm_ops);

効果のほどは?

TT-RSSの検索ボックスに「楽天 社長」と入力し、10件ほど引っかかる場合で試してみます。 今回は、すべてshared_buffers上に載っている状態で確認します。

TT-RSSから投げられる中間一致検索のSQLの実行時間は、186[ms]から0.5[ms]と大幅に短縮しました。 読み込んだブロック数も4,200から66と、大幅に減りました。

ttrss_entriesテーブルの読み込み部分の実行計画は、テーブルの全件スキャンから、インデックスを使ったスキャンに変わりました。 
--
-- ttrss_entriesスキャン部分のみ抜粋
-- インデックス付与前
--
->  Seq Scan on ttrss_entries  (cost=0.00..3173.92 rows=1 width=722) (actual time=32.090..185.770 rows=11 loops=1)
      Filter: (((upper(title) ~~ '%楽天%'::text) OR (upper(content) ~~ '%楽天%'::text)) AND ((upper(title) ~~ '%社長%'::text) OR (upper(content) ~~ '%社長%'::text)))
      Rows Removed by Filter: 20256
      Buffers: shared hit=4152 dirtied=4

--
-- ttrss_entriesスキャン部分のみ抜粋
-- インデックス付与後
--
->  Bitmap Heap Scan on ttrss_entries  (cost=32.46..34.49 rows=1 width=722) (actual time=0.149..0.170 rows=11 loops=1)
      Recheck Cond: (((upper(title) ~~ '%楽天%'::text) OR (upper(content) ~~ '%楽天%'::text)) AND ((upper(title) ~~ '%社長%'::text) OR (upper(content) ~~ '%社長%'::text)))
      Buffers: shared hit=27
      ->  BitmapAnd  (cost=32.46..32.46 rows=1 width=0) (actual time=0.143..0.143 rows=0 loops=1)
            Buffers: shared hit=16
            ->  BitmapOr  (cost=16.10..16.10 rows=13 width=0) (actual time=0.057..0.057 rows=0 loops=1)
                  Buffers: shared hit=8
                  ->  Bitmap Index Scan on ttrss_entries_title_content_idx  (cost=0.00..8.05 rows=6 width=0) (actual time=0.037..0.037 rows=33 loops=1)
                        Index Cond: (upper(title) ~~ '%楽天%'::text)
                        Buffers: shared hit=4
                  ->  Bitmap Index Scan on ttrss_entries_title_content_idx  (cost=0.00..8.05 rows=6 width=0) (actual time=0.017..0.017 rows=41 loops=1)
                        Index Cond: (upper(content) ~~ '%楽天%'::text)
                        Buffers: shared hit=4
            ->  BitmapOr  (cost=16.10..16.10 rows=13 width=0) (actual time=0.079..0.079 rows=0 loops=1)
                  Buffers: shared hit=8
                  ->  Bitmap Index Scan on ttrss_entries_title_content_idx  (cost=0.00..8.05 rows=6 width=0) (actual time=0.028..0.028 rows=96 loops=1)
                        Index Cond: (upper(title) ~~ '%社長%'::text)
                        Buffers: shared hit=4
                  ->  Bitmap Index Scan on ttrss_entries_title_content_idx  (cost=0.00..8.05 rows=6 width=0) (actual time=0.051..0.051 rows=217 loops=1)
                        Index Cond: (upper(content) ~~ '%社長%'::text)
                        Buffers: shared hit=4
検索ワードごと(さらにtitleとcontentごと)にBitmapを作りながらインデックススキャンを複数回行い、最後にテーブルの必要な行のみをスキャンしたようです。 実行計画は複雑化しましたが、処理に必要だった時間とブロック数が大幅に減りました。

副作用

今回は測定しませんでしたが、インデックスを追加したことで、フィードエントリを収集する(DBのデータを更新する)処理は遅くなっているはずです。

また、テーブルサイズ20[MB]に対し、今回追加したインデックスは60[MB]ありました。 このTT-RSSは僕しか使わないので問題ないと思いますが、しかるべき規模であれば、それなりのメモリを積んでちゃんとキャッシュさせないと、ディスクI/Oが厳しいことになりそうと思いました。

環境

  • Debian wheezy
  • Tiny Tiny RSS 1.14
  • PostgreSQL 9.3.5
  • pg_bigm 1.1-20131122

QooQ