はじめに
SQLの性能を調整する際、最初に確認すべき情報が「実行計画」です。
実行計画は、データベースがSQL文をどのような手順で処理しているかを示すもので、テーブルのアクセス方法、結合戦略、ソートや集約の方法などが記録されています。これを正しく読み解くことで、ボトルネックの特定やチューニングの方向性を見出すことができます。
しかし、実行計画には情報が多くどこから読めばよいのか分かりにくいので、本記事では実行計画を読むのに必要な情報を整理しました。
-
実行計画の取得方法
PostgreSQLを中心に、Oracleとの差分を補足しながら解説します。 -
実行計画の読み方
実行順序の基本ルール、出力されるカラムの意味、注目すべき指標を説明します。 -
代表的なアルゴリズム
テーブルスキャンや結合方式など、実行計画によく登場する処理の特徴を整理します。
全体的に PostgreSQL を中心に、必要に応じて Oracle の例を入れながら解説します。
第1章：実行計画の取得方法
SQLのチューニングや原因調査で最初に行うのが「実行計画を取って眺める」ことです。
1.1 PostgreSQLの実行計画取得
基本コマンド
- 推定のみ（実際の実行はしない）
EXPLAIN SELECT * FROM users WHERE id = 100;
また、以下のオプションを付けることでより詳しい内容を取得することができます。
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, COSTS) SELECT * FROM users WHERE id = 100;
| オプション | 意味 / 出力される情報 | 主な用途 | 注意点 |
|---|---|---|---|
| ANALYZE | 実際にSQLを実行して、実測の行数・時間 を表示 | 推定と実測の乖離を確認できる | 実行自体が行われるため、副作用のあるSQL（INSERT/UPDATE/DELETE）やリソースの負荷が大きいSQLの実行は注意する |
| BUFFERS | 共有ブロックのヒット数、読み取り数などバッファの使用状況を表示 | ディスクI/Oやワークメモリ不足を特定できる |
ANALYZE と組み合わせた場合のみ使用できる |
| VERBOSE | スキーマ修飾テーブル・出力列など 詳細情報 を表示 | 複雑なビューやサブクエリ解析に便利 | 出力が冗長になるので必要な時に使うと良い |
| COSTS | ノードごとの 推定コスト を表示 | オプティマイザの判断基準を確認できる | デフォルトでON |
1.2 Oracleの実行計画取得
- 事前計画（推定のみ）
EXPLAIN PLAN FOR
SELECT * FROM users WHERE id = 100;
- 実測付き（実際にクエリを走らせる）
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
第2章：実行計画の読み方（順番とカラムの意味）
実行計画を取得できても、「どこから読めばいいのか？」「どの値を見ればいいのか？」で迷う方は多いです（少なくとも私はそうでした）。
ここでは実行順序の基本ルールやカラムの意味を説明していきます。
2.1 実行順序の基本ルール
実行計画はツリー構造で出力されます。
このときの読み方の基本ルールは以下の通りです。
-
深い階層（下の段）のノードから読む
- 深い階層のノードの処理が先に実行され、その結果が上に渡される
-
同じ階層に複数のノードがある場合は上のノードから読む
- 上のノードの実行結果を使って下のノードの処理を実行する
具体例を見ていきます（基本ルールが理解しやすくなるのでOracleの例で記載します）。
実行するクエリ
SELECT u.id, u.name, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.age > 40;
実行計画の出力例
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL USERS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN ORDERS_USER_ID_IDX
実行順序
- 下の階層・同じ階層の上から：まず TABLE ACCESS FULL USERS（WHERE u.age > 40で候補抽出）を実行して親に渡す
- 下の階層から：抽出した各行ごとに、ORDERS_USER_ID_IDX → TABLE ACCESS BY INDEX ROWID ORDERSの順でORDERSのデータを取る
※NESTED LOOPSの詳細は後程説明します
2.2 PostgreSQLの実行計画でよく出てくるカラム
| カラム名 | 意味 / 内容 | ポイント |
|---|---|---|
| cost=startup..total | オプティマイザが推定したコスト（単位は任意の指標）。左の数値が最初の1行を出力するまでのコストで、右の数値が初期コストを含めた処理完了までにかかるコスト（※）。 | 実際の時間ではなく「相対的な比較指標」。どのノードが高コストかを確認するのに使う |
| rows | 推定行数 | 統計情報をもとに推定。実測との乖離が大きい場合は統計更新を検討 |
| width | 推定行サイズ（バイト数） | 返す列数やデータ型に依存。 |
| actual time=start..end | 実測の処理時間（ミリ秒）。 costと同じく、左が最初の1行の処理時間で、右が処理完了までの処理時間。 |
ANALYZE を付けた場合のみ表示。推定コストと比較して妥当性を判断する |
| actual rows | 実測の行数 | 実際に返された行数。rows（推定）との比較が重要 |
| loops | ノードが繰り返し実行された回数 | Nested Loop の内側などで増えやすい。外側行数 × 内側処理になる |
| Buffers | バッファやディスクI/Oの利用状況（hit/read/write/temp） | I/Oボトルネックやワークメモリ不足を特定できる |
| Planning Time | 実行計画を立てるのにかかった時間 | 複雑なクエリや統計参照が多い場合に長くなることがある |
| Execution Time | 実際のクエリ全体の実行時間 | 最終的なレスポンス指標 |
※例えばソート処理を含む場合は1行出すまでにすべての行を読み込む必要があるので初期コストが大きくなる
2.3 実行計画を読み進める流れ
参考までに個人的にこう読むと良さそうだと思う実行計画の読む流れを以下に書きます。
-
最上位ノードを確認
- Sort / Group / Join など最終処理の種類を把握する
-
重いノードを特定
- コストが高い or 実測時間が長い部分に注目
-
子ノードに降りて原因を探る
- 行数の増減や loops の多さを確認
-
推定 vs 実測の乖離を確認
- 推定が外れているなら統計更新や結合順序の見直し
-
I/OやTEMPの発生状況を確認
- ワークメモリ不足や不要な全件読みを疑う
-
改善仮説を3つ書き出す
- インデックス設計、結合方式の変更、集約戦略の調整 など
第3章：代表的なアルゴリズム（スキャン／結合／集約）
実行計画を読んでいると頻繁に目にするのが スキャン方法・結合方法・集約方法 です。
それぞれの特徴と使われやすい場面を理解しておくと、実行計画の意味が一気に分かりやすくなります。
3.1 スキャン
スキャン（行の取り出し方）の種類は主に以下の通りです。
Seq Scan（PostgreSQL） / Full Table Scan（Oracle）
- テーブルを頭から全件読み込む方式
- 少量テーブルや選択性の低い条件で有効
- インデックスが使えない場合もこの方式になる
Index Scan / Index Range Scan
- B-Treeインデックスを利用し、条件にマッチする範囲を効率的に取得
- カーディナリティ（カラムがとりうる値の種類）の高いカラムにインデックスが貼られてる場合に有効
- インデックスについては過去に記事を書いたのでこちらもご確認ください
Index Only Scan（PostgreSQL）
- インデックスだけで必要な列がすべて揃う場合に利用される
- テーブル本体を参照しないため高速
Bitmap Index Scan（Oracle） / Bitmap Heap Scan（PostgreSQL）
- Bitmapインデックスを利用し、条件にマッチする範囲を効率的に取得
- 複数条件の組み合わせ（OR条件など）やカーディナリティが低いカラムに有効
- Oracleはインデックス作成時にBitmapオプションを付けることでBitmapインデックスになる
- PostgreSQLではインデックス作成時に指定できないが、B-treeインデックスを貼ることで状況に応じてBitmapインデックスが選択される
3.2 結合アルゴリズム
結合アルゴリズム(2つのテーブルを結合する)は主に以下の通りです。
Nested Loop
- 結合する2つのテーブルのうち、片方のテーブル（外部表）を1行ずつ取り出して、もう片方のテーブル（内部表）をループしながらスキャンする
- 外部表が少量かつ内部表の結合キーに効果的なインデックスがあるときに有効（インデックスによって内部表の読み込みをスキップできるため）
Hash Join
- 結合する2つのテーブルのうち、片方のテーブルをスキャンして結合キーにハッシュ関数を適用してハッシュ値に変換する
- もう片方のテーブルをスキャンしてそのハッシュ値が存在するかどうかを調べる
- Nested Loopsで適切な外部表（つまり相対的に小さいテーブル）が存在しない場合に有効
- ハッシュ値を使うのはDBMSのワーキングメモリに保持する都合上、なるべく小さくしたいため
Merge Join
- 結合する2つのテーブルをそれぞれ結合キーでソートして一致する結合キーを見つけたら結果セットに加える
- Hash Join同様、Nested Loopsで適切な外部表が存在しない場合に選択肢として入る
- Hash Joinと比較すると、テーブル規模にもよるが、Hashは片方のテーブルしかハッシュテーブルを作らないので、Hashよりメモリを使う場合がある
結合アルゴリズムについては以下の記事が図を使って説明されていてとてもわかりやすかったので紹介します。
Nested Loop/Hash/Sort Merge結合の違いとパフォーマンス比較
3.3 集約
集約（GROUP BYなど行をまとめる）で利用されるアルゴリズムは主に以下の通りです。
Hash Aggregate（PostgreSQL/Oracle）
- 集約キーをハッシュテーブルに格納して集計
- ソートを使う方法より高速であることが多い
Sort + GroupAggregate
- ソート後に同じキーをまとめる方式
- ソートコストがかかるため、大量データでは注意
おわりに
本記事では、PostgreSQLを中心に実行計画の読み方を整理しました。
実行計画ははじめて読むと情報量が多く戸惑いますが、今回解説した内容が少しでも助けになれば幸いです。
ここまで読んでいただきありがとうございました。