第8回｜実行計画を読む（虫眼鏡）

クエリが遅いのは「なんとなく」ではない。EXPLAIN で計画を開き、統計とコストから遅さの理由を言葉にする回。

この回のねらい

同じ結果を返すクエリでも、PostgreSQL が内部でどう処理するかによって速度は桁で変わる。その「どう処理するか」を決めるのがプランナ（計画立案器）であり、決めた結果が実行計画（プラン）である。この回では EXPLAIN (ANALYZE, BUFFERS) の出力を1行ずつ読み、遅さの原因を統計情報とコストの言葉で説明できるようになる。これは本講座の卒業要件「遅いクエリの原因を計画から特定し、改善して before/after を示す」の中核である。

到達目標

• EXPLAIN (ANALYZE, BUFFERS) の各ノードの cost / rows / width / actual time / loops / Buffers を読める。
• スキャン方式（Seq / Index / Index Only / Bitmap）と結合方式（Nested Loop / Hash / Merge）が「いつ選ばれるか」を説明できる。
• Seq Scan が正しい場面（大部分を読む・小さいテーブル）と、索引が使われない理由（関数適用など）を言語化できる。
• 見積もり行数（estimated rows）と実測行数（actual ... rows）の乖離を見つけ、統計情報（ANALYZE・ヒストグラム）の観点で原因を説明できる。

前提と準備

第1回で作った PostShop スキーマ（orders 約100万行、order_items 約300万行ほか）が投入済みであることを前提とする。挙動は PostgreSQL 16 系を前提に記述する。

計画は統計情報に基づいて立てられるため、まず統計を最新化しておく。

-- 全テーブルの統計を収集（プランナの見積もりの土台）
VACUUM ANALYZE;

EXPLAIN の主なオプションは次の3つを押さえておけばよい。

ANALYZE を付けるとクエリは実際に実行される。SELECT は無害だが、UPDATE / DELETE / INSERT を計測するときは副作用が残る。トランザクションで囲んで巻き戻すこと。

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE orders SET status = 'paid' WHERE id = 1;
ROLLBACK;   -- 計測はできるが変更は捨てる

I/O の実時間も見たいときは、セッションで次を有効にすると Buffers に I/O Timings が加わる。

SET track_io_timing = on;

この回で新しく出てくる用語

計画を読むときに出てくる、スキャン方式・結合方式以外の語をここで押さえる。方式名そのものは本編の表で扱う。回をまたいで使う語は用語集にもまとめている。

本編

EXPLAIN の基本: 何が表示されるか

次のクエリは「2025-06-01 の注文件数」を数える。ただし ordered_at に date_trunc を適用しているのが後で効いてくる。

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders
WHERE date_trunc('day', ordered_at) = DATE '2025-06-01';

出力イメージ（実測値は環境依存。例:）。

 Aggregate  (cost=20834.00..20834.01 rows=1 width=8) (actual time=182.334..182.335 rows=1 loops=1)
   Buffers: shared hit=64 read=8306
   ->  Seq Scan on orders  (cost=0.00..20821.00 rows=5000 width=0) (actual time=0.045..181.002 rows=1370 loops=1)
         Filter: (date_trunc('day'::text, ordered_at) = '2025-06-01 00:00:00+09'::timestamp with time zone)
         Rows Removed by Filter: 998630
         Buffers: shared hit=64 read=8306
 Planning Time: 0.120 ms
 Execution Time: 182.360 ms

各要素の読み方は次のとおり。

