Heroku PostgreSQLのlc_ctypeがen_US.UTF-8でもpg_trgm導入で日本語全文検索は機能するかもしれない話

Heroku Postgresで日本語全文検索すると重い

以前、なろうの非公式APIクライアントを作ってみたので、

これを使って検索サイトを作ってみました。趣味と実益を兼ねた代物です。

構成は大体以下の通りです。

• サーバ：Heroku(無料版)
• 言語：python
• WEBフレームワーク：Flask
• DB：Heroku Postgres(有料版)

初めは無料版のみで収めようと思ったのですが、なろうに掲載されているタイトルを検索のたびに自動追加していたらあっという間に上限の1万行を突破しました。なのでDBだけは、千万行まで使える一つ上のランクの有料版を使っています。
ただ、サーバーの方はまだ無料版を使っているので、三十秒以上のリクエストでタイムアウトが発生します。そのうちリッチな処理をやりたくなったら有料版に切り替えるかもしれませんが、とりあえず無駄に三十秒オーバーしない内は無料版にしておこうと思ったら、

heroku[router]: at=error code=H12 desc="Request timeout" 

結構頻繁に出てくる。
タイムアウトしないとしても検索結果が出るのに十秒近くかかるのは、UX的にもあまり良くない。

実行計画の取得

調べたらやはりというべきかSQL実行にかなり時間がかかっている。
例えばこれは開発環境で実施した結果ですが、

EXPLAIN ANALYZE SELECT
    *
FROM
    narou_novels
WHERE
    (
        narou_novels.title ILIKE '%男主人公%'
    OR  narou_novels.writer ILIKE '%男主人公%'
    OR  narou_novels.story ILIKE '%男主人公%'
    OR  narou_novels.keyword ILIKE '%男主人公%'
    )
ORDER BY
    narou_novels.daily_point DESC
LIMIT 20 OFFSET 0;

                                                                                           QUERY PLAN                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1621.73..1621.78 rows=20 width=1047) (actual time=56.660..56.666 rows=20 loops=1)
   ->  Sort  (cost=1621.73..1626.97 rows=2093 width=1047) (actual time=56.659..56.662 rows=20 loops=1)
         Sort Key: daily_point DESC
         Sort Method: top-N heapsort  Memory: 87kB
         ->  Seq Scan on narou_novels  (cost=0.00..1566.04 rows=2093 width=1047) (actual time=0.031..54.243 rows=2038 loops=1)
               Filter: (((title)::text ~~* '%男主人公%'::text) OR ((writer)::text ~~* '%男主人公%'::text) OR ((story)::text ~~* '%男主人公%'::text) OR ((keyword)::text ~~* '%男主人公%'::text))
               Rows Removed by Filter: 6614
 Planning Time: 1.548 ms
 Execution Time: 56.723 ms
(9 rows)

PostgreSQLの実行計画の読み方は公式ドキュメントで確認すると大体こんな感じ

{ノード種類}  (cost={初期処理の推定コスト}..{全体推定コスト} rows={この計画ノードが出力する行の推定数} width={この計画ノードが出力する行の（バイト単位での）推定平均幅}) (actual time=初期処理の実時間}..{全体実時間} rows={この計画ノードが出力する行の推定数} loops=1)

結果を見るとやはり、ILIKEとORで全文検索している箇所が一番負荷が高い。

Seq Scan on narou_novels  (cost=0.00..1566.04 rows=2093 width=1047) (actual time=0.031..54.243 rows=2038 loops={そのノードを実行する総回数})

開発環境のたった数千行でもこうなるなら、数万行の本番環境だとかなり酷いパフォーマンスになってくる。
実際、キャプチャし損ねたのでお見せできませんが、数秒は余裕でかかっていた。せめて一秒以内に抑えたい。
なので、まずはSeq ScanをIndex Scanにしたい。これをするだけでもかなり違ってくるはず。
これらの違いはこのブログの解説がわかりやすかった。

インデックスの設定

しかし方法を調べると悲観的な情報しか出てこない。
単純なインデックスはどうもダメっぽい。
というのは以下のようなパターン(LIKEまたはILIKEで先頭にワイルドカード)はダメと公式でも記載されているからだ。

narou_novels.title ILIKE '%男主人公%'

なら他の方法は？と調べるとこのブログに拡張機能のpg_trgmを使ったらいいと書かれていた。
公式ドキュメントでもインデックス検索をサポートするとの記載が！

PostgreSQL 9.1から、これらのインデックス種類はLIKEおよびILIKEにおけるインデックス検索をサポートします。 以下に例を示します。
SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

Heroku Postgresの拡張機能でもサポートされているっぽい
ならこれで解決か？と思ったが先程のブログをみたところどうも制約があるらしい。

LC_CTYPEにC.UTF-8を指定するにはinitdb実行時に指定する必要があります。すでにC.UTF-8以外で作成されているデータベースではこの方法は使えません。
pg_trgmでは2文字以下の文字列に対してインデックスを使えません。たとえば「開発者」ではインデックスを使えますが、「開発」では使えません。

後者は最悪目を瞑るとしても前者の前提条件を満たせていないのはまずい。
なので、早速Herokuの文字コードを確認してみた。
まずは以下のコマンドで本番DBにログインして、

