ClaudeでスロークエリをEXPLAIN ANALYZEで絞り、コピー先で索引案を検証

PostgreSQLのスロークエリは、本番には統計を読むだけのロールで触れさせ、EXPLAIN (ANALYZE, BUFFERS)と索引の試作はコピー先で回すと、Claudeに調査を任せても本番のデータを書き換えるリスクを抑えられます。流れは3段です。pg_stat_statementsで遅いクエリを選び、実行計画を読んで索引案を出し、コピー先で前後を比べます。

この記事では、その3段と、本番側のロール設計、Claude Codeの権限ルールをまとめます。MCPサーバーの導入手順はPostgres MCP Proの記事にあり、ここではサーバーを足さずpsqlだけで進めます。

全体の流れと、本番に触らせない場所

調べる場所を2つに分けます。本番は「どのクエリが重いか」を知る場所、コピー先は「実際に走らせて確かめる」場所です。

分ける理由は2つあります。EXPLAIN ANALYZEは文を実際に実行するため、遅いクエリを本番で測ると、その遅さをもう一度本番に与えます。もう1つは、コピー先は別のサーバーなのでpg_stat_statementsに本番の実行履歴が無く、上位クエリの選定は本番側でしかできないことです。

本番に渡すのは統計だけのロール

pg_stat_statementsは、shared_preload_librariesに追加して再起動したうえで、データベースごとにCREATE EXTENSION pg_stat_statementsを実行すると使えます。他のユーザーが実行したクエリの文面とqueryidを見られるのは、スーパーユーザーとpg_read_all_statsの権限を持つロールだけです。

つまり、スロークエリの上位を取るだけなら、テーブルのデータを読む権限は要りません。統計専用のロールは次の形になります。

-- 本番で管理者が実行する。名前とパスワードは例
CREATE ROLE claude_stats LOGIN PASSWORD '<強いパスワード>';
GRANT pg_read_all_stats TO claude_stats;
ALTER ROLE claude_stats SET default_transaction_read_only = on;

pg_read_all_statsはpg_stat_*ビューと統計系の拡張を、通常はスーパーユーザーにしか見えない範囲まで読めるようにする定義済みロールです。pg_stat_statementsの文面では、定数が$1のような記号に置き換わります。

default_transaction_read_onlyは「新しいトランザクションの既定を読み取り専用にする」設定で、禁止ではなく既定値です。守りの本体は、このロールに書き込み権限を与えないことに置いてください。

コピー先には、データを読める別のロールを用意します。

-- コピー先で管理者が実行する
CREATE ROLE claude_ro LOGIN PASSWORD '<強いパスワード>';
GRANT pg_read_all_data TO claude_ro;
ALTER ROLE claude_ro SET default_transaction_read_only = on;

pg_read_all_dataは全テーブル・ビュー・シーケンスのSELECTと全スキーマのUSAGEを与えますが、行レベルセキュリティ（RLS）は迂回しません。RLSを使っているなら、その点も踏まえてコピー先のデータを用意します。索引を試作するための書き込み用ロールは別に作り、Claudeには承認制でだけ渡します。

Claude Codeの権限ルールで接続先を分ける

接続文字列はシェルの環境変数に置き、コマンドには変数名だけが現れるようにします。権限ルールはコマンドの文字列に対して照合されるので、変数名ごとに許可・確認・拒否を分けられます。

{
  "permissions": {
    "allow": [
      "Bash(psql \"$PROD_STATS_URL\" *)",
      "Bash(psql \"$COPY_RO_URL\" *)"
    ],
    "ask": [
      "Bash(psql \"$COPY_RW_URL\" *)"
    ],
    "deny": [
      "Read(./.env)",
      "Bash(pg_dump *)"
    ]
  }
}

これはプロジェクトの.claude/settings.jsonに置く想定の例で、公式の書式に沿っています。ルールの評価順はdeny、ask、allowで、最初に当たったものが結果を決めます。コピー先への書き込み用接続をaskに置くと、CREATE INDEXのたびに承認を求められます。

Bashのルールは複合コマンドを理解します。psql "$COPY_RO_URL" -c "..." && 別のコマンドは、後ろ側が許可されていなければ承認を求められます。一方で、引数を絞るパターンは壊れやすいと公式が注意しています。/usr/bin/psqlのようにパスを付けた呼び方やbash -cの中は、Bash(psql ...)のルールに当たりません。

接続先を取り違えたときの被害を、さらに小さくしたいならPreToolUse hookでSQLの文面を検査できます。書き方はPreToolUse hookの記事にあります。

上位クエリを取り、実測できる形にする

統計専用ロールで、合計実行時間の大きい順に取ります。公式の使用例もtotal_exec_timeの降順です。

psql "$PROD_STATS_URL" -X -c "
SELECT queryid, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows, left(query, 200) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;"

total_exec_timeは累計、mean_exec_timeは1回あたりの平均です。呼び出し回数の多い軽いクエリが累計で上に来ることもあるので、callsと並べて読みます。累計だけで選ぶと、1回で数十秒かかる月次バッチが埋もれます。

