SQL EXPLAIN の出力は、異世界の文字のように見えなくてもよい

あなたは実行しています EXPLAIN 遅いクエリを実行しています。データベースはあなたにこのような結果を返します。

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

あなたはそれを10秒間見つめます。ランダムにインデックスを追加します。アプリを再起動し、願いを抱きます。これはデバッグ戦略ではありません — これは追加ステップを含む迷信です。

EXPLAINは実際にあなたに具体的で行動可能な情報を伝えています。ここにその読み方を示します。

EXPLAIN と EXPLAIN ANALYZE：どちらを使うべきか

無地 EXPLAIN あなたにプランナーが 意図していることを示します。 クエリを実行しません — ただプランと推定コストを表示します。速いですが、推定は間違っている可能性があります。

EXPLAIN ANALYZE 実際にクエリを実行し、実際のタイムスタンプを追加します。あなたはプランと実際のデータを両方取得できます。

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

の BUFFERS オプションはPostgreSQL専用で、バッファヒット/ミスのカウントを追加 — I/O問題の診断に役立ちますが、今は無視してください。

一つの注意点： EXPLAIN ANALYZE 実際にクエリを実行します。テーブルが大きい場合、トランザクション内でラップし、ロールバックしてください： DELETE または UPDATE、トランザクションに包み、ロールバックします：

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

コスト数字：それらが意味するものと意味しないもの

プラン内のすべてのノードは (cost=X..Y rows=N width=W)を示しています。人々はこれらの数字を見て、ミリ秒だと誤解します。それは間違いです。

• cost=X..Y — Xは最初の行が返される前に実行される開始コスト（作業）、Yはすべての行を処理するための総コストです。単位は任意の「ページコスト」であり、ディスクページ読みに比例していますが、PostgreSQLのコスト定数によって調整されています。1.0のコストは1つの順序ページ読みを意味します。
• rows=N — プランナーの推定行数です。テーブルの統計情報が古ければ、非常に誤りが生じる可能性があります。
• width=W — 平均行サイズ（バイト）。行が広いと、結合やソートが遅くなります。

を実行すると、各ノードに EXPLAIN ANALYZEが追加されます。これらは (actual time=X..Y rows=N loops=L)ミリ秒です。推定と実際の行数の大きなギャップは、プランナーが悪い決定をした最も信頼できるサインです。 一貫しているからです — 幾何学的に。それと比較して ミリ秒。推定行数と実際の行数の大きなギャップは、プランナーが誤った決定をした最も信頼できるサインです。

注釈付きEXPLAIN ANALYZEのウォークスルー

導入時の同じクエリを、この度は ANALYZE と、各行を説明しています：

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

行動項目はすぐに見えてきます： Rows Removed by Filter: 9000 全テーブルスキャンは「ここにインデックスが必要」というパターンの最も一般的なパターンです。

実際に遭遇するノードタイプ

Seq Scan

テーブル内のすべての行をヒープ順に読みます。人々はこれを見るとすぐにインデックスを追加したいとします。しかし、Seq Scanは常に間違っているわけではありません — フィルタが20%以上の行をマッチする場合、プランナーはランダムなインデックス検索が遅いと正確に判断します。500行のテーブルに対してSeq Scanは問題ありません。しかし、1000万行のテーブルで非常に選択的なフィルタがある場合、Seq Scanは問題です。

Index Scan

B-treeインデックスを使用してマッチする行を検索し、それぞれの行をヒープから取得します。ヒープ取得が高コスト部分です — 各行はランダムI/Oです。フィルタが選択的（つまり、小さな割合の行を取得する）場合に適しています。

Index Only Scan

クエリはインデックスから完全に答えられ、ヒープにアクセスしなくてよいです。これは、クエリの SELECT と WHERE 句に含まれるすべての列がインデックスにカバーされている必要があります。最も速いスキャンタイプです — これを見たら、インデックス設計が適切に機能していることを意味します。

Bitmap Heap Scan

中間の方法です。プランナーはヒープページにマッチする行を含むビットマップを作成（ Bitmap Index Scanを介して）、それらのページのみを順に読み取り、ランダムI/Oを減らします。インデックススキャンが多くのランダム読みを必要とするが、シーケンススキャンが無駄な行をスキャンする場合に見られます。

