PostgreSQL 基礎講座 #5 EXPLAIN の読み方 — 実行計画でクエリを診断する

第 4 回で「インデックスが効いているか、効いていないならなぜかは実行計画で確かめる」と先送りにしました。今回がその道具です。EXPLAIN は PostgreSQL がクエリをどう実行するか(あるいはどう実行したか)を見せてくれるコマンドで、クエリ性能に関するほとんどすべての議論を、推測から事実に変えてくれます。

EXPLAIN vs EXPLAIN ANALYZE #

-- 計画だけを見る(実行しない)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- 実際に実行して実測値も一緒に見る
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

EXPLAIN はプランナの予想だけを見せ、EXPLAIN ANALYZE はクエリを実際に実行して予想と実測を並べて見せます。診断にはほぼ常に ANALYZE が必要です。ただし実際に実行するので、UPDATE・DELETE にかけるとデータが本当に変わります。書き込みクエリを診断するときは BEGIN; EXPLAIN ANALYZE ...; ROLLBACK; で包む習慣が安全です。

計画ツリー: 内側(インデントの深いところ)から読みます #

Nested Loop  (cost=0.57..205.31 rows=50 width=52) (actual time=0.041..0.318 rows=48 loops=1)
  ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=24)
        (actual time=0.019..0.020 rows=1 loops=1)
        Index Cond: (id = 42)
  ->  Index Scan using idx_orders_user_id on orders o  (cost=0.29..196.51 rows=50 width=36)
        (actual time=0.018..0.284 rows=48 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.361 ms

実行計画はツリーで、一番インデントの深いノードが先に実行され、外側へ結果を渡していきます。 上の例なら「users を PK インデックスで 1 件見つけ、orders を user_id インデックスで 48 件見つけ、Nested Loop で結合した」と読みます。各ノードのカッコ 2 組が核心です。前の cost=..., rows=... はプランナの予想、後ろの actual time=..., rows=... は実測です。cost はミリ秒ではなく相対的なコスト単位なので、絶対値よりは「どのノードが全体コストの大半を食っているか」を見る用途です。

スキャンノード 4 種類 #

Seq Scan 自体は罪ではありません。第 4 回で見たとおり、条件が行を十分に絞れなければ Seq Scan が最善です。問題は「数百万行から数十件を探すクエリなのに Seq Scan」の場合で、そのときは第 4 回のチェックリスト(先頭カラム、カラム加工、型不一致)に戻ります。

一番重要な比較: 予想 rows vs 実測 rows #

実行計画の読み方で 1 つだけ選ぶならこれです。予想 rows と実測 rows が桁単位でずれているノードを探してください。 プランナは統計を根拠に結合方式やスキャン方式を選ぶので、「1 行の予想が実際は 10 万行」なら、そのノードより上のすべての選択が間違った前提の上に立っています。処方はまず統計の更新です。

ANALYZE orders;  -- 統計の再収集(普段は autovacuum が自動でやりますが、大量ロード直後は手動で)

大量 INSERT やマイグレーション直後の突然の性能低下は、この統計の古さが原因のことが多いです。それでもずれるなら、相関の強いカラムの組み合わせなど、もっと深い話に入りますが、それは実践編の性能診断で続けます。

BUFFERS: 読み取り量を見る習慣 #

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

BUFFERS オプションは、各ノードが読んだブロック数を見せてくれます。shared hit はメモリ(キャッシュ)から、read はディスクから読んだ量です。同じクエリがある日は速く、ある日は遅いなら、計画が変わったのではなくキャッシュのヒット率が変わっただけかもしれず、その区別をこの数字がしてくれます。時間と一緒に読み取り量を見る習慣をつけると、「なぜ遅いのか」の解像度が一段上がります。

実務ではこの診断をクエリ 1 つずつではなく「何が遅いかを見つける → EXPLAIN で掘る」の順のルーチンとして回しますが、その前段(pg_stat_statements)は実践編の主題です。

まとめ #

• EXPLAIN は予想、EXPLAIN ANALYZE は実測です。診断は ANALYZE が基本で、書き込みクエリは BEGIN/ROLLBACK で包みます。
• 計画ツリーはインデントの深い内側から読みます。cost は相対単位なので「どこが大半を食っているか」で見ます。
• Seq Scan は判断の結果でありえます。「絞り込むクエリなのに Seq Scan」のときだけ第 4 回のチェックリストに戻ります。
• 1 つだけ見るなら、予想 rows と実測 rows の桁の差です。大きくずれていたら、まず ANALYZE で統計を更新します。
• BUFFERS で読み取り量まで見ると、「キャッシュのおかげで速かった」と「計画が良い」を区別できます。次回はトランザクションと MVCC です。