• 木構造: -> のインデントが深いノードほど内側で、内側から実行される。ここでは Seq Scan（内側）→ Aggregate（外側）の順に処理される。上の行ほど後に実行される、と読む。
• cost=20834.00..20834.01: プランナの見積もりコスト。起動コスト..総コスト。単位は任意（既定では順次1ページ読みが 1.0）。起動コストは「最初の1行が返るまで」、総コストは「最後まで」。ミリ秒ではないので実測とは直接比べない。
• rows=1: このノードが返すと見積もった行数。
• width=8: 1行あたりの平均バイト数（見積もり）。
• actual time=182.334..182.335: 実測時間（ミリ秒）。起動..総。ANALYZE を付けたときだけ出る。
• rows=1 loops=1: 実測の出力行数と、このノードが実行された回数（loops）。
• Filter と Rows Removed by Filter: 998630: 99.8万行を読んで条件で捨てた、という意味。関数適用で索引が使えず、全行を走査したことがここに現れている。
• Buffers: shared hit=64 read=8306: 共有バッファ（＝ PostgreSQL がディスクのページを載せておく共有メモリ上のキャッシュ）に命中したブロックが64、ディスクから読んだブロックが8306。1ブロック=8KB。read が大きいほど I/O が重い。BUFFERS を見ずに「遅い」を語らない。

ここで Seq Scan の見積もり rows=5000 と実測 rows=1370 がずれている。この乖離が診断の入口になる（後述）。

スキャン方式: Seq / Index / Index Only / Bitmap

1つのテーブルから行を取り出す方法は主に4つある。どれが選ばれるかは「何行取り出すか」と「索引があるか」でほぼ決まる。この「条件を満たす行が全体に占める割合」を選択率と呼び、以降しばしば使う。選択率が低い（＝少数行しか返らない）ほど索引が有利になる。

重要なのは、Seq Scan は「遅い方式」ではないという点である。テーブルの大部分を返すクエリでは、索引をたどってヒープをランダムアクセスするより、全ページを順に読む Seq Scan の方が速い。索引が「使われない」のはプランナがサボっているのではなく、その方が安いと見積もったからであることが多い。

Bitmap は Index Scan と Seq Scan の中間で、次のように2段構えで現れる。

 Bitmap Heap Scan on orders  (cost=...) (actual ...)
   Recheck Cond: (customer_id = 12345)
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=...) (actual ...)
         Index Cond: (customer_id = 12345)

内側の Bitmap Index Scan で「どのページに該当行があるか」のビットマップを作り、外側の Bitmap Heap Scan でそのページ群をまとめて読む。中程度の件数（Index Scan だとランダム I/O が増えすぎ、Seq Scan だと読みすぎ）で選ばれやすい。

結合方式: Nested Loop / Hash / Merge

2つのテーブルを結合する方法も主に3つある。

Nested Loop では loops の掛け算を必ず意識する。EXPLAIN の actual time と rows は1ループあたりの平均で表示されるため、内側ノードの実コストは「表示値 × loops」になる。

 ->  Index Scan using order_items_pkey on order_items oi
       (cost=0.43..8.45 rows=3 width=18) (actual time=0.005..0.007 rows=3 loops=21)

この内側ノードは loops=21、1ループ0.007ms・3行なので、実際には合計で 21 × 0.007 ≈ 0.15ms・21 × 3 = 63行 処理している。外側が20行程度なら Nested Loop で十分速いが、外側が10万行になれば「10万回の内側検索」になり、Hash Join に負ける。loops が大きい Nested Loop は要注意、と読めるようになる。

コストと行数見積もり

プランナは各計画の総コストを見積もり、最も安いものを選ぶ。コストは主に「読むページ数 × ページコスト」＋「処理する行数 × CPU コスト」で組み立てられる。ページを1枚順に読むコストを 1.0 とし、ランダム読みは random_page_cost（既定4.0）で重み付けする。だから同じ件数でも、順読みできる Seq/Bitmap は安く、ランダムアクセスの Index Scan は高く見積もられる。

width（1行の平均バイト数）も無視できない。返す列が多い・長いほど width が増え、ソートやハッシュに必要なメモリが増える。SELECT * を避けるだけでコストが下がることがある。

コストはあくまで相対比較のための数字であり、ミリ秒ではない。速さの絶対値は actual time と Buffers で見る。

統計情報とヒストグラム

