0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@akira_papa_AI(あきらパパ)

PostgreSQL EXPLAIN ANALYZEでAI生成SQLの遅さを10分で絞る — 5指標の読み順

0
Posted at

PostgreSQL EXPLAIN ANALYZEでAI生成SQLの遅さを10分で絞る — 5指標の読み順

AIにSQLを書いてもらうと、動くところまではめっちゃ速いです。

でも、テストデータでは一瞬だった検索が、本番相当の件数になると数秒かかる。そこでAIへ「速くして」と返すと、CREATE INDEX が3本くらい提案される。どれを採用すればいいか分からない。

なんかこれ、実装が速くなった分だけ、判断の渋滞が後ろへ移動した感じがします。

先に結論です。

SQLを直す前に、PostgreSQLの実行計画を次の5指標の順で読みます。

  1. Execution Time
  2. 推定行数と実行行数の差
  3. loops
  4. BUFFERS
  5. Rows Removed by Filter と Sort Method

この記事では、この順番を 「実行計画の読み順」 と呼びます。SQLの遅さを、時間、見積もり差、反復、I/O、捨てた行の順で絞り、推測より先に証拠を固定する診断手順です。

読了後のゴールは、データベースの専門家になることではありません。遅い実行計画を見て、最初に調べるノードを1つ選べること。 まずはここまでで十分です。

先に知っておきたい、EXPLAINとANALYZEの違い

PostgreSQLはSQLを受け取ると、「どのテーブルから、どの順で、どんな方法で行を探すか」という計画を作ります。これが 実行計画 です。健康診断の結果みたいなもので、SQL本文だけでは見えない体の中の動きが出てきます。

EXPLAIN は、その計画と見積もりを表示します。

EXPLAIN
SELECT id, created_at, total_amount
FROM orders
WHERE status = 'pending'
  AND created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

一方、EXPLAIN ANALYZE は SQLを本当に実行し、実際の時間や行数も表示します。PostgreSQL公式ドキュメントも、ANALYZE を付けるとクエリが実行され、副作用も通常どおり起きると説明しています。

ここは大事です。

本番や更新SQLへ、いきなり EXPLAIN ANALYZE を付けないでください。

安全な順番は、こんな感じです。

  1. まずplain EXPLAINで計画だけを見る
  2. 対象が読み取り専用のSELECTか確認する
  3. 実行負荷が許可された検証環境を使う
  4. statement timeoutなど、チームの停止条件を確認する
  5. そこで初めてANALYZEを使う

記事では次の形式を使います。

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT id, created_at, total_amount
FROM orders
WHERE status = 'pending'
  AND created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;
  • ANALYZE: 実際に実行し、実測値を出す
  • BUFFERS: PostgreSQLが触れたデータブロックの情報を出す
  • FORMAT JSON: 人間だけでなく、スクリプトでも扱いやすいJSONにする

なお、SQL、実行計画、バインド値には、個人情報や業務上の秘密が含まれることがあります。外部AIへ生の実行計画を貼る前に、テーブル名、条件値、コメント、識別子を匿名化してください。社内規定で外部送信が禁止なら、ローカル処理だけにします。

10分の初回成功: DBなしで「行数のズレ」を1件見つける

最初から実DBを触る必要はありません。

次のダミーJSONを bad-plan.json として保存します。実行計画でよく見る項目だけに絞っています。

[
  {
    "Plan": {
      "Node Type": "Limit",
      "Plan Rows": 100,
      "Actual Rows": 100,
      "Actual Loops": 1,
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Relation Name": "orders",
          "Plan Rows": 120,
          "Actual Rows": 18000,
          "Actual Loops": 1,
          "Rows Removed by Filter": 482000,
          "Shared Hit Blocks": 1000,
          "Shared Read Blocks": 3200
        }
      ]
    },
    "Planning Time": 0.42,
    "Execution Time": 384.7
  }
]

次に、実行計画を再帰的にたどるチェッカーを check_plan.py として保存します。

import json
import sys
from pathlib import Path


def walk(node):
    """実行計画のnodeを上から順に取り出す。"""
    yield node
    for child in node.get("Plans", []):
        yield from walk(child)


