TECH BLOG
技術ブログ

パーティションテーブルを使った pg_bigm の高速化

本記事では、PostgreSQL の拡張モジュールである pg_bigm を利用した全文検索 SQL の高速化について、パーティションテーブルを使った方法をご紹介いたします。
pg_bigm については公式サイトのドキュメント（ https://pgbigm.osdn.jp/pg_bigm-1-2.html ）や、インサイト技術ブログの過去記事（ PostgreSQL で全文検索 Pgroonga と pg_bigm の性能比較 : https://www.insight-ltd.co.jp/tech_blog/db-tuning/286/ ）をご参照ください。

PostgreSQL のバージョンは 14.1 です。

データの用意

検証用のデータとして Wikipedia からデータをダウンロードし、各ページのタイトルとテキストをテーブルに格納して速度検証を行います。
データはこちら（↓）からダウンロードしました。
https://dumps.wikimedia.org/jawiki/

テーブルおよびインデックスの作成

postgres=# CREATE TABLE wikipedia (title TEXT, text TEXT);
CREATE TABLE

・（作成してから text カラムの名前が分かりにくいと思いましたが）データ格納用のテーブルが作成されました。

postgres=# SELECT COUNT(1) FROM wikipedia;
 count  
--------
 500000
(1 row)

postgres=# \dt+ wikipedia
                                      List of relations
 Schema |   Name    | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+-----------+-------+----------+-------------+---------------+---------+-------------
 public | wikipedia | table | postgres | permanent   | heap          | 4029 MB | 
(1 row)

・データの追加についての記述は省略していますが、10 万件のデータ追加を 5 回繰り返して、合計 50 万件分のデータを追加しました。
（10 万件ではデータが足りなかったため、5 回繰り返して水増ししています）
・データサイズはおおよそ 4 GB となりました。

postgres=# CREATE INDEX idx_wikipedia_01 ON wikipedia USING gin (text gin_bigm_ops);
CREATE INDEX

postgres=# \di+ idx_wikipedia_01;
                                               List of relations
 Schema |       Name       | Type  |  Owner   |   Table   | Persistence | Access method |  Size   | Description 
--------+------------------+-------+----------+-----------+-------------+---------------+---------+-------------
 public | idx_wikipedia_01 | index | postgres | wikipedia | permanent   | gin           | 2664 MB | 
(1 row)

・インデックスのサイズはおおよそ 2.6 GB となりました。
・インデックス作成には 33 分ほどかかりました。

速度検証

これから実際に速度を検証していきます。
検証前には忘れずに VACUUM ANALYZE しておきます。

非パーティションテーブルでの検索速度確認

postgres=# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM wikipedia WHERE text LIKE '%テスト%';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on wikipedia  (cost=44.39..236.76 rows=50 width=238) (actual time=21.230..11003.693 rows=21227 loops=1)
   Recheck Cond: (text ~~ '%テスト%'::text)
   Rows Removed by Index Recheck: 14270
   Heap Blocks: exact=12695
   Buffers: shared hit=264586
   ->  Bitmap Index Scan on idx_wikipedia_01  (cost=0.00..44.37 rows=50 width=0) (actual time=18.327..18.330 rows=35497 loops=1)
         Index Cond: (text ~~ '%テスト%'::text)
         Buffers: shared hit=75
 Planning:
   Buffers: shared hit=1
 Planning Time: 0.506 ms
 Execution Time: 11005.284 ms
(12 rows)

・11 秒前後で検索結果を取得できました。
・実行計画の一行目の「actual time=21.230..11003.693」から、処理時間のほとんどが recheck 処理に費やされていることが分かります。

recheck 処理について

上記の結果から分かる通り、pg_bigm の全文検索が遅い場合、recheck 処理に原因にあることが多いです。
検索対象のテキストが長い、または検索対象のレコードが多いと recheck 処理に時間がかかるようになります。
公式サイトの「pg_bigm.enable_recheck」の項目に、recheck 処理についての説明がありますので、ご参照ください。
https://pgbigm.osdn.jp/pg_bigm-1-2.html#enable_recheck

ハッシュパーティションテーブルでの検索速度確認

次はパーティションテーブルに対して pg_bigm の全文検索を行い、実行速度を確認します。

親テーブルの作成

postgres=# CREATE TABLE wikipedia_hash_p  (title TEXT, text TEXT) PARTITION BY HASH (text);
CREATE TABLE 

postgres=# INSERT INTO wikipedia_hash_p SELECT * FROM wikipedia;
INSERT 0 500000

postgres=# CREATE INDEX idx_wikipedia_hash_p_01 ON wikipedia_hash_p USING gin (text gin_bigm_ops);
CREATE INDEX

・親テーブルが無事に作成されました。

子テーブルの作成

postgres=# CREATE TABLE wikipedia_hash_c_0000 PARTITION OF wikipedia_hash_p FOR VALUES WITH (MODULUS 1000, REMAINDER 0;
CREATE TABLE

postgres=# CREATE TABLE wikipedia_hash_c_0001 PARTITION OF wikipedia_hash_p FOR VALUES WITH (MODULUS 1000, REMAINDER 1;
CREATE TABLE
:
:

・上記のような CREATE TABLE 文で子テーブルを作ります。

パーティション数と検索実行時間

パーティション数を変動させて、検索実行時間が高速化されるかを確認しました。

今回の検証例では、パーティション数を 700 以上にすると検索時間を高速化されることが分かりました。
非パーティションやパーティション数が小さい場合と比較して、おおよそ半分程度の実行時間となっています（それでも 5, 6 秒ほどかかってしまうのですが・・）。
もちろん、テーブルのレコード件数やテキストの長さなどによって最速となるパーティション数は変わってくると思いますので、環境ごとに最速なパーティション数を検証する必要があります。
また、パーティション数を増やせば増やすほど Planning Time が増えていきますので、「pg_bigm の全文検索は速くなったけど、それ以外の SQL が遅くなった」ということにならないように注意が必要です。

（補足）検証中に発生したエラー

今回の検証中に、下記のエラーが発生しました。

ERROR: out of shared memory
HINT: You might need to increase max_locks_per_transaction.

out of shared memory ということでメモリ枯渇が発生したように見えて焦りましたが、設定値「max_locks_per_transaction」の値が小さいためのエラーでした。
具体的には、パーティション数が「max_locks_per_transaction」を超えると上記のエラーが発生します。
postgresql.conf を修正し、max_locks_per_transaction の値をパーティション数よりも大きい値にすることで、エラーが解消されました。

おわりに

今回はパーティションテーブルを利用することで pg_bigm の全文検索 SQL が 11 秒から 6 秒前後まで短縮できました。
検索時間が半分程度にはなりましたが、6 秒ではまだまだ遅い部類といえます。
そのため、次回以降も引き続き pg_bigm の高速化についてお伝えしたいと思います。