はじめに
「遅いSQLをAIに投げれば直してくれる」時代になりましたが、AIが出してきた提案が正しいかを判断するには、結局のところ実行計画を自分で読める必要があります。本記事では、バックエンドエンジニアがAIツール(Claude Codeなど)と協働しながらSQLチューニングを行う上で最低限押さえておきたい知識を、PostgreSQLを例に整理します。
対象読者:
- ORM(Django、Railsなど)を使っていて、生SQLや
EXPLAINをあまり読んだことがない人 - AIにEXPLAIN結果を渡して改善案をもらっているが、提案の妥当性を判断できず不安な人
- Oracleなど他DBの経験はあるが、PostgreSQLの実行計画・オプティマイザの挙動に馴染みが薄い人
1. 実行計画とは何か
実行計画とは、DBMSが「SQLをどんな手順で処理するか」を示した処理手順書です。同じ結果を返すSQLでも、テーブルの結合順序・検索方法・インデックスの使用有無によって内部の処理手順は大きく変わり、それに伴って性能も変わります。
EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.pref = '神奈川';
出力例:
Hash Join (cost=50.2..320.5 rows=120 width=64) (actual time=1.2..8.5 rows=118)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.0..200.0 rows=5000 width=32)
-> Hash (cost=45.0..45.0 rows=100 width=32)
-> Index Scan using idx_pref on customers c (cost=0.4..45.0 rows=100 width=32)
Index Cond: (pref = '神奈川')
2. 読み方のルールは1つだけ
実行計画は木構造(ツリー)です。次の1ルールさえ覚えれば読めます。
インデントが一番深い(内側の)ノードから実行され、結果が上の親ノードに向かって集約されていく
上の例を読む順番は次の通りです。
-
Index Scan using idx_pref on customers— インデックスで神奈川の顧客だけを絞り込む(100行) -
Hash— その100行を結合用のハッシュテーブルに変換する -
Seq Scan on orders— 並行してordersテーブルを全件読み込む(5000行) -
Hash Join— 両者を結合して最終結果を返す
各ノードの意味
| ノード | 何をしているか |
|---|---|
| Seq Scan | テーブルを先頭から末尾まで全件読み込む。インデックスを使わない読み方 |
| Index Scan | インデックスの木構造をたどり、条件に一致する行だけを取り出す |
| Hash | スキャンではなく、直下のノードが取り出した行を結合用にメモリ上で加工する処理 |
Hashの行数(100)は「別の100件をスキャンした」わけではなく、直下のIndex Scanが取り出した行をそのまま加工しているだけ、という点は誤解しやすいので注意してください。
主要な指標
| 項目 | 意味 | 見るポイント |
|---|---|---|
| cost | オプティマイザが見積もった相対コスト(開始..完了) | 突出して大きい行がボトルネック候補 |
| rows(見積) | 統計情報から予測した処理行数 | actual rowsと大きくズレていないか |
| actual time |
ANALYZE使用時のみ表示される実測時間 |
ここが一番遅いノードが改善対象 |
| Filter / Index Cond | WHERE条件の適用方法 |
Filterは「読んでから絞る」＝非効率、Index Condは「絞りながら読む」＝効率的 |
EXPLAINだけだと見積りしかわかりませんが、EXPLAIN ANALYZEは実際にクエリを実行してから計測するため実測値が得られます。ただし更新系SQLに使うと実際にデータが変わるので注意が必要です。
3. 実行計画から得られる4つの情報
-
ボトルネックの特定:
actual timeが突出して大きいノードを特定できる - 見積りと実測のズレのチェック: ズレが大きければ統計情報が古い、あるいは値の分布が偏っているサイン
- 選ばれたアルゴリズムの確認: なぜSeq ScanかIndex Scanか、なぜHash JoinかNested Loopかがわかる
- 改善策の裏付け: 「インデックスを貼ればSeq ScanがIndex Scanに変わるはず」という仮説をBefore/Afterで検証できる
4. 改善のアクション一覧
問題を特定したら、仮説→検証のサイクルを回します。
| 発見した問題 | 次のアクション |
|---|---|
| 大きいテーブルにSeq Scan | 該当列にインデックスを作成 |
| 見積りと実測の乖離大 |
ANALYZE テーブル名;で統計情報を更新 |
| 想定外の結合アルゴリズム | インデックス追加後、再度EXPLAIN ANALYZEで確認 |
| 中間結果が肥大化 | JOIN順序やクエリ構造を書き換える |
| インデックス追加しても改善せず | 不要なインデックスとして削除 |
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
作成後は必ず同じクエリで再度EXPLAIN ANALYZEを取り直し、Seq ScanがIndex Scanに変わったか、actual timeが短縮されたかを確認してください。改善しなければそのインデックスは不要なので削除を検討します。
5. 他DBとの違いで理解するPostgreSQLの個性
PostgreSQL特有の挙動は、Oracle・MySQLとの違いを対比させると腹落ちしやすくなります。まず全体像を比較します。
| 項目 | PostgreSQL | Oracle | MySQL |
|---|---|---|---|
| 実行計画のキャッシュ | 基本なし(プリペアド文のみ、セッション単位のローカルキャッシュ) | インスタンス全体で共有されるShared Pool(Library Cache)に自動キャッシュ |
クエリキャッシュは廃止済み、基本は毎回パース |
| ヒント句 | 標準では非対応(pg_hint_plan拡張で疑似的に対応) |
/*+ INDEX(t idx) */など豊富なヒント句が標準搭載 |
限定的なヒントあり |
| 対応する結合アルゴリズム | Nested Loop / Hash Join / Merge Join | 上記に加えパーティション結合など多彩な方式 | 基本的にNested Loopのみ |
| バインド変数への追従 | Custom Plan(値ごとに再計画) / Generic Plan(汎用プランを再利用)の二択 | Bind Peeking + Adaptive Cursor Sharing(値の偏りを検知して自動で計画を使い分け) | 該当機能なし |
| 統計情報の管理 |
ANALYZE(autovacuumのauto-analyzeで自動化) |
DBMS_STATSパッケージ、履歴をデフォルト31日保持しロールバック可能 |
ANALYZE TABLE |
Oracleとの違い①: 実行計画をキャッシュする場所
Oracleはインスタンス全体で共有されるShared PoolにSQLと実行計画をキャッシュするため、別セッションが投げた同じSQLでも既存のキャッシュ済みプランがそのまま再利用されます。一方PostgreSQLには全体共有のキャッシュが存在せず、プリペアド文を使わない限り、接続のたびに・SQLを送るたびに毎回ゼロから計画を作り直します。プリペアド文を使った場合でも、キャッシュされる範囲は「そのセッション内」だけです。「同じSQLなのに、DBを乗り換えたら速度が全然違う」という現象の背景には、この構造の違いがよくあります。
Oracleとの違い②: バインド変数への対応の細やかさ
前章で触れたPostgreSQLの「custom plan / generic planの6回目切り替え問題」は、OracleのBind Peeking + Adaptive Cursor Sharingと対比すると理解しやすくなります。Oracleは初回実行時に渡された値を覗き見て(Bind Peeking)最適なプランを作りますが、その後も値の分布によって最適なプランが変わりそうな「値の偏りに敏感なSQL」を自動検知し、必要に応じて複数のプランを使い分けます(Adaptive Cursor Sharing)。PostgreSQLにはこの自動判別機構がなく、custom planかgeneric planかの二択で、切り替え基準は実測ではなく見積りコストの比較のみです。Oracleより単純な分、予期しないタイミングでプランが変わって性能が急落するリスクが相対的に高いと言えます。
Oracleとの違い③: 統計情報の柔軟性
Oracleは統計情報の履歴をデフォルト31日間保持し、統計を更新して性能が悪化した場合は過去の統計にロールバックする、といった運用が可能です。PostgreSQLのANALYZEにはそうした履歴管理機能がなく、一度上書きされた統計情報は基本的に戻せません。統計更新前にバックアップを取る、あるいは本番投入前にステージング環境で検証するといった運用上の工夫がより重要になります。
MySQLとの違い: 結合アルゴリズムの選択肢
MySQLの結合方式は基本的にNested Loop(少量行の結合に向く方式)のみで、Hash JoinやMerge Joinのような大量行の結合・集計に強い方式がPostgreSQLやOracleほど充実していません。大量データを結合・集計するバッチ処理やレポーティング用途のクエリをMySQLで書く場合は、この制約を踏まえてクエリ設計(事前の絞り込み、集計テーブルの用意など)を意識する必要があります。
まとめると
- Oracle経験者がPostgreSQLを触るときの最大の注意点は「実行計画はキャッシュされない」「ヒント句が使えない」という前提の違い
- PostgreSQLでチューニングに詰まったら、まず「Oracleならここで自動的にやってくれることを、PostgreSQLでは自分で(ANALYZEの手動実行やインデックス設計で)肩代わりする必要がある」と捉えると設計判断がしやすくなります
6. PostgreSQL特有の落とし穴(詳細)
統計情報に有効期限はない
PostgreSQLの統計情報には「有効期限」という概念がなく、次にANALYZE(手動 or autovacuumのauto-analyze)が実行され上書きされるまで使われ続けます。前述の通りPostgreSQLは基本的にSQL実行のたびに毎回その時点の統計情報でゼロから実行計画を作り直すため、統計情報が更新された直後の1回目のクエリから新しいプランが使われます。
プリペアド文の「6回目問題」
アプリからバインド変数(プレースホルダ)を使うプリペアド文は、1〜5回目は毎回パラメータの値に基づいたcustom planを作りますが、6回目以降はそれまでの平均コストとgeneric plan(汎用プラン)の見積りコストを比較し、安ければgeneric planに切り替わります。
比較基準が「実測時間」ではなく「見積りコスト」である理由は次の通りです。
- 実測で比較しようとすると両方式を実際に1回ずつ実行する必要があり、削減したいはずのオーバーヘッドが増えてしまう
- 実測時間はキャッシュ状況や負荷などの外部要因でブレやすく、公平な比較基準になりにくい
- custom planを作る過程で見積りコストはどのみち算出されるため、追加コストなしで比較できる
ただしこれはあくまで見積り同士の比較なので、値によって最適なプランが大きく変わるクエリでは「見積り上は安いが実際は激遅」なgeneric planに切り替わるリスクがあります。心配な場合は次の設定で常にcustom planを強制できます。
SET plan_cache_mode = 'force_custom_plan';
7. AI時代のワークフロー
実行計画の読み方さえ押さえておけば、あとはAIとの分業がしやすくなります。
-
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)で実行計画をファイルに出力する - Claude CodeなどのAIツールにファイルを渡し、「以下のEXPLAIN ANALYZE結果を分析して」と依頼する
- AIが提示したボトルネック・改善案(インデックス追加、クエリ書き換えなど)を、Before/Afterの実行計画とセットで人間が検証する
- 統計情報の更新タイミングやプリペアド文の挙動、DB製品ごとのプランナの違いなど、内部の仕組みに起因する不安定さはAIが見落としやすいため、人間側でチェックする
AIは「実行計画を読んで改善案を出す」作業を高速化してくれますが、なぜその案が有効なのか、副作用はないかを判断する最終責任はエンジニア側に残ります。木構造の読み方、見積りと実測の違い、DB製品ごとのオプティマイザの個性を理解しているかどうかが、AIの提案を鵜呑みにせず正しく検証できるかの分かれ目になります。
まとめ
- 実行計画は「一番深いノードから上に向かって読む」木構造
-
EXPLAINは見積りのみ、EXPLAIN ANALYZEは実測値も取得できる - 改善は「仮説→インデックス作成やクエリ修正→再度EXPLAIN ANALYZEで検証」のサイクル
- PostgreSQLは実行計画をほぼキャッシュせず毎回作り直す点、ヒント句が標準では使えない点がOracleと大きく異なる
- プリペアド文のcustom/generic plan切り替えは、Oracleの自動判別機構(Adaptive Cursor Sharing)がない分、より注意が必要
- AIに解析を任せる時代でも、提案を検証するための基礎知識と他DBとの違いの理解は引き続き必須