def row_gap(node):
    """推定行数と実行行数の差を倍率で返す。"""
    estimated = max(float(node.get("Plan Rows", 0)), 1.0)
    actual = float(node.get("Actual Rows", 0))
    return max(actual / estimated, estimated / max(actual, 1.0))


plan = json.loads(Path(sys.argv[1]).read_text(encoding="utf-8"))[0]
bad = []

for node in walk(plan["Plan"]):
    gap = row_gap(node)
    if gap >= 10:
        bad.append(
            (
                node.get("Node Type", "Unknown"),
                node.get("Plan Rows", 0),
                node.get("Actual Rows", 0),
                gap,
            )
        )

for kind, estimated, actual, gap in bad:
    print(
        f"{kind}: estimate={estimated}, "
        f"actual={actual}, gap={gap:.1f}x"
    )

sys.exit(1 if bad else 0)

実行します。

python check_plan.py bad-plan.json
echo $?

出力例です。

Seq Scan: estimate=120, actual=18000, gap=150.0x
1

Seq Scanノードで、PostgreSQLの見積もりは120行、実際は18,000行でした。150倍のズレです。

ここで「原因は統計情報だ」と断定する必要はありません。最初に調べる場所を1つ選べた。 それが10分の成功です。

チェッカーの10倍は、理解しやすくするための練習用しきい値です。公式の万能な基準ではありません。実務では自分たちのSLO、データ分布、クエリ頻度に合わせて決めます。

指標1: Execution Timeで「本当に遅いか」を確認する

最初に見るのは、実行計画の一番下にある Execution Time です。単位はミリ秒です。

Execution Time: 384.700 ms

一方、各ノードに出る cost=0.00..123.45 は時間ではありません。PostgreSQLのplannerが計画を比較するために使う 任意単位の見積もり です。

ここを混ぜると、「costが100だから100ms」という誤読が起きます。

ただし、Execution Timeだけで修正の成功を決めるのも危険です。1回目はディスクから読み、2回目はキャッシュに乗って速い、ということが普通にあります。同じデータ、同じパラメータ、同じ環境で複数回測り、少なくとも中央値を見る方が安全です。

そして、何msから遅いかはシステム次第です。

  • 画面表示の検索
  • 夜間バッチ
  • 管理者だけが使う集計
  • 1秒に何百回も呼ばれるAPI

同じ384msでも意味が違います。許容時間、つまりSLOを決めるのは人間の仕事 です。

指標2: 推定行数と実行行数の差を見る

次に見るのが、Plan Rows と Actual Rows です。

  • Plan Rows: plannerが「これくらい返る」と予想した行数
  • Actual Rows: 実際に返った行数

PostgreSQLは推定行数を材料に、Seq Scan、Index Scan、Nested Loop、Hash Joinなどを選びます。予想が大きく外れると、現実には合わない計画を選ぶことがあります。

先ほどの150倍のズレが見えたら、候補は複数あります。

  • 統計情報が古い
  • データ分布が偏っている
  • 複数列の相関を見積もれていない
  • 条件式が複雑
  • パラメータによって行数が大きく変わる

ここでAIに ANALYZE orders; を即実行させるのではなく、「どの仮説を、どの読み取り専用クエリで確認するか」を出させます。実行許可は人間が判断します。

指標3: loopsで「小さな遅さの大量反復」を探す

loops は、そのノードが何回実行されたかです。

Index Scan using orders_customer_id_idx
  (actual time=0.030..0.050 rows=4 loops=20000)

1回50マイクロ秒くらいなら、小さく見えます。でも2万回なら話が変わります。

Nested Loopの内側で同じ検索が何度も走り、全体時間を押し上げることがあります。見る時は、ざっくり 1ループの時間 × loops を意識します。ただし上位ノードには子ノードの時間が含まれるため、全ノードを単純に足してはいけません。

ここでの問いは、「この処理は遅いか」ではなく、「なぜこんなに繰り返されているか」 です。

  • join順は妥当か
  • 外側の行数見積もりが外れていないか
  • N+1のような呼び出し方になっていないか
  • 先に絞り込める条件はないか

