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

SQL性能改善の第一歩 実行計画を可視化する pev2 のご紹介 2025.12.16 星原 宏紀 2025年を総括しよう！ 今年の開発⽣産性を振り返る⼤忘年会

⾃⼰紹介 星原 宏紀 (Koki Hoshihara) ウェルスナビ株式会社 サービス基盤グループ ソフトウェアエンジニアリング (SWE) チーム ウェルスナビでは 共通ライブラリ開発(⼤規模バッチ / 認証系)、バックエンド開発、 新規システムのパフォーマンスチューニング、新技術導⼊ を推進 ひとこと この半年間よく使っていた「pev2」を紹介できて嬉しいです！ よろしくお願いします！ 2 @2025 WealthNavi Inc.

アジェンダ はじめに pev2 の導⼊背景 実例: インデックス導⼊前後の実⾏計画を pev2 で確認する pev2 導⼊で得られた効果 まとめ 3

1 はじめに © WealthNavi Inc. All Rights Reserved. 44

はじめに 本セッションの焦点は、pev2 (読み:pebu) による実⾏計画確認コストの低減です。 SQLチューニングでは実⾏計画を読む負荷 (以降「認知負荷」) を下げることは重要です。 実⾏計画の認知負荷を下げるために、可視化ツールを検討した実践知を共有します。 以下の内容をお話しします。 ● pev2 を⽤いるメリット ● pev2 の導⼊⽅法 以下の内容には⾔及しません。 ● EXPLAIN および実⾏計画に関する詳細な説明 © WealthNavi Inc. All Rights Reserved. 5

はじめに 本セッションで検証した内容は以下の記事にも記載しています。 ぜひお⼿元で試しながらお聞きください。 x ● https://zenn.dev/khoshihara/articles/7748ff2d34daaf © WealthNavi Inc. All Rights Reserved. 6

留意事項 pev2 は、Postgres Explain Visualizer 2※1 の略称です。 そのため、本ツールの適⽤対象はPostgreSQLに限られますのでご注意ください。 本セッションに出てくる⽤語の定義 「ローカル環境」は、開発者のPC環境を指します。 © WealthNavi Inc. All Rights Reserved. ※1: https://github.com/dalibo/pev2 7

2 1 pev2 の導⼊背景 © WealthNavi Inc. All Rights Reserved. 88

SQLの実⾏計画を読むことの⼤切さ SQLや作成したインデックスはDMLを⽤いて性能検証する必要がある ● 担当した⼤規模バッチ開発※2の中で、インデックスの性能を検証する機会があった ● 追加したインデックスが想定通り使⽤されるとともに、許容できる実⾏時間に収まるか 実⾏計画を⽤いて確認する必要があった © WealthNavi Inc. All Rights Reserved. ※2: https://speakerdeck.com/kokihoshihara/proposal-for-java-library-development-with-spring-boot?slide=20 9

SQLの実⾏計画を読むことの負荷 SQLや作成したインデックスはDMLを⽤いて性能検証する必要がある ● 担当した⼤規模バッチ開発※2の中で、インデックスの性能を検証する機会があった ● 追加したインデックスが想定通り使⽤されるとともに、許容できる実⾏時間に収まるか 実⾏計画を⽤いて確認する必要があった 実⾏計画をレビューするコストは⾼い ● チーム横断の定例などで結果をレビューする機会があると仮定したとき 定例内で実⾏計画を共有すると、レビュワーがその場で読み解く負担が⼤きい ○ 実⾏計画を可視化することで、本来議論すべき内容に割く時間を確保する必要がある © WealthNavi Inc. All Rights Reserved. ※2: https://speakerdeck.com/kokihoshihara/proposal-for-java-library-development-with-spring-boot?slide=20 10

pev2 導⼊のモチベーション 取得対象レコード量やSQLコストを可視化できる ● 可視化することで実⾏計画を読み解く負担を軽減できる ○ 次の章で実際に確認します ローカル環境に閉じて実⾏計画を検証できることに加え、結果も保存可能 ● pev2 のGitHubリポジトリからHTMLファイルをダウンロードすることで インターネット接続しない状態で検証および検証結果を保存可能 ○ 内部的にはIndexedDB ※3(ブラウザ内のクライアントサイドデータベース) を使⽤している © WealthNavi Inc. All Rights Reserved. ※3: https://developer.mozilla.org/ja/docs/Web/API/IndexedDB_API 11

pev2 の実⾏イメージをキャプチャ付きで紹介 (1/2) © WealthNavi Inc. All Rights Reserved. 12

pev2 の実⾏イメージをキャプチャ付きで紹介 (2/2) © WealthNavi Inc. All Rights Reserved. 13

3 1 実例: インデックス導⼊前後の実⾏計画を pev2 で確認する © WealthNavi Inc. All Rights Reserved. 14

