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

Explainとは

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

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

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

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

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

二つの違いは、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sort Methodの説明です。

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

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

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

・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株式会社下雅意美紀』