heroku pg:psql

以下のsqlで確認

select name,setting,context from pg_settings where name like 'lc%';

結果

    name     |   setting   |  context  
-------------+-------------+-----------
 lc_collate  | en_US.UTF-8 | internal
 lc_ctype    | en_US.UTF-8 | internal
 lc_messages | en_US.UTF-8 | superuser
 lc_monetary | en_US.UTF-8 | user
 lc_numeric  | en_US.UTF-8 | user
 lc_time     | en_US.UTF-8 | user
(6 rows)

lc_ctypeはDB作成後に変換できない。
Heroku PostgresでCREATE DATABASEすればいいかもしれないが、時間がかかるし、できるかどうかも不明。というかできない気がする。¥lでデータベース一覧を見てみたらね……そういう手動で作ったものが皆無というか……
PGroonga使えたらよかったんだけど、ここのブログみた感じまだ導入されてないっぽい。かといって先のブログで解説されているようにアプリケーション側に組み込む方法は少々敷居が高い。

＼(^o^)／

…………まあ、ダメ元でやってみるか。もしかしたら何かドキュメントの見落としがあるかもだし。

pg_trgm導入

まずは拡張機能を有効にする。

create extension pg_trgm;

続いてインデックスを作成する。
コマンドは公式ドキュメントからそのまま

CREATE INDEX trgm_idx_table_column ON table USING GIN (column gin_trgm_ops);

インデックスはGINとGISTが選べる。
GINインデックスの方がより好ましいテキスト検索インデックス形式らしいのでそちらに。

CREATE INDEX trgm_idx_narou_novels_title ON narou_novels USING GIN (title gin_trgm_ops);
CREATE INDEX trgm_idx_narou_novels_writer ON narou_novels USING GIN (writer gin_trgm_ops);
CREATE INDEX trgm_idx_narou_novels_story ON narou_novels USING GIN (story gin_trgm_ops);
CREATE INDEX trgm_idx_narou_novels_keyword ON narou_novels USING GIN (keyword gin_trgm_ops);

すでに数万行データがあると数分から数十分くらい適用に時間がかかるので、待つ。
適用後、実行計画を確認。

EXPLAIN ANALYZE SELECT
    *
FROM
    narou_novels
WHERE
    (
        narou_novels.title ILIKE '%男主人公%'
    OR  narou_novels.writer ILIKE '%男主人公%'
    OR  narou_novels.story ILIKE '%男主人公%'
    OR  narou_novels.keyword ILIKE '%男主人公%'
    )
ORDER BY
    narou_novels.daily_point DESC
LIMIT 20 OFFSET 0;

                                                                                              QUERY PLAN                                                                                               
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=36336.90..36336.91 rows=20 width=888) (actual time=725.328..725.336 rows=20 loops=1)
   ->  Sort  (cost=36336.90..36355.19 rows=36581 width=888) (actual time=725.326..725.332 rows=20 loops=1)
         Sort Key: daily_point DESC
         Sort Method: top-N heapsort  Memory: 63kB
         ->  Bitmap Heap Scan on narou_novels  (cost=2206.20..36142.22 rows=36581 width=888) (actual time=20.177..659.437 rows=35870 loops=1)
               Recheck Cond: (((title)::text ~~* '%男主人公%'::text) OR ((writer)::text ~~* '%男主人公%'::text) OR ((story)::text ~~* '%男主人公%'::text) OR ((keyword)::text ~~* '%男主人公%'::text))
               Rows Removed by Index Recheck: 1
               Heap Blocks: exact=21131
               ->  BitmapOr  (cost=2206.20..2206.20 rows=36592 width=0) (actual time=16.273..16.276 rows=0 loops=1)
                     ->  Bitmap Index Scan on trgm_idx_narou_novels_title  (cost=0.00..314.04 rows=24 width=0) (actual time=2.123..2.123 rows=2 loops=1)
                           Index Cond: ((title)::text ~~* '%男主人公%'::text)
                     ->  Bitmap Index Scan on trgm_idx_narou_novels_writer  (cost=0.00..80.03 rows=23 width=0) (actual time=0.722..0.722 rows=0 loops=1)
                           Index Cond: ((writer)::text ~~* '%男主人公%'::text)
                     ->  Bitmap Index Scan on trgm_idx_narou_novels_story  (cost=0.00..1010.04 rows=24 width=0) (actual time=2.414..2.415 rows=101 loops=1)
                           Index Cond: ((story)::text ~~* '%男主人公%'::text)
                     ->  Bitmap Index Scan on trgm_idx_narou_novels_keyword  (cost=0.00..794.78 rows=36522 width=0) (actual time=11.008..11.008 rows=36223 loops=1)
                           Index Cond: ((keyword)::text ~~* '%男主人公%'::text)
 Planning Time: 3.047 ms
 Execution Time: 725.695 ms

!?

Index Scanが機能しているっぽい結果になった。
というか実際に検索結果が早くなった！
あ、これ行けたかもしれない。

結論

Heroku PostgreSQLのlc_ctypeがen_US.UTF-8でもpg_trgm導入で日本語全文検索をインデックススキャンすることができました。この情報がどこを探してもなかったので備忘録として記載します。