PostgreSQLのプランニング 高速化の取り組み 2024年2月22日 第45回 PostgreSQLアンカンファレンス@オンライン NTT オープンソースソフトウェアセンタ 渡 佑也

1 Copyright NTT CORPORATION 自己紹介 名前 ◼ 渡 佑也（わたり ゆうや） 所属 ◼ 日本電信電話株式会社（NTT）オープンソースソフトウェアセンタ ソフトウェア技術サポートプロジェクト 基盤技術グループ 業務内容 ◼ PostgreSQLのコミュニティ開発 ◼ クラウドネイティブデータベース開発

2 Copyright NTT CORPORATION 今日のキーワード — パーティショニング テーブルを子パーティションに分割する機能 ◼ 使い方 親テーブル 子パーティション 子パーティション ◼ テーブルを複数の子パーティションに分割 ◼ ユーザからは一つのテーブルとして扱える -- パーティションドテーブルの宣言 CREATE TABLE students(id INT PRIMARY KEY, name TEXT) PARTITION BY RANGE (id); -- 子パーティションの作成 CREATE TABLE students_1 PARTITION OF students FOR VALUES FROM (0) TO (100); CREATE TABLE students_2 PARTITION OF students FOR VALUES FROM (100) TO (200);

3 Copyright NTT CORPORATION パーティション化されたテーブルのプランニングを10倍高速化した話 ◼ プランナの内部構造についての説明を少し含みます 親テーブル 子パーティ ション 子パーティ ション 子パーティ ション 今日話すこと 親テーブル 子パーティ ション 子パーティ ション 子パーティ ション 多数の子パーティションが存在 ⋈ 結合演算（ジョイン） プランニングが遅い！

4 Copyright NTT CORPORATION 実際のクエリで確認 3つのテーブルを結合するだけのシンプルなクエリ SELECT students.name, gpas.gpa AS gpa, sum(scores.score) AS total_score FROM students, scores, gpas WHERE students.id = scores.student_id AND students.id = gpas.student_id GROUP BY students.id, gpas.student_id; students scores gpas ⋈ ⋈ 各テーブルはパーティショニング されている ※図示した結合順序は仮のもの

5 Copyright NTT CORPORATION プランニング時間 0 0.2 0.4 0.6 0.8 1 1.2 1.4 1.6 0 200 400 600 800 1000 プランニング時間（秒） 各テーブルの子パーティション数 ◼ パーティション数に応じて プランニング時間が急激に増加 ◼ 1024パーティションあると プランニングだけで1.5秒 （実際の値は環境による） ※PostgreSQL 17devel（最新の開発版）で評価

6 Copyright NTT CORPORATION なぜこんなに遅いのか プロファイラを使用してボトルネックを調査 ◼ perf ◼ Linuxで使用可能な性能解析ツール ◼ プロファイルを行ってボトルネック箇所の特定などに使用できる

7 Copyright NTT CORPORATION プロファイル方法 PostgreSQLのバックエンドプロセスをプロファイルする例 1. psqlコマンドを起動させるなどしてコネクションを確立させておく 2. バックエンドプロセスのプロセスIDを確認 3. perfコマンドでプロファイル $ ps x PID TTY STAT TIME COMMAND … 1368 ? Ss 0:00 postgres: ubuntu postgres [local] idle $ psql psql (17devel) Type "help" for help. postgres=# $ sudo perf record -ag -p 1368

8 Copyright NTT CORPORATION プロファイル方法（続き） 4. プロファイル結果を確認 $ sudo perf report Samples: 7K of event 'cpu-clock:pppH', Event count (approx.): 1966500000 Children Self Command Shared Object Symbol + 96.07% 0.00% postgres libc-2.31.so [.] __libc_start_main … + 93.69% 0.00% postgres postgres [.] planner + 93.69% 0.00% postgres postgres [.] standard_planner + 90.76% 0.00% postgres postgres [.] subquery_planner + 90.76% 0.00% postgres postgres [.] grouping_planner … + 32.09% 0.01% postgres postgres [.] generate_join_implied_equalities + 19.27% 0.05% postgres postgres [.] build_index_pathkeys + 19.18% 19.18% postgres postgres [.] bms_is_subset + 19.09% 0.01% postgres postgres [.] make_pathkey_from_sortinfo + 18.50% 17.34% postgres postgres [.] create_join_clause + 16.23% 16.23% postgres postgres [.] bms_equal + 14.80% 0.01% postgres postgres [.] match_eclass_clauses_to_index + 14.77% 4.26% postgres postgres [.] generate_implied_equalities_for_column + 14.52% 6.11% postgres postgres [.] generate_join_implied_equalities_normal … どの関数がボトルネックに なっているかなどを確認できる

9 Copyright NTT CORPORATION コンソールの画面では見づらいので可視化 フレームグラフとして可視化できる 冒頭のクエリをプランニングした場合のフレームグラフ 関数の呼び出し階層と サンプル数が可視化 される

10 Copyright NTT CORPORATION コンソールの画面では見づらいので可視化 フレームグラフとして可視化できる 冒頭のクエリをプランニングした場合のフレームグラフ 関数の呼び出し階層と サンプル数が可視化 される ボトルネックになって いる関数が判明 プランニングが遅い 直接的な原因

11 Copyright NTT CORPORATION printfデバッグ ボトルネックになっている関数が頻繁に呼び出されている本当の原因を 突き止めるためにprintfデバッグ ◼ 関数の前後にログを挟み込んでどの箇所がどんな引数で呼び出されて いるかなどを詳細に調査 ◼ PostgreSQLでのprintfデバッグにはelog・ereportが便利 ◼ PostgreSQLのソースコードを例えば次のように改変してコンパイル ◼ ログレベルは、LOG・NOTICE・DEBUGなどが選べる ◼ 詳細はPostgreSQLのドキュメントにも記載されている ◼ https://www.postgresql.jp/document/15/html/error-message- reporting.html elog(LOG, "%d", count);

