PostgreSQLのEXPLAINについて
PostgreSQLで重たいクエリの調査したときのメモ。
EXPLAINについて
EXPLAINは問い合わせプランを表示するコマンド。
PosgreSQLではSQL実行前に、プランナで問い合わせの実行コストを見積もり、
最小コストの問い合わせプランを算出する(問い合わせの最適化)。
問い合わせプランとは、
・どの方式でテーブルを検索するか
・複数のテーブルがある場合はどの順序や結合方式で検索するか
などを記述したもの。
実行してみる
EXPLAINと先頭につけることでコストが表示される。
cost=初期コスト..トータルコスト の意味。トータルコストの値が重要。
シーケンシャルI/Oで1ページ読み込みコストを1.0とした場合の相対値として表示。
EXPLAIN SELECT * FROM articles ORDER BY articles.id DESC NULLS LAST; QUERY PLAN ------------------------------------------------------------------------------------------------------ Gather Merge (cost=12167.24..13390.38 rows=10636 width=1588) Workers Planned: 1 -> Sort (cost=11167.23..11193.82 rows=10636 width=1588) Sort Key: id DESC NULLS LAST -> Parallel Seq Scan on articles (cost=0.00..3109.36 rows=10636 width=1588)
ANALYZE で 実際の実行時間を表示できる 。
(actual time=158.749..188.486 rows=18081 loops=1)
loopsは処理の繰り返し回数。timeは繰り返し全体の時間を表す。
EXPLAIN ANALYZE SELECT * FROM articles ORDER BY articles.id DESC NULLS LAST; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Gather Merge (cost=12167.24..13390.38 rows=10636 width=1588) (actual time=158.749..188.486 rows=18081 loops=1) Workers Planned: 1 Workers Launched: 1 -> Sort (cost=11167.23..11193.82 rows=10636 width=1588) (actual time=144.924..153.350 rows=9040 loops=2) Sort Key: id DESC NULLS LAST Sort Method: external merge Disk: 12256kB -> Parallel Seq Scan on articles (cost=0.00..3109.36 rows=10636 width=1588) (actual time=0.049..40.413 rows=9040 loops=2)
EXPLAIN演算子について
テーブルスキャン
Seq scan
インデックスを使わず。全件検索。
Index scan
インデックスを使用してスキャン。
Bitmap scan
ビットマップを使用してスキャン。
ORのときにに有効。インデックスを使って検索した結果をビットマップとして保持し、ビットマップ同士でOR演算する方式。
結合
Nested Loop
ネステッドループ結合を行う。
外部テーブルを1レコードずつ取り出し、その都度内部テーブルの全レコードとマッチングして結合していく方式。
データが小さい場合に向いている。
Merge join
ソート・マージ結合を行う。
事前に両方のテーブルを結合キーでソートし、両方のテーブルを先頭からマッチングしていく方式。→ テーブルを1回調べればよいので、テーブルの操作回数が減る。
データ量が多い場合に向いている。
Hash Join
ハッシュ結合を行う。
事前に内側テーブルのハッシュ表をメモリに読み込み、外側テーブルとハッシュ表を突き合わせて結合。→ メモリ内のハッシュ表の検索は高速。
ソートメモリが十分にある場合に向いている。
制御
・set enable_hashjoin to off を実行すると、プランナはマージ結合を選択する。
・さらに set enable_mergejoin to off を実行すると、プランナはネスト化ループ結合を選択する。
こちらのスライドで勉強させていただきました。
https://www.slideshare.net/MikiShimogai/postgre-sql-explain
