PostgreSQL11でのパーティショニング機能の進化（の細かいところ）

はじめに

9/20 PostgreSQL11 beta4がリリースされましたね。
(いよいよ正式版リリースが近づいてきました！)

今回はPG11のメジャー機能でもあるパーティショニングの性能改善（の一部）について書きます。
※調査結果はbeta3のときのものなので、少し結果が変わっているかもしれないのでご注意ください。

PostgreSQL11のメジャー機能

簡単な紹介になりますが、PG11の新機能には大きく以下のような項目があります。

• パーティショニングの改善
  • ハッシュパーティショニング実装、パーティションキーの更新対応、Primary keyのサポート　等
  • 参照性能の向上
• パラレルクエリの強化
  • ハッシュJOIN、UNION、CREATE INDEX/TABLE/MATERIALIZED VIEW　等
• SQLストアドプロシージャ
  • トランザクション制御　等
• JIT（Just-In-Time）コンパイルの実装

詳細はリリースノートをご確認ください。

今回は「パーティショニングの改善」についてご紹介しようと思います。

おさらい：パーティショニングとは

大規模なテーブルを小さくて管理しやすいテーブルに分割して格納することを指します。
PostgreSQL10ではSQLでパーティショニングを利用できる「宣言的パーティション」が実装されたことで話題になりました。

• 主なメリットとして、検索性能の向上があります。
• WHERE句に指定したパーティションキーにより、アクセス対象のパーティションが絞り込むことでディスクIOを抑える
  • これをパーティションプルーニングという

雑ですが、パーティショニングのイメージはこんな感じです。

本題

今回はPG11でのPostgreSQLのパーティショニング機能の性能改善について紹介します。

改善1：SQLを実行するタイミングでもプルーニングが行われるようになった

この改善により、WHERE区でサブクエリを使用している場合や、PREPARED STATEMENTでもプルーニングが可能になりました。
以下、サブクエリを使用した場合のPG10とPG11での比較結果です。

// PG10.4での実行計画
postgres=# EXPLAIN ANALYZE SELECT * FROM parent WHERE part_key = (SELECT id FROM foo WHERE id = 29);
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Append  (cost=41.88..4386.89 rows=19800 width=48) (actual time=4.871..16.009 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foo  (cost=0.00..41.88 rows=13 width=4) (actual time=0.002..0.003 rows=1 loops=1)
           Filter: (id = 29)
           Rows Removed by Filter: 1
・・・
   ->  Seq Scan on range10  (cost=0.00..437.00 rows=2000 width=48) (actual time=1.512..1.512 rows=0 loops=1)
         Filter: (part_key = $0)
         Rows Removed by Filter: 20000
 Planning time: 0.819 ms
 Execution time: 16.044 ms
(37 rows)

// PG11 beta3での実行計画
postgres=# EXPLAIN ANALYZE SELECT * FROM parent WHERE part_key = (SELECT id FROM foo WHERE id = 29);
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Append  (cost=41.88..4485.89 rows=19800 width=48) (actual time=1.540..1.541 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foo  (cost=0.00..41.88 rows=13 width=4) (actual time=0.007..0.007 rows=1 loops=1)
           Filter: (id = 29)
           Rows Removed by Filter: 1
   ->  Seq Scan on range1  (cost=0.00..437.00 rows=2000 width=48) (never executed)
         Filter: (part_key = $0)
・・・
 ->  Seq Scan on range10  (cost=0.00..437.00 rows=2000 width=48) (never executed)
         Filter: (part_key = $0)
 Planning Time: 0.862 ms
 Execution Time: 1.589 ms
(28 rows)

ver.	処理時間
PG10.4	Execution time: 16.044 ms
PG11 b3	Execution Time: 1.589 ms

PG10ではサブクエリではプルーニングが効かなかったので、全ての子テーブルを検索が走っていましたが、PG11では必要な子テーブルのみ検索されるので、大幅な時間短縮が見れらます。

改善2：プルーニングのアルゴリズム変更

• Faster partition pruning等の修正が入ったことで、新たに「boundinfo」を用いたプルーニングが行われるようになりました。
  • これまではパーティション数に比例して性能が悪化する仕様になっていましたが、このパッチにより性能悪化は抑えられるようになりました。

※パッチは一部であり、他にも多くの修正が入っています。

Faster partition pruningが入ったことによる変化

• ～PG10
  • 全てのパーティションテーブルをLockし、それぞれのメタデータにアクセスしている
  • WHERE句の条件と合うか1つずつ確認を行い、SCAN対象を絞る
• PG11（Faster partition pruningの修正等が入ったことにより）
  • 親テーブルのRelOptInfo内でboundinfoが管理されるようになり、その情報とbinary searchを利用したプルーニングが行われる
  • boundinfoには、partitionの境界情報が管理されており、どのテーブルにどこまでの値が格納されているか判断することができる

実測

プルーニングアルゴリズムの変化が確認できるか、パーティション数を変化させて実行計画の作成時間が変化するか確認してみました。

• 測定条件
  • PG10.4、PG11 beta3
  • RANGEパーティション
    • パーティション数は10、50、100で比較
    • 各子テーブルのレコード数は同じになるように調整
  • 検索対象は1レコードだけになるようにINSERTしておく

3回の平均値を結果としてまとめたのが下図になります。
パーティション数が増えた場合の実行計画の作成時間が短くなっていることを確認できます。

ver.	partition	処理時間
PG10.4	10	Planning time: 0.181 ms
PG11 b3	10	Planning Time: 0.160 ms
PG10.4	50	Planning time: 0.572 ms
PG11 b3	50	Planning Time: 0.422 ms
PG10.4	100	Planning time: 1.338 ms
PG11 b3	100	Planning Time: 0.979 ms

余談ですが、PG12では子テーブルのLOCKもなくなるということで更に性能が向上することが見込まれます。
目安ですが以下のような違いが出てくるはず。今回試した内容もPG10、PG12で比較するともっと顕著な差が出ると思います。

おわりに

PostgreSQLの機能追加は、次の大きな機能追加の布石となっているものも多く、細々したものを単体で見てもあまり意味がないことはあります。今回見たものもパーティショニング機能の中の改善の一部でしかありません。なんなら、実際にはもっと改善のための修正は入っているが有効化されていないものあるそうです。

今後も少しずつでも、PostgreSQLの改善内容をキャッチアップできればと思います。

おまけ：パーティショニング機能のその他の改善

大きな機能追加として、以下のようなものがあります。パーティショニング機能の改善は目覚ましいものがありますね。

• ハッシュパーティショニングの実装
  

• パーティショニングキーを更新に対応
  

• パーティショニングキーにPrimaryKeyを含めることが可能になる