TECH BLOG
技術ブログ

ARTICLE

  • 2022-03-18 PostgreSQLPostgreSQL チューニング全文検索

    pg_bigm の全文検索 SQL をパーティションテーブルを使って高速化

パーティションテーブルを使った 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 文で子テーブルを作ります。

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

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

パーティション数 Planning Time (ms) Execution Time (ms) Total Time (ms)
1 2.38 11041.31 11043.70
2 4.59 10933.36 10938.00
3 5.45 11081.35 11086.80
4 6.60 11433.39 11440.00
5 6.20 11110.80 11117.00
10 10.21 11493.04 11503.20
20 12.58 11760.07 11772.60
30 16.65 11125.13 11141.80
40 18.66 11256.62 11275.30
50 26.87 11053.23 11080.10
100 53.05 11383.38 11436.40
200 76.51 11390.90 11467.40
300 90.92 11477.12 11568.00
400 106.56 10954.70 11061.30
500 116.61 12008.94 12125.50
600 141.75 16079.02 16220.80
700 119.53 4527.58 4647.12
800 124.69 5540.63 5665.32
900 169.12 5840.16 6009.28
1000 143.82 5959.40 6103.23
1100 158.44 5539.23 5697.68
1200 155.51 5549.44 5704.96
1300 173.47 5569.95 5743.42
1400 217.72 5566.98 5784.70
1500 212.32 5608.34 5820.67
2000 240.73 5645.83 5886.56
2500 288.67 5385.60 5674.27
3000 335.34 5464.96 5800.31
3500 351.22 5507.79 5859.01
4000 416.57 5596.31 6012.88
4500 497.65 5382.46 5880.11
5000 482.42 5476.94 5959.36
10000 953.24 5956.13 6909.37

今回の検証例では、パーティション数を 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 の高速化についてお伝えしたいと思います。

CATEGORY

ARCHIVE

PostgreSQLの
ハイパフォーマンスチューニングのご相談は
株式会社インサイトまで
お気軽にお問い合わせください。

CONTACT