サンプル検証のソフトウェアスタック 以下のソフトウェアスタックで検証を実施 項目名 概要説明 ● M3 MacBook Pro ● メモリ：32GB 実行計画 可視化ツール ● pev2 データベース ● Dockerコンテナ(PostgreSQL 17.7)を使⽤ PC © WealthNavi Inc. All Rights Reserved. 15

書籍管理システムの⽉次バッチを想定した検証シナリオを作成 前⽉最もレンタルされた書籍を集計するバッチとして以下のシナリオを作成※4 ● ⽬的 ○ ● 利⽤シーン ○ ● 前⽉に貸出回数が多かった書籍 TOP10 を集計 仕⼊れ‧増刷の判断材料 などを想定 検証条件 (データ量) ○ 会員数：1万⼈ ○ ⽉間貸出件数：約4万件 (＝5,000⼈ × 2冊 × 4週) ○ 貸出履歴は2年分保持する ■ © WealthNavi Inc. All Rights Reserved. 貸出履歴は約100万レコードとする ※4: https://zenn.dev/khoshihara/articles/7748ff2d34daaf 16

検証シナリオのデータ量を整理 以下のデータ量およびデータのライフサイクルを前提とする テーブル名 テーブル種別 書籍 (book) マスタ 会員 (member) 貸出履歴 (rental) © WealthNavi Inc. All Rights Reserved. トランザクション トランザクション 概要説明 ● データ量：1000レコード ● ライフサイクル：なし ● データ量：1万レコード ● ライフサイクル：なし ● データ量：100万レコード ● ライフサイクル：2年 17

インデックス付与前のテーブル定義 © WealthNavi Inc. All Rights Reserved. 18

インデックス付与後のテーブル定義 © WealthNavi Inc. All Rights Reserved. 19

SQLクエリ JOINとWHERE句で使⽤する rentalテーブルのカラムに対 してインデックスを作成 © WealthNavi Inc. All Rights Reserved. 20

SQLクエリ‧実⾏計画を pev2 に貼り付け Submit し可視化 © WealthNavi Inc. All Rights Reserved. 21

インデックス導⼊前の EXPLAIN 結果 rental テーブルからの取得処理に Parallel Seq Scan が選択され、コストも⾼い状態 © WealthNavi Inc. All Rights Reserved. 22

インデックス導⼊後の EXPLAIN 結果 rental テーブルからの取得処理に Index Only Scanが選択され、コストも下がった © WealthNavi Inc. All Rights Reserved. 23

4 1 pev2 導⼊の効果 © WealthNavi Inc. All Rights Reserved. 24

pev2 導⼊によって具体的に得られた効果 実際に導⼊した結果、様々な層のエンジニアに対してメリットがあった ● 実⾏計画を読み解いた経験が少ない若⼿エンジニア ○ ● インデックスの作成⽅針や検証⽅法を伝え EXPLAIN 結果を pev2 で可視化することで 実⾏計画を直感的に理解しやすくなった 性能改善に熟達したテックリード ○ pev2 で可視化した実⾏計画を共有することでレビューを効率的に進めることができた © WealthNavi Inc. All Rights Reserved. 25

5 1 まとめ © WealthNavi Inc. All Rights Reserved. 26

まとめ 本セッションでは、各章で以下の内容をお話してきました。 1. pev2 の概要 2. pev2 導⼊の背景 (モチベーション) 3. パフォーマンス改善前後の実⾏計画を pev2 で⽐較‧確認する⽅法 4. 実⾏⾏計画を読み解く負担を軽減できること PostgreSQLのパフォーマンスチューニングでは、ぜひ pev2 を使ってみてください！ © WealthNavi Inc. All Rights Reserved. 27

重要な注意事項 ● 本資料は、断定的判断を提供するものではなく、情報を提供することのみを目的としており、いかなる種類の商品も勧誘 するものではありません。最終的な決定は、お客様自身で判断するものとし、当社はこれに一切関与せず、また、一切の 責任を負いません。 ● 本資料には将来の出来事に関する予想が含まれている場合がありますが、それらは予想であり、また、本資料の内容の 正確性、信頼性、完全性、適時性等を一切保証するものではありません。本資料に基づいて被ったいかなる損害について も、当社は一切の責任を負いません。また、当社は、新しい情報や将来の出来事その他の情報について、更新又は訂正 する義務を負いません。 ● 本資料を利用することによりお客様に生じた直接的損害、間接的損害、派生的損害その他いかなる損害についても、当社 は一切の責任を負いません。 商号等：ウェルスナビ株式会社 金融商品取引業者 関東財務局長（金商） 第2884号 加入協会：日本証券業協会 一般社団法人日本投資顧問業協会 © WealthNavi Inc. All Rights Reserved. 28