PostgreSQLの実行計画の読み方
2021.03.25
実行計画とは
実行計画とは、データベースがSQLを実行するときに見る手順書
実行計画を見れば、「どうしたら短い時間でSQLを実行できるか」の見積もりを見れます。
ざっくり、以下のことが確認できる
- テーブルへのアクセス順序
- テーブルからデータを取得する際の探索方法
- テーブル間の結合方法
SQLの実行が遅い原因を探る際に、まず最初に見るべきなのがこの実行計画で、オプティマイザとよばれる機能によって作られます。
オプティマイザについて少し解説
オプティマイザは決められたルールに基づいて、より短い時間でSQLを実行できるような実行計画を作成します。
SQLは非手続き型言語なので、オプティマイザが処理の順序を自由に選択することができます。
オプティマイザは2種類存在します。
ルールベースのオプティマイザ（RBO）
- RBOはSQLを分解して、そのルールに基づいて最適化を行います。
コストベースのオプティマイザ（CBO）
- CBOはデータベースを定期的に統計し、その統計情報をもとに最適化を行います。
- 現在のRDBMSの主流はCBOで、理由としてはデータが変化する環境においても、データにフィットした実行計画が出せるようになっているためです。
実際に実行計画を取得する
実際に実行計画を出力してみます。
やり方は、調査したいクエリの先頭にEXPLAINを足して実行するだけです。
EXPLAIN 調査したいクエリ;
「ANALYZE」オプションを使用することで、予想コストに加え、実際にかかった処理時間等の情報も確認できます。
EXPLAIN ANALYZE (調査したいクエリ);
↓実際の測定結果
sample=# explain analyze select * from users where users.relation_id = 121 order by id desc limit 1;
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Limit (cost=0.42..9761.20 rows=1 width=70) (actual time=42.868..42.874 rows=0 loops=1)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan Backward using users_pkey on users (cost=0.42..68325.86 rows=7 width=70) (actual time=42.852..42.859 rows=0 loops=1)
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Filter: (relation_id = 121)
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Rows Removed by Filter: 210011
-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Planning time: 1.876 ms
-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Execution time: 42.945 ms
actual time：処理時間
rows：実際に戻ってきた行数
loops：ステップの実行回数
Planning time：最適化にかかった時間
Execution time：実行時間
実行される処理順序
- 実行計画の出力結果は入れ子になっており、入れ子の深いところから順に実行される
- 兄弟要素がある場合は、先に記述されている行から実行される
実行計画の見るべきポイント
- パフォーマンス不良が起きた場合は、実行計画のどの部分にどれだけのコストがかかったかを確認する
- 複数のSQLでどちらを採用すべきか迷った場合は、実行時間の短い方のSQLを採用すればよい
- シークスキャン（データの先頭から一つずつ確認するスキャン。遅い。）が使われている箇所はインデックスが貼れるか検討する
参考
https://www.postgresql.jp/document/9.2/html/sql-explain.html
https://www.ydc.co.jp/solution/standby/article/postgres_1.html