取れた文面には$1が残っています。EXPLAINのGENERIC_PLANオプションは$1のままでも計画を出せますが、ANALYZEとは併用できません。実測には具体的な値が要るので、コピー先に実在する値を入れて渡します。パラメータ付きならPREPAREした文をEXPLAIN ANALYZE EXECUTEで測る方法もあります。

EXPLAIN (ANALYZE, BUFFERS)を安全に取る

実行計画の取得は、コピー先の読み取り専用接続で行います。

psql "$COPY_RO_URL" -X -c "
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 12345
  AND o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 50;"

orders以降はクエリの例です。押さえておく挙動は3つあります。

• ANALYZEを付けると文は実際に実行されます。SELECTの出力は捨てられますが、INSERT・UPDATE・DELETEなどの副作用は起きます
• 副作用を避けたいときは、BEGIN;の次にEXPLAIN ANALYZE ...;を流し、最後にROLLBACK;で戻します。公式がこの手順を示しています
• BUFFERSはANALYZEと一緒に使うと自動で含まれます。共有ブロックのヒット数と読み込み数が見えます

ノードごとの時間を測るため、EXPLAIN ANALYZEは通常の実行より長くかかることがあります。時間の絶対値より、索引を足す前後で同じ条件の差を見る使い方に向いています。

Claudeに計画を読ませるときの観点

計画の全文を貼ったら、次の順に読ませます。どれも公式のEXPLAINの説明で、計画の見方として挙がっている項目です。

Seq ScanのFilterは、すべての行を走査してから条件を当てる動きです。出力行が少ないのに走査行が多いなら、索引の候補になります。

プロンプトは、役割と制約を先に書き、出力の形を決めておくと同じ形で返ってきます。

添付のEXPLAIN (ANALYZE, BUFFERS)の出力を読んでください。
1. 時間がかかっているノードを上位3つ、行数のずれとあわせて挙げる
2. 原因を、統計の古さ・索引の不足・クエリの書き方に分けて推定する
3. 索引案をCREATE INDEX文で出す。実行はせず、ここで止まる
本番の接続は使わないでください。

3番目の「実行せず、ここで止まる」が効きます。索引案の提示と実行を別の承認に分けておくと、コピー先であっても意図しないDDLが先に走りません。

コピー先で索引案を検証する

索引案が出たら、承認したうえで書き込み用接続から作ります。作成後はテーブルの統計も更新するためANALYZEを流します。

psql "$COPY_RW_URL" -X -c "
CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);
ANALYZE orders;"

その後、同じクエリを$COPY_RO_URLでもう一度測り、前後を並べます。比べる項目は次のとおりです。

• 計画のノードがSeq ScanからIndex Scan系に変わったか
• Execution Timeが下がったか
• Buffers: shared readが減ったか
• 見積もり行数と実測行数のずれが縮んだか

注意したいのは、コピー先のデータ量と分布が本番と近いかどうかです。公式も、見積もりコストや行数は統計のサンプリングで変わると述べています。コピーが本番より小さいと、本番では使われない索引がコピーでは選ばれる、あるいはその逆もあります。

CLAUDE.mdに置く調査のルール

毎回プロンプトに書かなくて済むよう、調査の約束事をCLAUDE.mdに置きます。次の断片は例です。

## PostgreSQLの性能調査
- 本番は `$PROD_STATS_URL`（統計専用）だけを使い、pg_stat_statementsの参照に限る
- EXPLAIN ANALYZEは `$COPY_RO_URL` で行う。INSERT/UPDATE/DELETEは BEGIN と ROLLBACK で囲む
- CREATE INDEX と DROP INDEX は `$COPY_RW_URL` で、承認を得てから実行する
- pg_stat_statements_reset() は呼ばない
- 索引案は、前後のExecution TimeとBuffersの表で報告する

CLAUDE.mdの記述は、Claudeの振る舞いを方向づけますが、強制はしません。強制力があるのは権限ルールとロールの権限です。pg_stat_statements_resetは、既定ではスーパーユーザーだけが実行でき、統計を消すので、ルールとして明記しておく価値があります。

つまずきやすい点

• pg_stat_statementsが空: 拡張はshared_preload_librariesへの追加と再起動が必要です。CREATE EXTENSIONだけでは動きません
• ネストした文が見えない: pg_stat_statements.trackの既定はtopで、関数の中から実行された文は数えません。allにすると見えます
• 遅いのにクエリ側が原因でない: 見積もりと実測が大きくずれているなら、索引より先にANALYZEで統計を更新して計画が変わるかを確かめます

まとめ

スロークエリの調査は、本番を「統計を読む場所」、コピー先を「測って壊す場所」と分けると、Claudeに任せる範囲を権限で切れます。権限ルールは最初の関所で、境界になるのはロールの権限と、本番の管理者接続を実行環境に置かないことです。

索引案が効いたかどうかは、Claudeの説明ではなく、コピー先で測った前後のExecution TimeとBuffersで決めます。コピーの規模が本番と違うときは、その差を報告に書き添えたうえで、本番へ適用するかは人が判断します。