SQL性能改善の第一歩 実行計画を可視化するpev2のご紹介

Transcript

1. 2025.12.16 2025年を総括しよう！       今年の開発⽣産性を振り返る⼤忘年会 SQL性能改善の第一歩 
 実行計画を可視化する pev2 のご紹介 
 星原 宏紀
2. @2025 WealthNavi Inc. 星原 宏紀 (Koki Hoshihara)
 ウェルスナビ株式会社 
 サービス基盤グループ　ソフトウェアエンジニアリング (SWE) チーム
 ⾃⼰紹介 2 ひとこと この半年間よく使っていた「pev2」を紹介できて嬉しいです！ よろしくお願いします！ ウェルスナビでは 共通ライブラリ開発(⼤規模バッチ / 認証系)、バックエンド開発、 新規システムのパフォーマンスチューニング、新技術導⼊ を推進
3. 3 アジェンダ はじめに pev2 の導⼊背景 実例: インデックス導⼊前後の実⾏計画を pev2 で確認する pev2 導⼊で得られた効果 まとめ
4. © WealthNavi Inc. All Rights Reserved. 1 4 はじめに 1 4
5. © WealthNavi Inc. All Rights Reserved. 5 以下の内容をお話しします。 • pev2 を⽤いるメリット • pev2 の導⼊⽅法 本セッションの焦点は、pev2 (読み:pebu) による実⾏計画確認コストの低減です。 SQLチューニングでは実⾏計画を読む負荷 (以降「認知負荷」) を下げることは重要です。 実⾏計画の認知負荷を下げるために、可視化ツールを検討した実践知を共有します。 はじめに 以下の内容には⾔及しません。 • EXPLAIN および実⾏計画に関する詳細な説明
6. © WealthNavi Inc. All Rights Reserved. 6 本セッションで検証した内容は以下の記事にも記載しています。 x ぜひお⼿元で試しながらお聞きください。 • https://zenn.dev/khoshihara/articles/7748ff2d34daaf はじめに
7. © WealthNavi Inc. All Rights Reserved. 7 留意事項 pev2 は、Postgres Explain Visualizer 2※1 の略称です。 そのため、本ツールの適⽤対象はPostgreSQLに限られますのでご注意ください。 本セッションに出てくる⽤語の定義 「ローカル環境」は、開発者のPC環境を指します。 ※1: https://github.com/dalibo/pev2
8. © WealthNavi Inc. All Rights Reserved. 1 8 pev2 の導⼊背景 8 2
9. © WealthNavi Inc. All Rights Reserved. 9 SQLの実⾏計画を読むことの⼤切さ ※2: https://speakerdeck.com/kokihoshihara/proposal-for-java-library-development-with-spring-boot?slide=20 SQLや作成したインデックスはDMLを⽤いて性能検証する必要がある • 担当した⼤規模バッチ開発※2の中で、インデックスの性能を検証する機会があった • 追加したインデックスが想定通り使⽤されるとともに、許容できる実⾏時間に収まるか 実⾏計画を⽤いて確認する必要があった
10. © WealthNavi Inc. All Rights Reserved. 10 SQLの実⾏計画を読むことの負荷 SQLや作成したインデックスはDMLを⽤いて性能検証する必要がある • チーム横断の定例などで結果をレビューする機会があると仮定したとき 定例内で実⾏計画を共有すると、レビュワーがその場で読み解く負担が⼤きい ◦ 実⾏計画を可視化することで、本来議論すべき内容に割く時間を確保する必要がある 実⾏計画をレビューするコストは⾼い ※2: https://speakerdeck.com/kokihoshihara/proposal-for-java-library-development-with-spring-boot?slide=20 • 担当した⼤規模バッチ開発※2の中で、インデックスの性能を検証する機会があった • 追加したインデックスが想定通り使⽤されるとともに、許容できる実⾏時間に収まるか 実⾏計画を⽤いて確認する必要があった
11. © WealthNavi Inc. All Rights Reserved. 11 pev2 導⼊のモチベーション • 可視化することで実⾏計画を読み解く負担を軽減できる ◦ 次の章で実際に確認します 取得対象レコード量やSQLコストを可視化できる • pev2 のGitHubリポジトリからHTMLファイルをダウンロードすることで インターネット接続しない状態で検証および検証結果を保存可能 ◦ 内部的にはIndexedDB ※3(ブラウザ内のクライアントサイドデータベース) を使⽤している ローカル環境に閉じて実⾏計画を検証できることに加え、結果も保存可能 ※3: https://developer.mozilla.org/ja/docs/Web/API/IndexedDB_API
12. © WealthNavi Inc. All Rights Reserved. 12 pev2 の実⾏イメージをキャプチャ付きで紹介 (1/2)
13. © WealthNavi Inc. All Rights Reserved. 13 pev2 の実⾏イメージをキャプチャ付きで紹介 (2/2)
14. © WealthNavi Inc. All Rights Reserved. 1 実例: インデックス導⼊前後の実⾏計画を pev2 で確認する 3 14
15. © WealthNavi Inc. All Rights Reserved. 15 サンプル検証のソフトウェアスタック 項目名 概要説明 PC • M3 MacBook Pro • メモリ：32GB 実行計画 可視化ツール • pev2 データベース • Dockerコンテナ(PostgreSQL 17.7)を使⽤ 以下のソフトウェアスタックで検証を実施
16. © WealthNavi Inc. All Rights Reserved. 16 書籍管理システムの⽉次バッチを想定した検証シナリオを作成 • ⽬的 ◦ 前⽉に貸出回数が多かった書籍 TOP10 を集計 • 利⽤シーン ◦ 仕⼊れ‧増刷の判断材料 などを想定 • 検証条件 (データ量) ◦ 会員数：1万⼈ ◦ ⽉間貸出件数：約4万件 (＝5,000⼈ × 2冊 × 4週) ◦ 貸出履歴は2年分保持する ▪ 貸出履歴は約100万レコードとする 前⽉最もレンタルされた書籍を集計するバッチとして以下のシナリオを作成※4 ※4: https://zenn.dev/khoshihara/articles/7748ff2d34daaf
17. © WealthNavi Inc. All Rights Reserved. 17 検証シナリオのデータ量を整理 以下のデータ量およびデータのライフサイクルを前提とする テーブル名 テーブル種別 概要説明 書籍 (book) マスタ • データ量：1000レコード • ライフサイクル：なし 会員 (member) トランザクション • データ量：1万レコード • ライフサイクル：なし 貸出履歴 (rental) トランザクション • データ量：100万レコード • ライフサイクル：2年
18. © WealthNavi Inc. All Rights Reserved. 18 インデックス付与前のテーブル定義
19. © WealthNavi Inc. All Rights Reserved. 19 インデックス付与後のテーブル定義
20. © WealthNavi Inc. All Rights Reserved. 20 SQLクエリ JOINとWHERE句で使⽤する rentalテーブルのカラムに対 してインデックスを作成
21. © WealthNavi Inc. All Rights Reserved. 21 SQLクエリ‧実⾏計画を pev2 に貼り付け Submit し可視化
22. © WealthNavi Inc. All Rights Reserved. 22 インデックス導⼊前の EXPLAIN 結果 rental テーブルからの取得処理に Parallel Seq Scan が選択され、コストも⾼い状態
23. © WealthNavi Inc. All Rights Reserved. 23 インデックス導⼊後の EXPLAIN 結果 rental テーブルからの取得処理にIndex Only Scanが選択され、コストも下がった
24. © WealthNavi Inc. All Rights Reserved. 1 pev2 導⼊の効果 4 24
25. © WealthNavi Inc. All Rights Reserved. 25 pev2 導⼊によって具体的に得られた効果 • 実⾏計画を読み解いた経験が少ない若⼿エンジニア ◦ インデックスの作成⽅針や検証⽅法を伝え EXPLAIN 結果を pev2 で可視化することで 実⾏計画を直感的に理解しやすくなった • 性能改善に熟達したテックリード ◦ pev2 で可視化した実⾏計画を共有することでレビューを効率的に進めることができた 実際に導⼊した結果、様々な層のエンジニアに対してメリットがあった
26. © WealthNavi Inc. All Rights Reserved. 1 まとめ 5 26
27. © WealthNavi Inc. All Rights Reserved. 27 まとめ 1. pev2 の概要 2. pev2 導⼊の背景 (モチベーション) 3. パフォーマンス改善前後の実⾏計画を pev2 で⽐較‧確認する⽅法 4. 実⾏⾏計画を読み解く負担を軽減できること 本セッションでは、各章で以下の内容をお話してきました。 PostgreSQLのパフォーマンスチューニングでは、ぜひ pev2 を使ってみてください！
28. © WealthNavi Inc. All Rights Reserved. • 本資料は、断定的判断を提供するものではなく、情報を提供することのみを目的としており、いかなる種類の商品も勧誘 するものではありません。最終的な決定は、お客様自身で判断するものとし、当社はこれに一切関与せず、また、一切の 責任を負いません。 • 本資料には将来の出来事に関する予想が含まれている場合がありますが、それらは予想であり、また、本資料の内容の 正確性、信頼性、完全性、適時性等を一切保証するものではありません。本資料に基づいて被ったいかなる損害について も、当社は一切の責任を負いません。また、当社は、新しい情報や将来の出来事その他の情報について、更新又は訂正 する義務を負いません。 • 本資料を利用することによりお客様に生じた直接的損害、間接的損害、派生的損害その他いかなる損害についても、当社 は一切の責任を負いません。 商号等：ウェルスナビ株式会社 金融商品取引業者　関東財務局長（金商） 第2884号　加入協会：日本証券業協会　一般社団法人日本投資顧問業協会 重要な注意事項 28