AIは候補を広げるのが得意です。どのjoinや結果が業務上必要かは、人間が仕様に戻って判断します。

指標4: BUFFERSで「CPUではなくI/Oか」を見る

BUFFERS を付けると、各ノードが触れたブロック数を確認できます。

よく見るのは次です。

  • shared hit: shared buffer、つまりPostgreSQLのキャッシュ上で見つかった
  • shared read: ストレージから読み込んだ
  • dirtied: 変更された
  • written: 書き出された
Buffers: shared hit=1000 read=3200

read が多いから即バグ、ではありません。初回実行、集計、広い期間検索なら、必要な読み込みかもしれません。

大切なのは、修正前後を同じ条件で比べることです。必要な結果件数が同じなのに、触るブロックが大幅に減ったなら、改善の根拠になります。

逆に、Execution Timeだけ速くなってBuffersが変わっていないなら、たまたまキャッシュが温まった可能性もあります。数字が1つだと物語を作れてしまう。複数の証拠を並べる理由です。

指標5: 捨てた行とSortの退避を見る

最後に、読んだ後で捨てた行と、並べ替えの方法を見ます。

Rows Removed by Filter

Rows Removed by Filter: 482000

50万行近く調べて、最終的に必要なのは100行。この差は、条件に合う行へ早く到達できていない可能性を示します。

ただし、これだけで「indexを作ればよい」とは言えません。テーブルが小さい、ほとんどの行を返す、書き込みが非常に多い、といった場合はSeq Scanの方が合理的です。

Seq Scanは悪役ではありません。大量に読んで大量に捨て、しかも遅い時に初めて改善候補になります。

Sort Method

Sort Method: external merge  Disk: 20480kB

external merge や Disk が出ていれば、sortがメモリに収まらずディスクを使った証拠です。

考える候補は、必要行を先に絞る、適切な順序を持つindexを検討する、不要な列や行を減らす、設定を見直す、などです。work_mem を増やす案もありますが、接続やsortごとにメモリを使い得るため、AIへ一律変更させるのは危険です。

具体例: indexを作る前に、仮説を比較する

例の検索へ戻ります。

SELECT id, created_at, total_amount
FROM orders
WHERE status = 'pending'
  AND created_at >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

実行計画から次が観測できたとします。

  • Execution Time: 384.7ms
  • 推定120行、実測18,000行
  • loops: 1
  • shared read: 3,200 blocks
  • Rows Removed by Filter: 482,000

ここから立てられる仮説は、少なくとも3つです。

  1. 統計が現状のデータ分布を表せていない
  2. status と created_at の組み合わせに合うindexがない
  3. pendingが多すぎて、indexの選択性が低い

複合index候補は、たとえばこうです。

CREATE INDEX CONCURRENTLY idx_orders_status_created_at
ON orders (status, created_at DESC);

でも、これは 候補 です。

indexには容量、作成負荷、更新時の書き込みコストがあります。既存indexとの重複もあります。CONCURRENTLY にも制約や運用上の注意があります。採用前に、既存index、書き込み頻度、対象クエリの実行頻度、検証環境での改善幅を人間が確認します。

SQLの変更前後では、次を同時に確認します。

  • 結果件数と内容が同じ
  • Execution Timeの中央値がSLO内
  • 推定行数と実行行数の差が縮んだ
  • loopsが意図どおり
  • buffersが減った
  • 大量のfilter除外やdisk sortが解消した
  • 書き込み性能とindex容量が許容範囲

速くなったけど結果が違う。これは改善ではありません。

AIへ渡すプロンプト3本

AIへは「直して」ではなく、証拠と責任境界を渡します。実行計画に秘密が含まれる場合は、先に匿名化してください。

1. 実行計画のトリアージ

あなたはPostgreSQLの性能調査補助です。
次の匿名化済みEXPLAIN JSONを、以下の順で読んでください。

1. Execution Time
2. Plan RowsとActual Rowsの差
3. loops
4. BUFFERS
5. Rows Removed by FilterとSort Method

出力は「観測できる事実」「改善仮説」「不足している証拠」に分けてください。
Seq Scanを自動的に悪と判定しないでください。
CREATE INDEXや設定変更を断定せず、読み取り専用の確認SQLを先に提案してください。
実行可否と最終採用は人間へ戻してください。

