【PostgreSQL】EXPLAIN ANALYZEで実行計画を読む

PostgreSQL
2023.01.25

こんにちは、かつコーチです。

前回はインデックスの種類を解説しましたが、「インデックスを貼った」だけでは安心できません。

実際にそのインデックスが使われているかは、実行計画（PostgreSQLがクエリをどう処理するか事前に立てる計画）を見て確認する必要があります。

今回はEXPLAINとEXPLAIN ANALYZEの読み方を、実際のボトルネック調査の流れに沿って解説します。

EXPLAINとEXPLAIN ANALYZEの違い

EXPLAINは計画だけ、ANALYZEは実際に実行する

EXPLAINはクエリを実行せず、オプティマイザが立てた計画だけを表示します。

EXPLAIN ANALYZEは実際にクエリを実行し、計画と実測値（実行時間・実際の行数）を突き合わせて表示します。

EXPLAIN
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=64)
  Filter: (customer_id = 42)

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..1834.00 rows=12 width=64)
                    (actual time=0.021..8.412 rows=15 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 99985
Planning Time: 0.089 ms
Execution Time: 8.437 ms

EXPLAIN ANALYZEは実際にデータを変更するクエリ（INSERT・UPDATE・DELETE）でも本当に実行してしまう点に注意してください。

本番データを壊したくない場合は、BEGINでトランザクションを開始し、確認後にROLLBACKする運用が安全です。

BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status = 'shipped' WHERE order_id = 100;
ROLLBACK;

読み方の基本

cost・rows・widthの意味

cost=0.00..1834.00は、コストの推定値です。

最初の数値はそのノードの結果が最初の1行返るまでのコスト、2番目の数値は全行返し終えるまでのコストで、実時間（ミリ秒）ではなく相対的なコスト単位です。

rows=12はオプティマイザが見積もった返却行数、width=64は1行あたりの推定バイト数です。

EXPLAIN ANALYZEのactual time=0.021..8.412 rows=15 loops=1は実測値で、こちらが実際に発生した時間（ミリ秒）と行数です。

見積もりと実測のズレを見る

オプティマイザの見積もり（rows=12）と実際の行数（actual rows=15）が大きくずれている場合、統計情報が古い可能性があります。

-- テーブルの統計情報を更新する
ANALYZE orders;

見積もりと実測が数倍〜数十倍ずれているようなら、ANALYZEの実行やdefault_statistics_target（統計情報のサンプリング精度を決める設定）の見直しを検討してください。

代表的なノードの種類

ノード意味着目点
Seq Scanテーブル全体を順に読む全表走査大きいテーブルで頻発していれば要注意
Index Scanインデックスを使い、該当行をテーブル本体からも取得想定通りインデックスが選ばれているか
Index Only Scanインデックスだけで結果が完結するテーブル本体へのアクセスがなく高速
Bitmap Heap Scanインデックスでビットマップを作り、まとめてテーブルを読む該当行がある程度多いときに選ばれやすい
Nested Loop外側の各行に対し内側を繰り返し検索する結合方式外側の行数が少ないときに有効
Hash Join片方をハッシュテーブル化して結合する方式中〜大規模な結合で選ばれやすい

よくあるつまずきポイント・エラー対処

Before/After：インデックスを貼ったのにSeq Scanのまま

インデックスを追加してもオプティマイザが使ってくれない、という相談はよくあります。

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

-- ❌ Before：インデックスがあるのにSeq Scanが選ばれている
Seq Scan on orders  (cost=0.00..1834.00 rows=15000 width=64)
                    (actual time=0.015..7.912 rows=15234 loops=1)
  Filter: (customer_id = 42)

私が実際に踏んだのはこのケースで、原因は「テーブル全体の中で該当する行の割合が大きすぎた」ことでした。

customer_id = 42の行が全体の15%を占めていたため、オプティマイザは「インデックスで1行ずつ探すより、順番に全部読んだ方が速い」と正しく判断していたのです。

インデックスが使われないことが必ずしもバグとは限らない、という点は覚えておいてください。

-- ✅ After：本当に絞り込みたい条件で確認する（該当割合が小さいケース）
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 9999;

Index Scan using idx_orders_customer_id on orders
    (cost=0.42..8.44 rows=3 width=64)
    (actual time=0.031..0.034 rows=3 loops=1)
  Index Cond: (customer_id = 9999)

該当行が少ない条件では、同じインデックスがきちんとIndex Scanとして選ばれています。

「インデックスが使われているか」ではなく「その条件でオプティマイザが正しく判断しているか」という視点で見ることが大切です。

LIMITと組み合わせたときの罠

ORDER BYとLIMITを組み合わせたクエリでは、インデックスがソート済み順序を提供できると劇的に速くなります。

-- ❌ Before：ソート対象列にインデックスがなく、全件ソートしてからLIMIT
EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY ordered_at DESC LIMIT 10;

Limit  (cost=1943.00..1943.03 rows=10 width=64)
       (actual time=45.231..45.235 rows=10 loops=1)
  ->  Sort  (cost=1943.00..1968.50 rows=100000 width=64)
            (actual time=45.229..45.231 rows=10 loops=1)
        Sort Key: ordered_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Seq Scan on orders  (cost=0.00..1584.00 rows=100000 width=64)

-- ✅ After：ordered_atにインデックスを貼るとソート済みの順で取得できる
CREATE INDEX idx_orders_ordered_at_desc ON orders (ordered_at DESC);

EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY ordered_at DESC LIMIT 10;

Limit  (cost=0.42..0.87 rows=10 width=64)
       (actual time=0.019..0.024 rows=10 loops=1)
  ->  Index Scan using idx_orders_ordered_at_desc on orders
            (cost=0.42..4521.42 rows=100000 width=64)
            (actual time=0.018..0.021 rows=10 loops=1)

Beforeでは全100,000行をSeq ScanしてからSortしていたのに対し、Afterではインデックスが既にソート済みの順序を提供しているため、先頭10件を取得した時点で処理が終わっています。

Sort Method: top-N heapsortのようなメモリ内ソートの表示自体は珍しくありませんが、ORDER BY + LIMITが頻出するクエリでは、ソート対象列へのインデックスを検討する価値があります。

応用・一歩先の使い方

BUFFERSオプションでキャッシュヒット率を見る

EXPLAIN (ANALYZE, BUFFERS)を使うと、ディスクI/Oの発生状況まで確認できます。

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 9999;

Index Scan using idx_orders_customer_id on orders
    (cost=0.42..8.44 rows=3 width=64)
    (actual time=0.031..0.034 rows=3 loops=1)
  Index Cond: (customer_id = 9999)
  Buffers: shared hit=4

shared hit=4はキャッシュ（共有バッファ）から4ブロック読めたことを示し、shared readが出てくる場合はディスクからの読み込みが発生しています。

同じクエリを繰り返し実行してshared readがshared hitに変わっていくかを見ると、キャッシュの温まり具合を確認できます。

本番相当のデータ量・アクセスパターンで計測しないと、開発環境の小さいデータでは全く違う実行計画が選ばれることもあるので、パフォーマンス検証は可能な限り本番同等のデータ量で行うことをおすすめします。

JSON形式で出力しツールに渡す

EXPLAIN (ANALYZE, FORMAT JSON)を使うと、実行計画をJSON形式で取得できます。

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 9999;

視覚化ツール（explain.depesz.comやpev2など）に貼り付けると、ボトルネックになっているノードを色分けして確認できます。

テキストのまま読むより直感的に把握できるので、複雑な結合を含むクエリの調査で活用してみてください。

まとめ

この記事のポイント

  • EXPLAINは計画のみ、EXPLAIN ANALYZEは実際に実行して実測値まで確認できる
  • costは相対的な推定値、actual timeが実測のミリ秒
  • 見積もり行数と実測行数が大きくずれていたらANALYZEで統計情報を更新する
  • インデックスが使われないのは必ずしもバグではなく、該当割合が大きい場合は正しい判断のことがある
  • ORDER BY + LIMITはソート対象列のインデックスで劇的に改善することがある
  • BUFFERSオプションでディスクI/Oとキャッシュヒットの状況まで確認できる

次に読むべき記事

クエリ応用・設計・パフォーマンスの3カテゴリはここまでです。

インデックスの基礎的な種類は前回の記事、JSONBとGINインデックスの実践的な使い方は以下の記事も参考にしてください。

  • PostgreSQLのインデックスの種類（B-tree・GiST・GIN・BRIN）
  • JSONB型とGINインデックスで半構造化データを扱う

タグ: PostgreSQL, 上級者向け, パフォーマンス

タイトルとURLをコピーしました