【PostgreSQL】Explainの見方(analyze , cost , scan , sort)についてのまとめ

2021年5月1日PostgreSQLその他

Explainとは

Explainは推定された実行計画を表示するコマンドです。

『実行計画=最適なのか』を確認する際に使用します。

-- 実行方法:select文の前に「explain」を加えるだけでOK
explain select * from XXXXX;

 

 

ExplainとExplain Analyze

「Explain」には、「Explain」と「Explain Analyze」という二つの書き方があります。

Explain AnalyzeもExplainと同様、SELECT文の先頭に付けて実行します。

 

二つの違いは、

Explain              … 推定された実行計画を表示する

Explain Analyze … 推定ではなく、一度実行してみた結果を表示する

--推定された実行計画を表示する
explain select * from XXXXX;         

--推定ではなく、一度実行した結果を表示する
explain analyze select * from XXXXX; 

 

 

costの見方

実際にexplainを実行すると、「cost」という値が返ってきます。

cost=A..B
-- A = 初期コスト(select文に対して最初の行を返すコスト)
-- B = トータルコスト(最後の行を返すコスト)

重要なのはトータルコストのほうです。また、costの値は相対値であり、単位はありません。

(シーケンシャルで1ページを読み込む場合のコストを1.0として示されます)

 

 

cost以外の値(rows、width)

cost以外の値、rows、widthは次のとおり。(widthはそれほど重要ではない)

cost=A..B rows=1000 width=4
-- rows = select文の結果として推定された行数を表示(あってない場合も全然ある)
-- width = 取得される行の平均サイズ(integer:4 , boolean:1 ・・・)

 

rowsの値が実際とかけ離れている場合、vacuum、analyzeし統計情報を更新を検討します。

vacuum、analyzeの説明はこちらに記載しています。

 

 

actual time

explain analyzeを実行した時には、実際の実行時間が返ってきます。

actual time=A..B rows=800 loops=1
-- time = 実際の実行時間(ミリ秒)
-- loops = 処理の繰り返し回数

 

 

○○ Scanの違い

返ってきた結果で○○ Scanと書いてあるところの説明です。

・Seq Scan                  … 全行スキャンしている(インデックスを使用していない)

・Index Scan           … インデックスを使用している

・Bitmap Scan        … (例えば複数の)インデックスを効率的に使用してスキャンしている

・Index Only Scan … テーブルのアクセスを省略して検索。非常に高速

※ Index Only Scanは、テーブルに直接アクセスせずVisibility Mapから取得する

 

 

Sort

Sort Methodの説明です。

・quick sort Memory   … メモリ上のみで処理ができたため早い

・external sort dsik   … メモリ+ディスクへのアクセスも発生するため時間がかかる

 

 

Nested loop , Merge Join , Hash Joinについて

テーブル同士の結合について書いています。

・Nested loop … 全行×全行のイメージ

・Merge Join … 結合するテーブルをそれぞれソートした後に結合する。走査回数が減る

・Hash Join … あらかじめテーブルのハッシュ表を作り、結合するテーブルと突き合わせる

※ Hash Joinのハッシュ表はメモリに収めておく。

 

比較すると

・Nested loop … データが小さい場合に有効

・Merge Join  … データが多い場合に有効

・Hash Join    … メモリーが十分にある場合に有効

 

 

参考・引用

次の資料を参考にさせていただきました。

『Slide Share , https://www.slideshare.net/MikiShimogai/sql-42453213,SQLチューニング入門 入門編,

PostgreSQLカンファレンス2014 2014年12月5日, TIS株式会社下雅意美紀』