プランナが rows を見積もる根拠が統計情報である。ANALYZE が列ごとにサンプリングし、pg_statistic（人間向けビューは pg_stats）に格納する。主な中身は次の3つ。

• n_distinct: 相異なる値の数（等値条件の選択率に使う。例: customer_id の distinct が5万なら = 12345 は約 1/50000）。
• most_common_vals / most_common_freqs: 頻出値とその頻度（偏った等値条件に使う）。
• histogram_bounds: 値をほぼ等頻度に区切った境界（範囲条件 >= < BETWEEN の選択率に使う）。

中身は次のように覗ける。

SELECT attname, n_distinct, null_frac,
       array_length(histogram_bounds::text::text[], 1) AS hist_buckets
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('ordered_at', 'customer_id', 'status');

出力イメージ。

  attname    | n_distinct | null_frac | hist_buckets
-------------+------------+-----------+--------------
 ordered_at  |     -1     |     0     |     101
 customer_id |   50000    |     0     |     101
 status      |     6      |     0     |
(3 rows)

ordered_at の n_distinct = -1 は「ほぼ全行が異なる値」を表す。histogram_bounds は既定で100バケット（default_statistics_target = 100）に区切られ、ordered_at >= '2025-06-01' AND < '2025-06-02' のような範囲条件はこのヒストグラムから正確に見積もれる。一方 status は値が6種類しかないので most_common_vals 側で扱われ、ヒストグラムは持たない。

見積もり誤差の原因と診断

rows（見積もり）と actual ... rows（実測）の乖離こそ、遅いクエリ診断の一番の手がかりである。乖離が大きいと、プランナは間違った前提で方式を選ぶ（少数行だと思って Nested Loop を選んだら実は大量行だった、など）。主な原因は次のとおり。

最初の例で Seq Scan が rows=5000（=100万×約0.5%）と見積もったのは、date_trunc('day', ordered_at) という式に対して分布統計が無く、等値の既定選択率にフォールバックしたためである。列そのものに条件を書けばヒストグラムが効き、見積もりは実測に近づく。これを次のハンズオンで確かめる。

ハンズオン

意図的に遅いクエリを渡し、EXPLAIN (ANALYZE, BUFFERS) で原因を特定し、書き換えや索引で改善し、before/after のプランと実測を並べる。これは卒業要件そのものの練習である。実測ミリ秒は環境依存なので「例:」として読むこと。

課題1: 関数適用で索引が効かない WHERE を直す

やること: 「2025-06-01 の注文件数」を、まず date_trunc を使った本編のクエリで計測し、索引が使われない理由を述べたうえで、索引が効く形に書き換えて before/after を比較する。

想定解答

before は本編の計画そのもので、Seq Scan + Rows Removed by Filter: 998630 が原因である。ordered_at に索引があっても、date_trunc('day', ordered_at) という式には使えない（索引は列 ordered_at の値で並んでいて、date_trunc を適用した値では並んでいない）。

改善は2手ある。(a) 条件を「列そのものの範囲」に書き換える。(b) 範囲条件を索引で引けるよう索引を作る。

-- (b) ordered_at に索引を作る（第7回参照）
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at);

-- (a) 同じ意味を「関数を使わない範囲条件」で書く
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*)
FROM orders
WHERE ordered_at >= DATE '2025-06-01'
  AND ordered_at <  DATE '2025-06-02';

after の出力イメージ（例:）。

 Aggregate  (cost=48.55..48.56 rows=1 width=8) (actual time=0.512..0.513 rows=1 loops=1)
   Buffers: shared hit=17
   ->  Index Only Scan using idx_orders_ordered_at on orders  (cost=0.42..45.12 rows=1370 width=0) (actual time=0.028..0.402 rows=1370 loops=1)
         Index Cond: ((ordered_at >= '2025-06-01 00:00:00+09'::timestamptz) AND (ordered_at < '2025-06-02 00:00:00+09'::timestamptz))
         Heap Fetches: 0
         Buffers: shared hit=17
 Planning Time: 0.210 ms
 Execution Time: 0.535 ms

