観測・診断図鑑 · 基礎から応用へ

EXPLAIN ANALYZEの読み方

推定と実測、行数と繰り返し回数を区別し、PostgreSQLの実行計画から遅さの仮説を立てる。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. まず、推定と実行を分ける
  2. 行数のずれを下から探す
  3. BUFFERSと合わせて仮説を絞る
  4. 比較するときの注意
  5. この記事をロゲイアに質問する理解を確かめる

まず、推定と実行を分ける

EXPLAINは、PostgreSQLが選んだ走査・結合・集約などの処理順序を木として表示する。ANALYZEを付けると対象のSQLを実際に実行し、各ノードで処理した行数や時間も記録する。読取専用の表示コマンドだと思い込まず、最初は小さなSELECTから試そう。

costの値はプランナの比較に使う単位であり、ミリ秒ではない。開始コストと総コストを実測時間に直接換算したり、別のサーバーの値と単純比較したりしない。対象SQL・バインド値・データ量・設定をそろえると、改善前後の違いを考えやすい。

行数のずれを下から探す

ツリーの下側から、推定rowsとactual rowsを比べる。たとえば1行の見積もりに対して24行が出れば、そのずれが上の結合方式や繰り返し回数の選択に影響していないかを見る。索引を追加する前に、条件の偏りや列間の相関、統計の鮮度を疑う。

loopsが複数あるノードでは、表示される実測行数と時間は1回あたりの平均である。actual rows=4、loops=100なら、合計の出力行数はおおむね400行になる。親ノードの時間は子の処理を含むため、各行の時間を足して全体時間を作ることはできない。

BUFFERSと合わせて仮説を絞る

次の例はcustomer_idで絞るSELECTの調べ方であり、表示結果や速度を保証するものではない。先に計画の形を読み、実測が必要なときだけANALYZEを使う。TIMING OFFではノード別の時刻計測を省けるが、行数や全体の実行時間は確認できる。

shared hitは共有バッファ内で見つかったブロックへのアクセス、shared readはそこへ読み込んだブロックへのアクセスを示す。readが多いことだけで物理ディスクが遅いとは断定できない。OSのキャッシュ、I/O時間の計測設定、同時負荷も確認する。

sql
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, total
FROM orders
WHERE customer_id = 42;

比較するときの注意

実行計画の観察にも負荷があり、1回目と2回目ではキャッシュの状態が変わる。同じ条件で複数回調べ、ばらつきを残そう。平均だけでなく、遅い実行に共通する入力や待機がないかを確かめると、索引・SQL・設定のどこを変えるべきか判断しやすい。

ANALYZE付きのINSERTやUPDATEは実際に書き込む。トランザクションで戻せる変更もあるが、シーケンスや外部処理など、ROLLBACKですべてを無かったことにできるとは限らない。実行計画は原因を示す手掛かりであり、単独で対処を決定するものではない。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 12

学習コースから次を選ぶ