遅いSQLの調査でEXPLAIN ANALYZEを取ったものの、出力のどこを疑えばいいのか決められないことがあります。costが10万を超えているから遅いのだと考えてしまう、最上位ノードのactual timeが大きいので結合処理が原因だと決めつけてしまう、loopsが100と出ているのに行数の合計を読み間違える。こうした読み違いが起きると、インデックスを追加しても実行時間が変わらないまま時間が過ぎます。出力を読む順番と、各項目が何を表しているのかを先に押さえてください。
この記事は「PostgreSQL EXPLAIN ANALYZEの見方」の一部を、話題を絞って書き直したものです。BUFFERSを付けてI/O律速かCPU律速かを判定する手順や、遅いSQLを早期に検知する運用設計は、ブログの完全版にまとめています: PostgreSQL EXPLAIN ANALYZEの見方
まず推定と実測の差を見る
EXPLAIN ANALYZEが役に立つのは、プランナの見積もりと実際に起きたことを並べて比較できるからです。プランナはpg_statisticの統計情報から返却行数を推定し、その推定行数をもとに結合方式やインデックスの利用を決めます。推定が外れると、その先の選択も連鎖して外れます。だから最初に確認するのはrows=とactual rows=の比です。
各項目が示すもの
cost=A..BのAは最初の1行を返すまで、Bは全行を返し終わるまでの推定コストです。単位は秒ではなく、ページ読み取り1回を1.0とした相対値で、seq_page_cost=1.0、random_page_cost=4.0、cpu_tuple_cost=0.01などから算出されます。候補プランを比べるためだけの数値なので、costの絶対値から遅さを判断できません。
SSDやEBS gp3ではランダムアクセスのペナルティが実態より大きく見積もられがちです。そのためrandom_page_costを下げてインデックススキャンを選びやすくする調整がよく使われます。設定はインスタンス全体に適用されるため、影響範囲を確認してから変更してください。
rows=はloops1回あたりの推定行数、width=は1行あたりの推定バイト数です。widthが大きすぎる場合は、必要のないカラムまでSELECTしている可能性を疑います。actual time=X..Yは実測のミリ秒で、Xが最初の1行、Yが全行を返し終わるまでの値になります。
loopsが1より大きいノードでは、actual timeとactual rowsがどちらも1回あたりの平均値です。ここは見落としやすい点で、loops=100でactual rows=1なら合計100行を返しています。バージョンによっては平均値が小数で表示されるため、整数に丸められているかどうかもバージョン差として意識しておきます。
実行時間は自分と子ノードの累計
ノードのactual timeには、子ノードの時間が含まれています。
Hash Join (actual time=120.500..980.200 rows=50000 loops=1)
-> Seq Scan on orders (actual time=0.015..300.100 rows=500000 loops=1)
-> Hash (actual time=119.800..119.810 rows=1000 loops=1)
-> Seq Scan on customers (actual time=0.010..60.200 rows=1000 loops=1)
Hash Joinの980msには、子のSeq Scanの300msとHash構築の120msが入っています。ノード自身が消費した時間は累計から子の累計を引いた差分なので、この例では980 − 300 − 120 = 560ms程度がHash Join自身の処理時間です。
評価は末端の行から上へ累計時間をたどり、親と子の差が大きいノードを探して原因と見ます。loopsが1より大きいノードは表示値が1回あたりなので、親の累計と比べるときはactual time × loopsに換算してから当ててください。
Parallel Seq ScanなどのParallel系ノードでは、Workers PlannedとWorkers Launchedも確認します。LaunchedがPlannedより少なければmax_parallel_workersが枯渇している状態で、プランナの想定より遅くなります。
元記事で扱っている内容
- EXPLAIN ANALYZEを3つの観点から読んで原因の大半を特定する手順
- 上から順に確認していく原因の切り分け手順
- BUFFERSオプションの出力から、I/O律速かCPU律速かを見分ける方法
- EXPLAIN ANALYZEに関するよくある質問への回答
- 遅いSQLを早い段階で検知するための運用設計