はじめに
初めまして、キャスレーコンサルティング SI（システム・インテグレーション）部の松本です。
業界3年目にして、実行計画を一度も見たことがなかった私が、
「実行計画とは何か、どのように取得するのか。」について調べた結果をまとめました。
※本ブログでは、PostgreSQL Tutorialより提供されているdvdrentalというDBをサンプルに用いております。
目次
1.実行計画とは
2.実行計画の取得方法
2.1.EXPLAIN文
2.2.オプション(ANALYZE,FORMAT)
2.3.ANALYZE文
3.実行計画の構造
3.1.ノードのツリー構造について
3.1.1.テーブルスキャンノード
3.1.2.結合系ノード
3.1.3.その他のノード
3.2.処理コストの確認方法
4.まとめ
1.実行計画とは
実行計画とは、ユーザによって発行されたクエリを実行するための手順書の事です。
主にテーブルからのデータ取得方法や、テーブルの結合方法などが書かれています。
また、作成はプランナが、データの並び順や物理的な配置などの様々な統計情報を基に行います。
基本的には、ベストな実行計画を作成してくれます。
図 1-1 実行計画の実行まで
ここで「ベストな実行計画を作成してくれるのであれば、なぜ実行計画を見る必要があるのか？」
というような疑問が浮かんだ人もいるかもしれません。
必要な理由としては二つあり、
一つ目は「SQLを書くときに複数のSQLで迷った場合」です。
この場合、実行計画を確認することで、予想コストが低い方、もしくは実行時間が短い方を選択することが可能となります。
二つ目は、「レスポンスが遅いと感じた時」です。
プランナ自体は、ただのプログラムなので、作られる実行計画も、
実際のデータから導き出すという点からは、問題ないのですが、
ユーザの思惑や、仕様上の特性などについては考慮してくれません。
そういった点から、想定外の実行計画となっていないかを確認するために見る必要があります。
このように、実行計画を見る機会はあまり多くないにせよ、
見なくてはいけないときがあるということがわかりましたでしょうか。
次の章では実行計画の取得方法について説明していきます。
2.実行計画の取得方法
PostgreSQLでは「EXPLAIN」文を使用します。
2.1.EXPLAIN文
EXPLAINを、実行するSQLの先頭につけることで、実行計画を取得できます。
下記サンプルで「EXPLAIN」文の実行結果を記載しています。
●実行したSQL
dvdrental=# EXPLAIN select * from actor;
●実行結果
QUERY PLAN
Seq Scan on actor (cost=0.00..4.00 rows=200 width=25)
(1 行)
この結果が実行計画と呼ばれるものです。
また、「EXPLAIN」文には複数のオプションが存在します。
本ブログでは、数あるオプションからピックアップし、2つ紹介します。
2.2.オプション(ANALYZE,FORMAT)
1つ目が、「ANALYZE」オプションです。
「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。
(※ ANALYZEオプションを付与すると、実際にクエリが実行されてしまうため、
INSERT文や、DELETE文などを行う際は注意が必要です。)
●実行したSQL
dvdrental=# EXPLAIN ANALYZE select * from actor;
●実行結果
QUERY PLAN
Seq Scan on actor (cost=0.00..4.00 rows=200 width=25) (actual time=7.084..7.707 rows=200 loops=1) Planning time: 0.077 ms Execution time: 7.743 ms
(3 行)
Planning time、Execution timeの部分が、ANALYZEオプションによって追加で表示された内容です。
順番に説明していきます。
actual time：処理時間を表す
rows：実行結果として、実際に戻ってきた行数を表す
loops：ステップの実行回数を表す
Planning time：解析されたクエリから実行計画を生成し、最適化するのに要した時間
Execution time：実行時間を表す
EXPLAINと、EXPLAIN ANALYZEの違いはプランナが見積もった値か、実際の実行時に発生した値かです。
2つ目が、「FORMAT」オプションです。
「FORMAT」オプションを付与することで、「EXPLAIN」文の出力フォーマットを変更できます。
デフォルトはTEXT形式となっています。
EXPLAINとSQLの間に「 (FORMAT 出力フォーマット*)」と記載することで、変更ができます。
(*にはTEXT、JSON、XMLなどを記載してください。)
下記サンプルではJSON形式で出力しています。
●実行したSQL
dvdrental=# EXPLAIN (FORMAT JSON) select * from actor;
●実行結果
QUERY PLAN
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan", +
"Relation Name": "actor",+
"Alias": "actor", +
"Startup Cost": 0.00, +
"Total Cost": 4.00, +
"Plan Rows": 200, +
"Plan Width": 25 +
} +
} +
]
(1 行)
実行計画の取得方法、取得時のオプションについての説明は以上となり、
次はプランナが実行計画を作成するうえで、参照する統計情報の取得方法について説明します。
2.3.ANALYZE文
「ANALYZE」文とは、データの並び順や物理的な配置などの統計情報を取得するためのSQLです。
統計情報を取得したいテーブルの前に、「ANALYZE」と記載することで取得できます。
ただし、PostgreSQLでは、autovacuum機能による自動VACUUM時に、
統計情報の取得が行われるようになっているため、「ANALYZE」文での取得はほとんど必要ありません。
下記サンプルでは「ANALYZE」文実行しています。
●実行したSQL
dvdrental=# ANALYZE actor;
●実行結果
ANALYZE
実行計画の取得方法、統計情報の取得方法について説明しましたので、
3章では取得した実行計画の構造について説明します。
3.実行計画の構造
3章では、取得した実行計画の構造について詳しく見ていきます。
3.1.ノードのツリー構造について
実行計画で処理を行う単位をノードと呼び、ツリー構造となっています。(図3-1 ノードのツリー構造 参照)
最下層ノードはテーブルスキャンノードで、
その上に結合ノード、さらに上にその他(ソートなど)のノードとなっており、
最下層のノードから順に実行されます。
図3-1 ノードのツリー構造
まずは、最下層のテーブルスキャンノードについて説明していきます。
3.1.1.テーブルスキャンノード
テーブルスキャンノードとは、テーブルからデータを取り出す役割のノードです。
代表例として、テーブル全体を順番にスキャンするSeq Scanや、
テーブルに付与されているインデックスのみをスキャンし、実テーブルはスキャンしないIndex Scanなどが存在します。
下記サンプルではSeq Scanが使われています。
●実行したSQL
dvdrental=# EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC;
●実行結果
QUERY PLAN
Sort (cost=68.67..70.18 rows=603 width=84) Sort Key: address.city_id Hash Join (cost=18.50..40.82 rows=603 width=84) Hash Cond: (address.city_id = city.city_id) Seq Scan on address (cost=0.00..14.03 rows=603 width=61) Hash (cost=11.00..11.00 rows=600 width=23) Seq Scan on city (cost=0.00..11.00 rows=600 width=23)
(7 行)
【各行の説明】
7行目：cityテーブルのデータを取り出しています
5行目：addressテーブルのデータを取り出しています
3.1.2.結合系ノード
次に結合系のノードについてです。
結合系ノードは、複数のテーブルを結合する役割のノードです。
代表例として、外側テーブルの行毎に内側テーブルのすべての行を突き合わせ結合するNested Loopや、
内側テーブルの結合キーでハッシュを作成し、ハッシュと外側テーブルの結合キーで一致する行を結合するHash Joinなどが存在します。
下記サンプルではHash Joinが使われています。
●実行したSQL
dvdrental=# EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC;
●実行結果
QUERY PLAN
Sort (cost=68.67..70.18 rows=603 width=84) Sort Key: address.city_id Hash Join (cost=18.50..40.82 rows=603 width=84) Hash Cond: (address.city_id = city.city_id) Seq Scan on address (cost=0.00..14.03 rows=603 width=61) Hash (cost=11.00..11.00 rows=600 width=23) Seq Scan on city (cost=0.00..11.00 rows=600 width=23)
(7 行)
【各行の説明】
7行目：city(内側)テーブルのデータを取り出しています
6行目：ハッシュを作成し、
5行目：address(外側)テーブルのデータを取り出しています
4行目：ハッシュと外側テーブルの結合キー(city_id)
3行目：結合をしています
3.1.3.その他のノード
最後にその他のノードについてです。
その他のノードとしては、スキャン結果をソートするSortなどが存在します。
下記サンプルでSortが使われています。
●実行したSQL
dvdrental=# EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC;
●実行結果
QUERY PLAN
Sort (cost=68.67..70.18 rows=603 width=84) Sort Key: address.city_id Hash Join (cost=18.50..40.82 rows=603 width=84) Hash Cond: (address.city_id = city.city_id) Seq Scan on address (cost=0.00..14.03 rows=603 width=61) Hash (cost=11.00..11.00 rows=600 width=23) Seq Scan on city (cost=0.00..11.00 rows=600 width=23)
(7 行)
【各行の説明】
7行目：city(内側)テーブルのデータを取り出しています
6行目：ハッシュを作成し、
5行目：address(外側)テーブルのデータを取り出しています
4行目：ハッシュと外側テーブルの結合キー(city_id)
3行目：結合をしています
2行目：ソートのキーを決定しています
1行目：取得結果をソートしています
このように、複数のノードから一つの実行計画が作られているため、
複雑な実行計画も、時間をかければ読むことは可能です。
次に、処理コストの見方を説明します。
3.2.処理コストの確認方法
取得した実行計画の各ノードには、始動コストと総コスト、行数と行の長さが記載されています。
始動コスト:一件目のデータを返すのにかかる想定のコストを表す
総コスト:処理完了までにかかる想定のコストを表す
行数：プランナによって見積もられた、ノード実行によって返却される行数を表す
(※実際に取得した結果の行数ではありません。)
行の長さ：上記同様プランナによって見積もられた、ノードの実行によって返却される行の平均の長さを表す
下記サンプルに、処理コストや行に関する内容が記載されています。
●実行したSQL
dvdrental=# EXPLAIN select * from actor;
●実行結果
QUERY PLAN
Seq Scan on actor (cost=0.00..4.00 rows=200 width=25)
(1 行)
【各行の説明】
一行目の「cost=」の後にある「0.00」：始動コスト、
後半の「..」の後にある「4.00」：総コスト
「rows=」の後にある「200」：行数
「width=」の後にある「25」：行の長さ
最後に始動コストが「0.00」以外のノードのサンプルを記載します。
●実行したSQL
dvdrental=# EXPLAIN select * from address inner join city ON address.city_id=city.city_id ORDER BY city.city_id ASC;
●実行結果
QUERY PLAN
Sort (cost=68.67..70.18 rows=603 width=84) Sort Key: address.city_id Hash Join (cost=18.50..40.82 rows=603 width=84) Hash Cond: (address.city_id = city.city_id) Seq Scan on address (cost=0.00..14.03 rows=603 width=61) Hash (cost=11.00..11.00 rows=600 width=23) Seq Scan on city (cost=0.00..11.00 rows=600 width=23)
(7 行)
7行目のSeq Scanは事前に準備がないため、始動コストが0.00となっています。
それに対し、1行目のSortは、一度すべてのデータを確認してから一件目の処理を行うため、
始動コスト(cost=)が68.67となっています。
ここまでの説明で、実行計画の取得方法と、構造、読み方の説明が終わりとなります。
最後にまとめです。
4.まとめ
・実行計画とは、クエリ実行にあたっての手順書のことである
・実行計画の取得には「EXPLAIN」文を使用する
・複雑な実行計画であっても、構造(ノードのツリー構造)は同じため、時間をかければ読むことは可能である
・「複数のSQLで迷った時」には、「EXPLAIN ANALYZE」で
処理コストや処理時間を確認し、低い方、短い方のSQLを選択する
・「レスポンスが遅いと感じた時」には、実行計画を決定するための統計情報が古い可能性があるため、
「EXPLAIN」と「EXPLAIN ANALYZE」で確認する
「EXPLAIN」で取得したrowの値はプランナによって見積もられた行数で、
「EXPLAIN ANALYZE」で取得したrowの値は実際に実行結果として取得した行数なので、
rowの値がずれている場合は、統計情報が古いということになる
・統計情報が古い場合「ANALYZE」文で最新の統計情報を取得する
最後に
今回のブログでは、PostgreSQLの実行計画について紹介致しました。
ご一読いただき、ありがとうございました。