before / after の比較。

要点は、書き換えで見積もり誤差も消えたこと（5000→1370）。列そのものの範囲条件にしたことで、範囲用のヒストグラムが効くようになった。Heap Fetches: 0 は、count(*) に必要な列が索引だけで足り、ヒープを触らず完結したことを示す（Index Only Scan の狙いどおり）。

課題2: JOIN 集計の Seq Scan を索引で潰す

やること: 「特定顧客（customer_id = 12345）の注文ごとの合計金額」を出す。まず索引なしで計測し、遅い箇所を特定して索引を追加し、before/after を並べる。

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, sum(oi.qty * oi.unit_price) AS amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.customer_id = 12345
GROUP BY o.id;

想定解答

before の出力イメージ（例:）。order_items 側は主キー (order_id, product_id) の先頭列が order_id なので索引で引けている。問題は orders 側に customer_id の索引がなく、100万行を Seq Scan していることにある。

 HashAggregate  (cost=20896.50..20897.10 rows=60 width=40) (actual time=201.418..201.470 rows=21 loops=1)
   Group Key: o.id
   Buffers: shared hit=8 read=8370
   ->  Nested Loop  (cost=0.43..20896.20 rows=60 width=18) (actual time=15.204..201.180 rows=64 loops=1)
         Buffers: shared hit=8 read=8370
         ->  Seq Scan on orders o  (cost=0.00..20821.00 rows=20 width=8) (actual time=15.010..200.640 rows=21 loops=1)
               Filter: (customer_id = 12345)
               Rows Removed by Filter: 999979
               Buffers: shared read=8306
         ->  Index Scan using order_items_pkey on order_items oi  (cost=0.43..3.72 rows=3 width=18) (actual time=0.020..0.024 rows=3 loops=21)
               Index Cond: (order_id = o.id)
               Buffers: shared hit=8 read=64
 Planning Time: 0.204 ms
 Execution Time: 201.520 ms

読み解き。外側 Seq Scan on orders が Rows Removed by Filter: 999979＝99.9万行を捨てており、read=8306 ブロックの I/O をここで消費している。内側 Index Scan ... loops=21 は1ループ0.024msで健全（合計でも約0.5ms）。つまり遅さの主因は外側の Seq Scan である。

customer_id に索引を足す。

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

after の出力イメージ（例:）。

 HashAggregate  (cost=249.35..249.95 rows=60 width=40) (actual time=0.298..0.334 rows=21 loops=1)
   Group Key: o.id
   Buffers: shared hit=95
   ->  Nested Loop  (cost=0.86..248.75 rows=60 width=18) (actual time=0.041..0.235 rows=64 loops=1)
         Buffers: shared hit=95
         ->  Index Scan using idx_orders_customer_id on orders o  (cost=0.43..79.20 rows=20 width=8) (actual time=0.028..0.060 rows=21 loops=1)
               Index Cond: (customer_id = 12345)
               Buffers: shared hit=23
         ->  Index Scan using order_items_pkey on order_items oi  (cost=0.43..3.72 rows=3 width=18) (actual time=0.005..0.007 rows=3 loops=21)
               Index Cond: (order_id = o.id)
               Buffers: shared hit=72
 Planning Time: 0.312 ms
 Execution Time: 0.362 ms

before / after の比較。

結合の形（Nested Loop）は前後で同じで、変わったのは外側 orders の取り出し方だけ。遅さの主因は結合ではなく片側のスキャンだった、と計画から言い切れる。

逆に索引が不要な場面も確認する。 同じ結合でも「2025年通年の地域別売上」のように大部分の注文を読む集計では、話が逆になる。

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.region, sum(oi.qty * oi.unit_price) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN customers  c  ON c.id = o.customer_id
WHERE o.ordered_at >= DATE '2025-01-01' AND o.ordered_at < DATE '2026-01-01'
GROUP BY c.region;