Hash Join

小さい入力からハッシュテーブルを作成し、大きな入力の各行に対してプローブします。大きな結合に便利で、ソート順が有用でない場合に適しています。ハッシュノードに Batches: > 1 が存在する場合、ハッシュテーブルがディスクにスパイルしたことを意味します。 work_memを超過したためです。そのセッションの work_mem を上げることで修正できます。

Nested Loop

外側の各行に対して、内側側（通常はインデックスを介して）をプローブします。外側が非常に小さい場合に優れています — O(n)ではなくO(n log n)です。しかし、両側が大きい場合、内側スキャンが外側の各行に対して1回ずつ実行され、非常に遅くなります。Nested Loopに loops=50000が存在する場合、内側スキャンは50,000回実行されています。

Merge Join

両方の入力が結合キーでソートされているため、プランナーはそれらを並行して歩きます。効率的ですが、ソートが最初に必要です。両側がすでに結合キーにインデックスを持っている場合、またはプランナーがソートノードの方がハッシュよりも安いと判断した場合に見られます。

プランナーがSeq Scanを選択する理由

これは人々にとって最も混乱を引き起こします。あなたはインデックスを持っています。クエリが遅いです。EXPLAINがSeq Scanを示します。

プランナーは列統計（ pg_statisticに保存され、 ANALYZE によって更新されます）を使用して、フィルタがどのくらいの行を返すかを推定します。30%の行がマッチすると推定された場合、Seq Scanは300,000のランダムインデックス検索よりも確かに安いです。ランダムI/Oは高価です。

プランナーがSeq Scanを好む閾値は、ハードウェアやコスト定数によって約10–20%の選択性です。プランナーの推定と現実を比較できます：

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

プランナーが8,000行を推定したが、実際には80行しかない場合、統計情報が古くなっている問題です。実行 ANALYZE orders; を実行し、プランを再確認してください。これは悪いプランを修正する頻度が、インデックスを追加するよりも高いです。

JSON形式でのEXPLAINの読み方

PostgreSQLはプランをJSON形式で出力でき、プログラム的に解析またはツリー表示で探索しやすくなります：

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

JSON出力は濃密です。それを IO Tools’ JSON Formatterに貼り付けると、ナビゲート可能なツリーになります — 複雑なクエリや複数のサブクエリやCTEを持つプランを扱う場合に便利です。

実用的なデバッグワークフロー

クエリが遅い場合、この順序に従って、推測するのではなく：

1. でWindows上で EXPLAIN (ANALYZE, BUFFERS) 実際のクエリで、生産環境に近いパラメータ（ $1 プレースホルダーではなく、実際の値）を使用して実行します。
2. 最もコストの高いノードを見つけてください。 最も高い actual time、最高コストの見積もりではないことに注意してください。それらは異なることがあります。
3. そのノードでの推定と実際の行数を比較してください 10倍以上の差異がある場合、プランナーは悪質な情報に基づいていました。まず ANALYZE <table>; を実行してください。
4. 大きなテーブルで選択的なフィルタを持つSeq Scanを見つけてください。 Rows Removed by Filter: <large number> Seq Scanの直下に位置するものがインデックス候補です。
5. ハッシュノードに Batches > 1. が存在するか確認してください。存在する場合、結合がディスクにスパイルしています。そのセッションの work_mem を増加させ、再テストしてください。
6. ネストループに高いループカウントがあるか確認してください。 ループカウントが数千ある場合、内側スキャンが過負荷です。内側テーブルの結合列にインデックスを設けることが一般的に解決します。

新しいインデックスを書く前に、すべてのクエリを IO Tools’ SQL Formatter を通じて実行してください — 読みやすいクエリは分析しやすく、場合によっては問題自体を完全に除去します。

プランを理解することが最初のステップであり、インデックスではありません。EXPLAIN出力が読めるようになると、推測する時間を減らし、実際に効果的な変更を実行する時間を増やすことができます。

あなたも好きかもしれません