Postgres EXPLAIN スロークエリアナライザー

--- name: postgres-explain-slow-query-analyzer description: PostgresのEXPLAINおよびEXPLAIN ANALYZEプラン（テキストまたはJSON）を読み取り、クエリが遅い理由を見つけます。大規模リレーションのシーケンシャルスキャン、巨大な行推定を伴うネストループ、誤推定（推定値と実際値）、高いバッファ読み取り、ディスクにスピルするSort/Hash、LIMITプッシュダウンの欠如、実際の時間による高コストノード。修正案（インデックス、書き換え、統計情報）をランク付けします。オフラインのstdlib Pythonアナライザーです。 --- # Postgres EXPLAIN スロークエリアナライザー あなたは開発者やDBAがPostgresのクエリプランを読むのを支援します。遅いクエリは、SQLだけを見つめても修正できないことがほとんどです。プランは、Postgresがシーケンシャルスキャンを選択したか、爆発したネストループ、悪い行推定、ディスクにスピルしたSort、またはギガバイトのバッファを読み取ったHash Joinを示します。`EXPLAIN`または`EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT|JSON)`プランを受け取り、高コストなノードを特定し、なぜ問題なのかを説明し、具体的な修正案をランク付けします。このスキルはプランを読むためのものであり、証拠なしにSQLをゼロから書き換えるものではありません。 破壊的なステートメントは実行しません。レプリカ上またはロールバック可能なトランザクション内で`EXPLAIN (ANALYZE, BUFFERS)`を使用することを推奨します。ANALYZEは実際にクエリを実行することを警告してください。 ## このスキルのファイル - `scripts/explain_analyze.py` - テキストまたはJSONのEXPLAIN / EXPLAIN ANALYZE出力を解析し、実際の時間またはコストでノードをスコアリングし、EX01-EX12（シーケンシャルスキャン、ネストループの爆発、誤推定、ディスクソート/ハッシュ、高い共有読み取り、未実行ノード、パラレル不均衡）を報告し、提案された修正をランク付けします（Python 3.8+標準ライブラリのみ） - `references/plan-nodes-and-buffers.md` - ノードタイプ、コスト、行数、ループ数、バッファ、一時ファイルの読み方 - `references/checks-and-codes.md` - すべての検出コード、重大度、対処法、および入力のヒント - `templates/slow-query-review.md` - プラン抜粋とランク付けされた修正案とともに返すレビューノート - `examples/orders_by_email.explain.txt` - 遅いルックアップ（シーケンシャルスキャン+ネストループ）のテキストEXPLAIN ANALYZE - `examples/review-report.md` - サンプルに対する実際のアナライザー出力 ## ワークフロー ### 1. プランを収集する Postgresのバージョン、SQL、および`EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)`（またはJSON）を尋ねます。本番環境でANALYZEが安全でない場合は、ANALYZEなしの`EXPLAIN (BUFFERS)`を取得し、タイミングは推定値のみであると伝えます。Seq Scanが疑わしい場合は、テーブルサイズ（`pg_stat_user_tables`、`pg_relation_size`）を取得します。 ### 2. アナライザーを実行する ``` python3 scripts/explain_analyze.py examples/orders_by_email.explain.txt python3 scripts/explain_analyze.py plan.json --json-out python3 scripts/explain_analyze.py plan.txt --min-ms 5 python3 scripts/explain_analyze.py --self-test ``` 終了コード0 = 高重大度の検出なし、1 = 高重大度の検出あり、2 = 不正な入力。シェルがない場合は、`references/plan-nodes-and-buffers.md`を使用してプランツリーをボトムアップで辿り、`references/checks-and-codes.md`のコードを適用します。 ### 3. 結果を読む - 高優先度: EX01 選択的なフィルタを持つ大規模リレーションのSeq Scan、EX03 実際の行数×ループ数が巨大なNested Loop、EX05 ホットノードで100倍を超える誤推定比率、EX07 一時ファイルにスピルしたSort/Hash。 - 中優先度: EX02 インデックスなしの結合キー、EX04 スキャン後にほとんどの行を除去するFilter、EX06 高い共有読み取りバッファ、EX08 紙面上は高コストに見えるが実行されないブランチ。 - 低/情報: EX09 パラレルワーカーの不均等、EX10 実行時間に対して計画時間が長い、EX11 LIMITがプッシュダウンされていない、EX12 CTE MATERIALIZEDによる完全な計算の強制。 ### 4. 修正案をランク付けする 証拠に基づいた変更を優先します。Filter/Join Filterに一致するインデックスを追加または調整する、統計情報を更新する（`ANALYZE`）、Nested Loopになった相関サブクエリを書き換える、Sort/Hashがスピルし、セットがメモリに収まる場合にのみ`work_mem`を増やす、Postgres 12+で不要にマテリアライズされたCTEを`NOT MATERIALIZED`に変更する。プランから列名を特定せずにインデックスを発明しないでください。 ### 5. 納品する `templates/slow-query-review.md`を使用して、判定、実際の時間とバッファを持つ上位の高コストノード、検出リスト、および短いランク付けされた修正リストを返します。 ## 出力例 サンプルプランでは、Nested Loopが時間の98%を占めていました。`customers`の外部Seq Scan（`lower(email)`によるフィルタ、式インデックスなし）が、`orders_customer_id_idx`の内部Index Scanに供給され、48,210回のループが実行されました。外部スキャンの推定行数は42でしたが、実際は48,210でした（誤推定）。`orders`の共有読み取りバッファが高くなっていました。ランク付けされた修正: `lower(email)`の式インデックス、次に結合選択性を確認、`ANALYZE customers`。 ## ルール - すべての主張はプランのノード（タイプ、コスト/実際、行数、ループ数、バッファ）に基づくこと。 - 推定のみのEXPLAINとANALYZEを区別し、コストをミリ秒として扱わないこと。 - 測定せずにシーケンシャルスキャンのグローバル無効化やrandom_page_costの非現実的な値を推奨しないこと。 - EXPLAIN ANALYZEがクエリを実行する（トリガー、書き込み、ロック）ことを警告すること。 - これはプラン読み取りの補助であり、`pg_stat_statements`モニタリングや完全なスキーマレビューの代わりにはなりません。 FILE:examples/orders_by_email.explain.txt QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Filter: (created_at >= (now() - '90 days'::interval)) Rows Removed by Filter: 3 Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms FILE:examples/review-report.md # Postgres EXPLAIN分析 ソース: `examples/orders_by_email.explain.txt` 実行時間: 912.61 ms 計画時間: 0.412 ms **判定: 要対応** - 高重大度の検出が3件あります。 ## 時間（またはコスト）による上位ノード | ノード | リレーション | 実際のms（xループ） | コスト | 行数 推定 -> 実際 | ループ数 | 共有読み取り | |------|----------|---------------------|------|-----------------|-------|-------------| | Nested Loop | - | 912.4 | 184220 | 12 -> 96 | 1 | 88440 | | Index Scan | public.orders | 771.4 | 4 | 8 -> 0 | 48210 | 86610 | | Seq Scan | public.customers | 88.2 | 1850 | 42 -> 48210 | 1 | 1830 | ## 検出結果 | 重大度 | コード | 詳細 | |-----|------|--------| | high | EX01 | 選択的なFilter（(lower((email)::text) = 'ada@example.com'::text)）を持つpublic.customersのSeq Scan。151790行を除去 | | high | EX03 | Nested Loopの子Index Scanがloops=48210で実行（実際の行数/ループ~0）、約771.4 msを消費 | | high | EX05 | public.customersのSeq Scanで誤推定: 推定行数=42、実際=48210（比率~1148倍） | | medium | EX02 | Nested Loopにpublic.customersの内部/外部Seq Scanがある | | medium | EX06 | Nested Loopの共有読み取り=88440ブロック | | medium | EX06 | public.ordersのIndex Scanの共有読み取り=86610ブロック | ## ランク付けされた修正案（提案） 1. Seq ScanのFilterに一致するbtree/式インデックスを追加する（lower()などの関数ラッパーを確認）。 2. Nested Loopの外部行数を減らすか、内部結合キーにインデックスを付ける。ANALYZE後に結合順序を再確認。 3. 対象テーブルでANALYZEを実行。相関列には拡張統計情報を検討。 4. より良いインデックスを使用するか、早期にフィルタリングしてNested Loopがヒープを再読み取りしないようにして、共有読み取りを減らす。 FILE:references/checks-and-codes.md # チェックとコード | コード | 重大度 | 意味 | 対処法 | |------|----------|---------|------------| | EX01 | high | 選択的なFilterと大きな実際の行数または高い実際の時間を持つリレーションのSeq Scan | フィルタに一致する式またはbtreeインデックス。一致する式インデックスなしで列を関数でラップしない | | EX02 | medium | 結合条件がインデックス化されていないように見える（Nested Loop内部Seq Scanまたは結合キーのFilterによる高ループ数） | 内部結合キーにインデックスを付ける。データ型が一致していることを確認（インデックスを無効にするキャストがないか） | | EX03 | high | 非常に高いループ数×実際の行数（行爆発）のNested Loop | まず外部行数を減らす。Hash/Merge Joinを検討。NL選択を駆動する誤推定を修正 | | EX04 | medium | Filterがスキャン後にほとんどの行を除去する（入力行数 >> 出力行数） | 述語をインデックス条件にプッシュ。プランナーがインデックスを使用できるように書き換え | | EX05 | high | 誤推定: 実際/推定または推定/実際が重要なノードで100以上 | ANALYZE。相関列の拡張統計。述語での非不変関数を避ける | | EX06 | medium | ホットノードでの高い共有読み取りバッファ | ヒープフェッチを減らすためのキャッシュ/インデックス。Nested Loopが同じページを再読み取りしていないか確認 | | EX07 | high | SortまたはHashが一時ファイルにスピル（外部マージ / 一時書き込みのあるBatches > 1） | Sort/Hashの前に行を減らす。このクエリセッションのみwork_memを慎重に増やす。ソートを避けるためのサポートインデックスを追加 | | EX08 | low | ノードが実行されない（実際のループ数=0）が、推定コストを支配している | ランタイムには無視。ANALYZEなしのプラン比較に有用 | | EX09 | low | ワーカー貢献が不均等なパラレルクエリ | parallel_*設定とgatherが価値があるか確認 | | EX10 | info | 実行時間に対して計画時間が大きい | プリペアドステートメント。ワンオフ計画の簡素化。パーティションプルーニングの問題 | | EX11 | medium | 大規模セットの完全なSortまたは完全な集計の上にLIMITがある | 順序/フィルタが早期停止付きのインデックススキャンを可能にするように書き換え | | EX12 | medium | MATERIALIZEDを強制するCTE Scan | WITH ... AS NOT MATERIALIZED（PG12+）またはCTEをインライン化 | ## 入力のヒント - テキストプラン: 完全なツリーを貼り付け。インデントを削除しない。 - JSONプラン: Postgres `FORMAT JSON`配列（1つのプランオブジェクト）。スクリプトは生の配列または`{Plan: ...}`を受け入れます。 - `QUERY PLAN`などのオプションのヘッダー行や破線ルールは無視されます。 FILE:references/plan-nodes-and-buffers.md # プランノードとバッファ ## 有用なプランの取得 ```sql EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) <query>; -- または EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>; ``` 本番環境では、ホットスタンバイを優先するか、ロールバックするトランザクションで書き込みをラップします。`ANALYZE`はステートメントを実行します。 ## テキストノード行の読み方 例: `Seq Scan on public.customers (cost=0.00..1550.00 rows=42 width=64) (actual time=0.020..85.110 rows=48210 loops=1)` - ノードタイプとリレーション - `cost=startup..total`（プランナーユニット、msではない） - `rows` = 推定行数。ANALYZEでは`actual time`、`rows`、`loops`も表示 - そのノードの合計時間 ~= (実際の合計時間) * サブツリー貢献パターンのループ数。親が子を含む場合、リーフ時間の方が明確 ## 一般的なノードタイプ - **Seq Scan**: ヒープ全体を読み取る。小さなテーブルやほとんどの行が一致する場合は問題ない。大きなテーブルで選択的なFilterがあると問題。 - **Index Scan / Index Only Scan / Bitmap Heap Scan**: 選択的な場合に良い。多くの再チェックがあるBitmap Heapは依然として問題になる可能性がある。 - **Nested Loop**: 外部の各行に対して内部を実行。外部行が少なく内部がインデックス化されている場合は最適。外部が大きい場合は壊滅的。 - **Hash Join / Merge Join**: より大きなセットに適している。スピルするHash（`Batches` > 1、一時書き込み）に注意。 - **Sort**: `Sort Method: external merge`はディスクへのスピルを意味する。`work_mem`または前段での行数削減を検討。 - **Aggregate / GroupAggregate / HashAggregate**: グループ化列がインデックス化されているか、事前にフィルタリングされているか確認。 - **CTE Scan**: Postgres 12+はCTEをMATERIALIZEする場合がある。フィルタのプッシュダウンをブロックする可能性がある。 - **Limit**: 安価なパスの上にあるべき。Limitがすべての完全なソートの上にある場合、基準をプッシュダウンする。 ## バッファ（BUFFERSオプション） - `shared hit` = 共有バッファで見つかった（安価） - `shared read` = OS/ディスクからバッファに読み取られた（キャッシュミス時は高価） - `temp read/written` = work_memスピルファイル 繰り返されるNested Loop内部側で巨大な`shared read`を持つノードは、より良い外部フィルタまたはインデックスを必要とすることが多い。 ## 行の誤推定 `rows`（推定）を実際の`rows`と比較します。高価なノードで10倍以上（特に100倍以上）の比率は、悪い統計、古い`ANALYZE`、相関列、またはインデックス列をラップする関数（`lower(email)`）を意味します。 ## このスキルが行わないこと `pg_stat_statements`、auto_explain、またはスキーマだけから始まるインデックス設計レビューの代わりにはなりません。1つのプランから始めて、そのプランを説明します。 FILE:scripts/explain_analyze.py #!/usr/bin/env python3 """Postgres EXPLAIN / EXPLAIN ANALYZE プランアナライザー（標準ライブラリのみ、Python 3.8+）。 テキストまたはJSONプランを解析し、高コストなノードをスコアリングし、プラン読み取りに焦点を当てた検出結果EX01-EX12（シーケンシャルスキャン、ネストループの爆発、誤推定、バッファ圧力、ディスクスピル）を報告します。 使用法: explain_analyze.py PLAN.txt|.json [--min-ms N] [--json-out] explain_analyze.py --self-test 終了コード: 0 = 高重大度の検出なし、1 = 高重大度の検出あり、2 = 不正な入力。 """ import argparse, json, re, sys SEV = {"high": 0, "medium": 1, "low": 2, "info": 3} NODE_RE = re.compile( r"^(?P<indent>\s*)(?:->\s*)?(?P<type>.+?)" r"(?=\s+using\s+|\s+on\s+|\s*\(cost=)" r"(?:\s+using\s+(?P<idx>[\w.]+))?" r"(?:\s+on\s+(?P<rel>[\w.]+))?" r"(?:\s+(?P<alias>\w+))?" r"\s*\(cost=(?P<cstart>[\d.]+)\.\.(?P<ctotal>[\d.]+)\s+rows=(?P<erows>[\d]+)\s+width=(?P<width>[\d]+)\)" r"(?:\s*\(actual time=(?P<astart>[\d.]+)\.\.(?P<atotal>[\d.]+)\s+rows=(?P<arows>[\d]+)\s+loops=(?P<loops>[\d]+)\))?" ) BUF_RE = re.compile( r"Buffers:\s*(?:shared hit=(?P<shit>\d+))?" r"(?:\s*read=(?P<sread>\d+))?" r"(?:.*?temp read=(?P<tread>\d+))?" r"(?:.*?written=(?P<twrite>\d+))?", re.I, ) FILTER_RE = re.compile(r"^\s+Filter:\s*(.+)$") REMOVED_RE = re.compile(r"Rows Removed by Filter:\s*(\d+)") SORT_RE = re.compile(r"Sort Method:\s*(.+)$", re.I) HASH_BATCH_RE = re.compile(r"Batches:\s*(\d+)", re.I) PLAN_TIME_RE = re.compile(r"Planning Time:\s*([\d.]+)\s*ms", re.I) EXEC_TIME_RE = re.compile(r"Execution Time:\s*([\d.]+)\s*ms", re.I) class InputError(Exception): pass def parse_text(text): lines = text.splitlines() nodes = [] extras = {"planning_ms": None, "execution_ms": None} stack = [] i = 0 while i < len(lines): line = lines[i].rstrip() if not line or set(line) <= set("-=") or line.strip() == "QUERY PLAN": i += 1 continue m = PLAN_TIME_RE.search(line) if m: extras["planning_ms"] = float(m.group(1)); i += 1; continue m = EXEC_TIME_RE.search(line) if m: extras["execution_ms"] = float(m.group(1)); i += 1; continue m = NODE_RE.match(line) if m: raw_indent = re.match(r"^(\s*)", line).group(1) ind = len(raw_indent) node = { "type": m.group("type").strip(), "rel": m.group("rel"), "idx": m.group("idx"), "cost_start": float(m.group("cstart")), "cost_total": float(m.group("ctotal")), "est_rows": int(m.group("erows")), "width": int(m.group("width")), "act_start": float(m.group("astart")) if m.group("astart") else None, "act_total": float(m.group("atotal")) if m.group("atotal") else None, "act_rows": int(m.group("arows")) if m.group("arows") else None, "loops": int(m.group("loops")) if m.group("loops") else None, "filter": None, "removed": None, "buffers": {}, "sort_method": None, "hash_batches": None, "indent": ind, "children": [], } while stack and stack[-1][0] >= ind: stack.pop() if stack: stack[-1][1]["children"].append(node) else: nodes.append(node) stack.append((ind, node)) j = i + 1 while j < len(lines): pl = lines[j] if not pl.strip(): break if NODE_RE.match(pl) or PLAN_TIME_RE.search(pl) or EXEC_TIME_RE.search(pl): break if not pl.startswith(" ") and not pl.startswith("\t"): break fm = FILTER_RE.match(pl) if fm: node["filter"] = fm.group(1).strip() rm = REMOVED_RE.search(pl) if rm: node["removed"] = int(rm.group(1)) bm = BUF_RE.search(pl) if bm: node["buffers"] = {k: int(bm.group(k)) for k in ("shit", "sread", "tread", "twrite") if bm.group(k)} sm = SORT_RE.search(pl) if sm: node["sort_method"] = sm.group(1).strip() hb = HASH_BATCH_RE.search(pl) if hb: node["hash_batches"] = int(hb.group(1)) j += 1 i = j continue i += 1 if not nodes: raise InputError("プランノードが見つかりません（cost=... rows=... のEXPLAINテキストが必要です）") return nodes, extras def parse_json_plan(obj): if isinstance(obj, list): if not obj: raise InputError("空のJSONプラン配列") obj = obj[0] if not isinstance(obj, dict): raise InputError("JSONプランはオブジェクトまたはオブジェクトの配列である必要があります") plan = obj.get("Plan") or obj.get("plan") if not plan: raise InputError("JSONにPlanオブジェクトがありません") extras = { "planning_ms": obj.get("Planning Time"), "execution_ms": obj.get("Execution Time"), } def conv(p, indent=0): node = { "type": p.get("Node Type", "Unknown"), "rel": p.get("Relation Name") or p.get("Relation"), "idx": p.get("Index Name"), "cost_start": float(p.get("Startup Cost", 0)), "cost_total": float(p.get("Total Cost", 0)), "est_rows": int(p.get("Plan Rows", 0)), "width": int(p.get("Plan Width", 0)), "act_start": p.get("Actual Startup Time"), "act_total": p.get("Actual Total Time"), "act_rows": p.get("Actual Rows"), "loops": p.get("Actual Loops"), "filter": p.get("Filter"), "removed": p.get("Rows Removed by Filter"), "buffers": {}, "sort_method": p.get("Sort Method"), "hash_batches": p.get("Hash Batches") or p.get("Batches"), "indent": indent, "children": [], } if "Shared Hit Blocks" in p or "Shared Read Blocks" in p: node["buffers"] = { k: int(p[src]) for k, src in (("shit", "Shared Hit Blocks"), ("sread", "Shared Read Blocks"), ("tread", "Temp Read Blocks"), ("twrite", "Temp Written Blocks")) if src in p } for ch in p.get("Plans") or []: node["children"].append(conv(ch, indent + 2)) return node return [conv(plan)], extras def walk(nodes): out = [] def w(n): out.append(n) for c in n["children"]: w(c) for n in nodes: w(n) return out def time_ms(n): if n["act_total"] is None or n["loops"] is None: return None return float(n["act_total"]) * float(n["loops"]) def analyze(nodes, extras, min_ms=1.0): findings = [] flat = walk(nodes) def add(sev, code, msg, node=None): findings.append((sev, code, msg, node)) for n in flat: t = n["type"] ms = time_ms(n) est, act = n["est_rows"], n["act_rows"] loops = n["loops"] or 1 rel = n["rel"] or "?" if t.startswith("Seq Scan") and n.get("filter") and (n.get("removed") or 0) > 1000: if (act or 0) > 1000 or (ms or 0) >= min_ms * 10: add("high", "EX01", "%sのSeq Scan、選択的なFilter（%s）あり。%s行を除去" % (rel, n["filter"], n.get("removed")), n) if t.startswith("Nested Loop"): for c in n["children"]: if c["type"].startswith("Seq Scan"): add("medium", "EX02", "Nested Loopに%sの内部/外部Seq Scanがある" % (c.get("rel") or "?"), c) if t.startswith("Nested Loop"): for c in n["children"]: cl = c.get("loops") or 0 if cl >= 10000 and (time_ms(c) or 0) >= min_ms * 20: add("high", "EX03", "Nested Loopの子%sがloops=%dで実行（実際の行数/ループ~%s）、約%.1f msを消費" % ( c["type"], cl, c.get("act_rows"), time_ms(c) or 0), c) if n.get("removed") and act is not None: total_in = n["removed"] + act if total_in > 1000 and n["removed"] / total_in >= 0.9: add("medium", "EX04", "%s %sのFilterが行の%.0f%%を除去（%d除去、%d保持）" % ( t, rel, 100.0 * n["removed"] / total_in, n["removed"], act), n) if act is not None and est is not None and (ms or 0) >= min_ms: if est == 0: ratio = float("inf") if act > 0 else 1.0 else: ratio = max(act / max(est, 1), est / max(act, 1)) if ratio >= 100: add("high", "EX05", "%s %sで誤推定: 推定行数=%d、実際=%d（比率~%.0f倍）" % (t, rel, est, act, ratio if ratio != float("inf") else 9999), n) sread = n.get("buffers", {}).get("sread") or 0 if sread >= 5000 and (ms or 0) >= min_ms: add("medium", "EX06", "%s %sの共有読み取り=%dブロック" % (t, rel, sread), n) sm = (n.get("sort_method") or "").lower() if "external" in sm: add("high", "EX07", "Sortがディスクにスピル（%s）: %s" % (n["sort_method"], t), n) if n.get("hash_batches") and n["hash_batches"] > 1 and (n.get("buffers", {}).get("twrite") or 0) > 0: add("high", "EX07", "Hashがスピル: Batches=%d、一時書き込み=%s" % (n["hash_batches"], n["buffers"].get("twrite")), n) if loops == 0 and n["cost_total"] >= 1000: add("low", "EX08", "ノード%sは実行されないが、コスト=%.0f" % (t, n["cost_total"]), n) if t.startswith("Limit"): for c in n["children"]: if c["type"].startswith("Sort") and (c.get("act_rows") or c.get("est_rows") or 0) > 1000: add("medium", "EX11", "大規模セット（%s行）のSortの上にLIMITがある" % (c.get("act_rows") or c.get("est_rows")), n) if "CTE" in t and (ms or n["cost_total"] > 1000): add("medium", "EX12", "CTE Scan %sがマテリアライズされている可能性があります。フィルタをプッシュダウンする必要がある場合はNOT MATERIALIZEDを検討してください" % (n.get("rel") or ""), n) if extras.get("planning_ms") and extras.get("execution_ms"): if extras["planning_ms"] > extras["execution_ms"] and extras["planning_ms"] > 20: add("info", "EX10", "計画時間 %.1f ms > 実行時間 %.1f ms" % (extras["planning_ms"], extras["execution_ms"]), None) findings.sort(key=lambda x: (SEV.get(x[0], 9), x[1])) return findings def report(nodes, extras, findings, src): flat = walk(nodes) lines = [] lines.append("# Postgres EXPLAIN分析") lines.append("") lines.append("ソース: `%s`" % src) if extras.get("execution_ms") is not None: lines.append("実行時間: %s ms" % extras["execution_ms"]) if extras.get("planning_ms") is not None: lines.append("計画時間: %s ms" % extras["planning_ms"]) high = sum(1 for f in findings if f[0] == "high") lines.append("") if high: lines.append("**判定: 要対応** - 高重大度の検出が%d件あります。" % high) elif findings: lines.append("**判定: 注意点ありでOK** - 検出が%d件ありますが、高重大度はありません。" % len(findings)) else: lines.append("**判定: OK** - しきい値を超える検出はありません。") lines.append("") lines.append("## 時間（またはコスト）による上位ノード") lines.append("") scored = [] for n in flat: ms = time_ms(n) scored.append((ms if ms is not None else -1, n["cost_total"], n)) scored.sort(key=lambda x: (x[0] if x[0] >= 0 else -1, x[1]), reverse=True) lines.append("| ノード | リレーション | 実際のms（xループ） | コスト | 行数 推定 -> 実際 | ループ数 | 共有読み取り |") lines.append("|------|----------|---------------------|------|-----------------|-------|-------------|") for ms, cost, n in scored[:12]: am = ("%.1f" % ms) if ms is not None and ms >= 0 else "-" ra = "%s -> %s" % (n["est_rows"], n["act_rows"] if n["act_rows"] is not None else "-") lines.append("| %s | %s | %s | %.0f | %s | %s | %s |" % ( n["type"], n["rel"] or "-", am, cost, ra, n["loops"] if n["loops"] is not None else "-", n.get("buffers", {}).get("sread", "-"))) lines.append("") lines.append("## 検出結果") lines.append("") if not findings: lines.append("なし。") else: lines.append("| 重大度 | コード | 詳細 |") lines.append("|-----|------|--------|") for sev, code, msg, _ in findings: lines.append("| %s | %s | %s |" % (sev, code, msg.replace("|", "/"))) lines.append("") lines.append("## ランク付けされた修正案（提案）") lines.append("") fixes = [] codes = {f[1] for f in findings} if "EX01" in codes: fixes.append("Seq ScanのFilterに一致するbtree/式インデックスを追加する（lower()などの関数ラッパーを確認）。") if "EX03" in codes or "EX02" in codes: fixes.append("Nested Loopの外部行数を減らすか、内部結合キーにインデックスを付ける。ANALYZE後に結合順序を再確認。") if "EX05" in codes: fixes.append("対象テーブルでANALYZEを実行。相関列には拡張統計情報を検討。") if "EX07" in codes: fixes.append("Sort/Hashの前に行を減らすか、測定後にこのセッションのみwork_memを増やす。") if "EX06" in codes: fixes.append("より良いインデックスを使用するか、早期にフィルタリングしてNested Loopがヒープを再読み取りしないようにして、共有読み取りを減らす。") if "EX11" in codes: fixes.append("LIMITが完全なセットをソートする代わりに、順序付けられたインデックススキャンで停止できるように書き換える。") if "EX12" in codes: fixes.append("WITH ... AS NOT MATERIALIZEDを試すか、CTEをインライン化してフィルタをプッシュダウンする。") if not fixes: fixes.append("しきい値から高優先度の構造的修正はありません。pg_stat_statementsの監視下でプランを維持してください。") for i, f in enumerate(fixes, 1): lines.append("%d. %s" % (i, f)) lines.append("") return "\n".join(lines) + "\n" def load_plan(path): raw = open(path, encoding="utf-8").read() if path.endswith(".json") or raw.lstrip().startswith(("[", "{")): try: obj = json.loads(raw) except json.JSONDecodeError as e: raise InputError("無効なJSON: %s" % e) return parse_json_plan(obj) return parse_text(raw) def self_test(): tests = [] def t(name, ok): tests.append((name, bool(ok))) sample = """Nested Loop (cost=0.42..184220.50 rows=12 width=72) (actual time=0.085..912.440 rows=96 loops=1) Buffers: shared hit=1204 read=88440 -> Seq Scan on public.customers c (cost=0.00..1850.00 rows=42 width=40) (actual time=0.040..88.200 rows=48210 loops=1) Filter: (lower((email)::text) = 'ada@example.com'::text) Rows Removed by Filter: 151790 Buffers: shared hit=20 read=1830 -> Index Scan using orders_customer_id_idx on public.orders o (cost=0.42..4.20 rows=8 width=40) (actual time=0.015..0.016 rows=0 loops=48210) Index Cond: (customer_id = c.id) Buffers: shared hit=1184 read=86610 Planning Time: 0.412 ms Execution Time: 912.610 ms """ nodes, extras = parse_text(sample) t("ルートのネストループを解析", nodes[0]["type"] == "Nested Loop") t("子が2つ", len(nodes[0]["children"]) == 2) t("実行時間", extras["execution_ms"] == 912.610) findings = analyze(nodes, extras) codes = {f[1] for f in findings} t("EX01 選択的なシーケンシャルスキャン", "EX01" in codes) t("EX03 ネストループの爆発", "EX03" in codes) t("EX05 誤推定", "EX05" in codes) t("EX06 共有読み取り", "EX06" in codes) r = report(nodes, extras, findings, "t") t("レポートの判定が要対応", "要対応" in r) tiny = """Seq Scan on public.t (cost=0.00..1.05 rows=5 width=4) (actual time=0.002..0.003 rows=5 loops=1) Execution Time: 0.020 ms """ nodes2, ex2 = parse_text(tiny) f2 = analyze(nodes2, ex2) t("小さなテーブルは高重大度なし", not any(x[0] == "high" for x in f2)) spill = """Sort (cost=1000..1100 rows=10000 width=8) (actual time=50..60 rows=10000 loops=1) Sort Method: external merge Disk: 4096kB """ ns, es = parse_text(spill) fs = analyze(ns, es) t("EX07 外部ソート", any(x[1] == "EX07" for x in fs)) j = [{"Plan": {"Node Type": "Limit", "Startup Cost": 0, "Total Cost": 10, "Plan Rows": 10, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 5.0, "Actual Rows": 10, "Actual Loops": 1, "Plans": [{"Node Type": "Sort", "Startup Cost": 0, "Total Cost": 9, "Plan Rows": 5000, "Plan Width": 4, "Actual Startup Time": 0.1, "Actual Total Time": 4.9, "Actual Rows": 5000, "Actual Loops": 1, "Sort Method": "quicksort"}]}, "Planning Time": 1.0, "Execution Time": 5.1}] nj, ej = parse_json_plan(j) fj = analyze(nj, ej) t("JSON Limit+Sort -> EX11", any(x[1] == "EX11" for x in fj)) try: parse_text("プランではない") ok = False except InputError: ok = True t("不正な入力を拒否", ok) for name, ok in tests: print(("PASS " if ok else "FAIL ") + name) passed = sum(ok for ok in tests) print("%d/%d パス" % (passed, len(tests))) return 0 if passed == len(tests) else 1 def main(argv=None): ap = argparse.ArgumentParser(description="Postgres EXPLAIN / EXPLAIN ANALYZE プランを分析します。") ap.add_argument("plan", nargs="?") ap.add_argument("--min-ms", type=float, default=1.0) ap.add_argument("--json-out", action="store_true") ap.add_argument("--self-test", action="store_true") a = ap.parse_args(argv) if a.self_test: return self_test() if not a.plan: ap.print_usage(sys.stderr) return 2 try: nodes, extras = load_plan(a.plan) findings = analyze(nodes, extras, min_ms=a.min_ms) except (OSError, InputError) as e: print("エラー: %s" % e, file=sys.stderr) return 2 if a.json_out: print(json.dumps({ "execution_ms": extras.get("execution_ms"), "planning_ms": extras.get("planning_ms"), "findings": [{"severity": s, "code": c, "detail": m} for s, c, m, _ in findings], }, indent=2)) else: sys.stdout.write(report(nodes, extras, findings, a.plan)) return 1 if any(f[0] == "high" for f in findings) else 0 if __name__ == "__main__": sys.exit(main()) FILE:templates/slow-query-review.md # スロークエリプランレビュー - 日付: - データベース / Postgresバージョン: - 環境（本番レプリカ / ステージング）: - クエリ名またはチケット: ## SQL（簡潔） ```sql ``` ## 判定 - 全体的な評価: - 主要な時間の要因: ## 上位ノード | ノード | 実際の時間 / コスト | 行数 推定 -> 実際 | ループ数 | バッファ | メモ | |------|--------------------|-----------------|-------|---------|-------| | | | | | | | ## 検出結果 | コード | 重大度 | 詳細 | |------|----------|--------| | | | | ## ランク付けされた修正案 1. 2. 3. ## フォローアップ - [ ] 変更後に`EXPLAIN (ANALYZE, BUFFERS)`を取得 - [ ] 追加する前に`pg_stat_user_indexes` / 未使用インデックスを確認 - [ ] ベーステーブルのANALYZEが古くなっていなかったか確認