このクエリは order_items のほぼ全行（約300万）が集計対象になる。計画は次のようになりやすい。

 HashAggregate
   ->  Hash Join   (Hash Cond: oi.order_id = o.id)
         ->  Seq Scan on order_items oi
         ->  Hash
               ->  Hash Join (Hash Cond: o.customer_id = c.id)
                     ->  Seq Scan on orders o     (Filter: 通年の範囲)
                     ->  Hash -> Seq Scan on customers c

ここで order_items を Nested Loop ＋ 索引で300万回引いたら、かえって遅い。大量行をまとめて処理するなら Seq Scan ＋ Hash Join が正解であり、プランナが索引を使わないのは正しい判断である。「索引が使われない＝悪」ではないことを、この2つのクエリの対比で体得してほしい。

よくあるつまずきと対処

エラーではないが典型的な誤り。次の2つは同じ意味なのに、上は索引が効かず下は効く。

-- 索引が効かない: 列に関数を適用している
WHERE date_trunc('day', ordered_at) = DATE '2025-06-01';

-- 索引が効く: 列そのものへの範囲条件
WHERE ordered_at >= DATE '2025-06-01' AND ordered_at < DATE '2025-06-02';

到達度チェックリスト

• EXPLAIN 出力の cost=起動..総 / rows / width / actual time / loops / Buffers を、それぞれ何の値か説明できる。
• 入れ子ノードが内側から実行されること、actual time・rows が1ループあたりで、実コストは × loops になることを説明できる。
• Seq / Index / Index Only / Bitmap の4スキャン方式が、それぞれいつ選ばれるかを言える。
• Nested Loop / Hash / Merge の3結合方式が、それぞれいつ選ばれるかを言える。
• Seq Scan が正しい場面（大部分を読む・小テーブル・大量結合）を1つ挙げて説明できる。
• 見積もり rows と actual rows の乖離を見つけ、統計が古い／関数適用／相関のどれかに原因を切り分けられる。
• 遅いクエリを before/after の計画と実測で示し、改善理由を言葉にできる。

宿題（次回までの自習）

1. order_items に対する「よく使う集計クエリ」を1つ選び、EXPLAIN (ANALYZE, BUFFERS) を取って、各ノードのスキャン方式・結合方式・Buffers を1行ずつ注釈するメモを作る。
2. 課題1で作った idx_orders_ordered_at を DROP INDEX してから同じ範囲クエリを計測し、計画が Index Only Scan から Seq Scan に変わること、Buffers の read が跳ね上がることを確認する（確認後は索引を作り直す）。
3. region と status のように相関しそうな2列で AND 条件のクエリを作り、見積もり rows と実測を比べる。ずれていたら CREATE STATISTICS で拡張統計を作り、ANALYZE 後に見積もりが改善するか観察する。

次回への接続

計画から「どの索引を足すべきか」まで読めるようになった。索引そのものの内部構造（B-tree・式索引・複合索引の列順・Index Only Scan の可視性マップ）は第7回で扱う。また、どのクエリを最初に虫眼鏡で見るべきか——本番で遅い順に候補を挙げる定点観測は第12回の pg_stat_statements が担う。次回第9回は、計画どおり動くクエリが「同時に」走ったときに何が起きるか（トランザクションと並行制御）へ進む。

参考

• PostgreSQL 公式ドキュメント「Using EXPLAIN」 https://www.postgresql.org/docs/current/using-explain.html
• 同「EXPLAIN」コマンド https://www.postgresql.org/docs/current/sql-explain.html
• 同「How the Planner Uses Statistics」 https://www.postgresql.org/docs/current/planner-stats.html
• 同「Row Estimation Examples」 https://www.postgresql.org/docs/current/row-estimation-examples.html
• 同「ANALYZE」コマンド https://www.postgresql.org/docs/current/sql-analyze.html
• 計画の可視化ツール（出力を貼ると木構造・時間・行数を図示）: explain.dalibo.com など