はじめに
業務でクエリチューニングを行った際に、EXPLAIN文を使うことがあったので勉強がてらまとめました。
PostgreSQL内部で何を行っているかざっくりと理解しておいたほうが、EXPLAIN文を理解すやすいと思ったので一緒に調べました。
そもそもクエリチューニングとは
クエリを調整して使用するメモリや実行速度を改善するようなことを言います。
実際に出力される結果に変更はされないように行います。
PostgreSQL内部の全体的なクエリ処理のフロー
1. 接続の確立
1プロセスに1ユーザーのクライアント/サーバモデルによって実装されています。
接続が確立されたら、クライアントからサーバへ問い合わせを送ることができるようになります。
2. 構文の確認
構文の確認を行う部分をパーサと呼びます。
パーサでは平文のテキストとして渡される問い合わせの文字列が正しいかを確認して、正しい場合には構文解析ツリーが返され、正しくない場合にはエラーを返します。
3. 書き換え
パースツリーを解析してクエリツリーに変換します。
その際にテーブルが実際に存在するかどうかをチェックし、存在していたらテーブル名をOIDに変換している部分をアナライザと呼びます。
OIDは、Let's Postgresによると
オブジェクト識別子とは、PostgreSQLがDB内部に格納している様々なオブジェクト(テーブルやインデックス、関数、演算子、データ型定義などなど)を一意に識別するためのIDです。
クエリを書き換えることによってVIEWやRULEを実装している部分をリライタと呼びます。
(ここら辺があまり理解できていないなく、かなりざっくりとしているので必要に応じてドキュメントを参考にしてください。)
4. 最適な実行方法を決める
これらを元に、最適な実行計画を作成する部分をプランナ/オプティマイザと呼びます。
ここでは結果が変わらないすべての経路を作成し、それぞれのコストを比較して一番早い経路を選択します。
例えばインデックスがある場合、インデックスを使用するパターンとインデックスを使用しないパターンを比較してより効率的である方を実行計画とします。
5. 実行する
実際に実行する部分をエクゼキュータと呼びます。
プランナ/オプティマイザで作成された計画を元に実行し、実行結果をクライアントへ返却します。
EXPLAIN文
一言で言うとプランナ/オプティマイザが生成する実行計画を表示する構文です。
プランナ/オプティマイザで最適な実行計画を決めるために使用されるコストの見積もりや、実際に実行される時間などを計測することができます。
PostgreSQLで決めれられている実行計画が、実際に最適なのかを確認するためにEXPLAIN文で確認することで、クエリチューニングに活かすことができる。
実際にEXPLAIN文を使用してみる
セレクト文の前にEXPLAINをつけるだけで、そのセレクト文の実行計画やコスト見積もりを見ることができる。
今回はidにインデックスがはってあるuserテーブルを例としてみる。
EXPLAIN SELECT * FROM user WHERE id <= 100;
-- 下記が出力結果
Index Scan using monitor_pkey on user (cost=0.28..50.77 rows=27 width=361);
出力結果の確認方法
実行計画
Index Scan using monitor_pkey on user
そのままですがmonitor_pkeyを使用してuserテーブルをIndex Scanするという実行計画を立てています。
「Index Scan」とはインデックスを使用してスキャンをするテーブルスキャンの一種です。
全件を検索する場合は「Seq Scan」、ビットマップを使用する場合は「Bitmap Scan」などが使用されます。
コストの見積もり
(cost=0.28..50.77 rows=27 width=361);
・costは..の左側が初期コスト(最初の行を返すまでのコスト)、
・costは..の右側がトータルコスト(最後の行を返し終わるまでのコスト)
・rowsは推定された行数
・widthは推定された入力サイズ
となっています。
コストの単位について
コスト1.00はシーケンシャルの読み込み1回分のコストと同じになっています。
where句の条件だけ変えてみる
EXPLAIN SELECT * FROM user WHERE id <= 600;
-- 下記が出力結果
Seq Scan on user (cost=0.00..88.01 rows=389 width=361)
今回は対象となるidを600以下に設定しました。
先ほどまでは「Index Scan」が行われていたのですが、今回は「Seq Scan」(全件検索)となっています。
そのため、初期コストがかからないものの、トータルコストが高くなっています。
indexが機能しているかはこのようにして確認することができます。
実際のコストを計測するには
今までのEXPLAIN文では、コストの見積もりを出力しています。
しかし、見積もりと実際のクエリを実行する場合ではコストが変わってきます。
EXPLAIN ANALYZE文を使用する
EXPLAIN ANALYZE SELECT * FROM user WHERE id <= 600;
-- 下記が出力結果
Seq Scan on user (cost=0.00..88.01 rows=389 width=361) (actual time=0.007..0.213 rows=387 loops=1)
Filter: (id <= 600)
Rows Removed by Filter: 974
Planning time: 0.075 ms
Execution time: 0.241 ms
実際にクエリを実行して、実際の計測データを表示しています。
まとめ
EXPLAIN文でクエリチューニングの前後を比較することでどの程度改善されたのか数字で計れますし、インデックスが想定通りになっているのかわかりそうです。
プランナ/オプティマイザで決められた実行計画を変更する方法も書こうかと思ったのですが、案外ボリューミーになってしまいそうなので次回にします。
参考
下記のpostgresqlのドキュメントを参考にしたのでより詳しく知りたい方はご覧ください。
PostgreSQL 9.6.5文書
PostgreSQLクエリ実行の基礎知識 ～Explainを読み解こう～
Let's Postgres