12 Copyright NTT CORPORATION プランニングが遅い本当の原因 EquivalenceClass内のメンバ探索が非常に遅いこと ◼ EquivalenceClass：結合演算の等価性を管理する機構 ◼ 典型的な3つのテーブル間の結合演算は次のようなSQLになる ◼ 結合の順番は（内部表と外部表の入れ替えを無視して）3通り SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id t1 t2 t3 ⋈ ⋈ t1.id = t2.id t1.id = t3.id t1 t3 t2 ⋈ ⋈ t1.id = t3.id t1.id = t2.id t2 t3 t1 ⋈ ⋈ t2.id = t3.id t1.id = t2.id

13 Copyright NTT CORPORATION プランニングが遅い本当の原因 EquivalenceClass内のメンバ探索が非常に遅いこと ◼ EquivalenceClass：結合演算の等価性を管理する機構 ◼ 典型的な3つのテーブル間の結合演算は次のようなSQLになる ◼ 結合の順番は（内部表と外部表の入れ替えを無視して）3通り SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.id = t3.id t1 t2 t3 ⋈ ⋈ t1.id = t2.id t1.id = t3.id t1 t3 t2 ⋈ ⋈ t1.id = t3.id t1.id = t2.id t2 t3 t1 ⋈ ⋈ t2.id = t3.id t1.id = t2.id SQLに出現しない ため自明でない

14 Copyright NTT CORPORATION EquivalenceClass 「t1.idとt2.idが等しくt1.idとt3.idが等しいのでt2.idとt3.idも等しい」 という知見を導出する EquivalenceClass t1.id t2.id t3.id t1.id = t2.id t1.id = t3.id ◼ 等価な値を持つものを集合として保持 ◼ t2.idとt3.idが等しいことが分かり 結合演算のプランニングに活用できる

15 Copyright NTT CORPORATION 子パーティションが多いとき 子パーティション同士の結合のため子メンバが必要 子メンバの数が多くなった結果探索に非常に長い時間を要する EquivalenceClass t1.id t2.id t3.id t1_child1.id t2_child1.id t3_child1.id t1_child2.id t2_child2.id t3_child2.id t1_childn.id t2_childn.id t3_childn.id ※イメージ図 子メンバが必要

16 Copyright NTT CORPORATION PostgreSQL本体のソースコードを改良 子パーティションのメンバを保持しない最適化を導入（詳細は割愛） ◼ 最適化を導入してEquivalenceClassの探索が高速化される様子 EquivalenceClass t1.id t2.id t3.id t1_child1.id t2_child1.id t3_child1.id t1_child2.id t2_child2.id t3_child2.id t1_childn.id t2_childn.id t3_childn.id 子メンバを 保持しない 必要に応じて 導出 ※イメージ図。今後変更の可能性あり

17 Copyright NTT CORPORATION 高速化の結果 0 0.2 0.4 0.6 0.8 1 1.2 1.4 1.6 0 200 400 600 800 1000 プランニング時間（秒） 各テーブルの子パーティション数 改良前 改良後 10倍高速化 ◼ 1.5秒かかっていたものが 0.15秒まで削減 ◼ 100パーティションでも 1.3倍高速化

18 Copyright NTT CORPORATION どんなワークロードで恩恵が得られる？ 次の条件を満たすようなクエリを扱う場合 ◼ 大量の子パーティションがあるテーブルに対する結合演算のクエリ ◼ ほとんどの子パーティションが結合演算に関与するクエリ （＝プルーニングが効きづらいクエリ） 広範なワーロードで効果が期待される

19 Copyright NTT CORPORATION PostgreSQLコミュニティにパッチを投稿 国外の方の協力も得て議論しながら 改良中 ◼ 現在は議論の過程でいただいた 様々なアイデアをもとに ブラッシュアップの最中

20 Copyright NTT CORPORATION 今後のロードマップ 遅くともPostgreSQL 18（願わくば17）で本体に取り込まれてほしい ◼ コミットフェストにも登録して鋭意作業中 ◼ パッチは次の場所から参照できます ◼ https://commitfest.postgresql.org/47/3701/ ◼ https://www.postgresql.org/message-id/flat/CAJ2pMkZNCgoUKSE+_5 [email protected] ◼ 5月下旬にカナダ・バンクーバーで 開催されるPGConf.dev 2024で 本取り組みを発表予定

21 Copyright NTT CORPORATION この問題に取り組むにあたり心掛けたこと コミュニティで関心を持ってもらうには丁寧さがとても大事 ◼ 今のPostgreSQLが抱える問題を丁寧に説明すること ◼ 問題を簡単に再現できる手順を提供すること ◼ きちんと動くパッチ（PoC）を添付して自分の高速化も簡単に再現で きるようにすること ◼ 精緻な実験結果を添えて自分のアイデアの正当性を主張すること ◼ 実験は100回以上は行った （正確には数えていないがおそらくもっとたくさん）

22 Copyright NTT CORPORATION まとめ ◼ 子パーティションが多数存在するテーブルに対する結合演算は プランニングに非常に長い時間がかかる ◼ 結合条件の等価性を管理する機構に問題が存在 ◼ 問題を解決した結果10倍以上の高速化を実現 ◼ パッチをPostgreSQLコミュニティに投稿して議論中 ご清聴いただきありがとうございました