2. 改善案の比較

次の遅いSELECTについて、改善候補を比較してください。

- SQL書き換え
- 統計情報の確認・更新
- index候補

各案を、期待効果、副作用、確認方法、撤回方法で表にしてください。
index案では、列順、既存indexとの重複、書き込みコストも質問してください。
work_memなどのグローバル設定を一律変更しないでください。
分からない点は推測で埋めず、質問として返してください。

3. 修正前後の差分レビュー

修正前と修正後の匿名化済みEXPLAIN JSONを比較してください。

比較項目:
- Execution Time
- estimated rows / actual rows
- loops
- shared hit / read
- Rows Removed by Filter
- Sort MethodとDisk使用

「改善した」「悪化した」「判断不能」を項目ごとに分けてください。
キャッシュ差の可能性を指摘し、同条件で複数回測る手順を出してください。
結果件数と業務上の正しさは実行計画だけでは確認できない、と明記してください。
最終承認は人間へ戻してください。

人間が決めること、AIに任せること

工程 人間 AI
対象選定 SLO、頻度、影響範囲を決める 遅い候補を整理する
実行許可 環境、負荷、データ境界を承認する 安全な確認手順の草案を出す
診断 業務仕様と必要な結果を判断する 5指標で事実と仮説を分ける
改善 SQL、index、統計、設定の採否を決める 複数案と副作用を比較する
検証 結果の正しさとSLO達成を承認する 修正前後の差分を要約する
運用 再計測周期と停止条件を決める 定期レポートの草案を作る

なんでもAIへ任せるのではなく、AIは選択肢と証拠を整え、人間は許容損失と正しさを決める。 この分け方が、地味ですが効きます。

うまく効かない条件と回避策

この読み順にも限界があります。

小さいテーブルや大半の行を返す検索

Seq Scanが最適なことがあります。node名で失敗判定せず、時間、行数、buffersと一緒に見ます。

本番だけデータ分布が違う

小さなローカルDBの計画は、本番相当環境を再現しません。機密データをコピーせず、分布を再現した合成データや許可済みステージングで測ります。

ロック待ちや外部要因

EXPLAINはSQL計画には強いですが、アプリ側のconnection pool待ち、ネットワーク、ロック競合の全体像を単独では説明できません。必要ならpg_stat_activity、ロック情報、アプリのtraceへ調査範囲を広げます。

1回だけの測定

キャッシュや同時負荷でぶれます。同条件で複数回測り、中央値とばらつきを見ます。

SQLは速いが結果が間違っている

実行計画は業務仕様を保証しません。結果件数、境界値、権限条件、テストを別に確認します。

無料のこの読み順で十分な人もいます。単発の低頻度クエリでSLOを満たし、運用負荷もないなら、仕組みを増やす必要はありません。

一方、チーム固有の価値は、SLO、負荷許可、データ分布、書き込み比率に合わせてしきい値を決めることです。そしてデータは増えます。半年前の正解が今も正解とは限らない。pg_stat_statementsなどで重いクエリ候補を観測し、同じ読み順で定期的に見直すところまで行くと、診断が習慣になります。

まとめ

AI生成SQLが遅かった時、最初の一手を「とりあえずindex」にしない。

  1. Execution Timeで、SLOに対して本当に遅いかを見る
  2. 推定行数と実行行数の差で、plannerの見積もりを疑う
  3. loopsで、小さな処理の大量反復を探す
  4. BUFFERSで、I/Oの量を比べる
  5. Rows RemovedとSortで、読んで捨てた仕事を探す

今日10分でやることは、ダミーJSONへチェッカーを1回実行するだけです。警告が1件出て、最初に見る場所を選べたら終わり。

実行計画は答えそのものではなく、次の問いを選ぶための証拠です。AIがSQLを書く速度を上げるほど、人間には「どの証拠で採用するか」を設計する仕事が残る。なんかこの役割分担の方が、長く使える気がするんです。

参考リンク

更新日: 2026-07-27

生成AI活用エンジニア&3児のパパ。AI×開発の実践知を毎日発信しています → X

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?