Claude Codeで未使用インデックスと重複を洗い出す — PostgreSQLの削除候補の出し方

PostgreSQLで未使用のインデックスを探すときの出発点は、pg_stat_user_indexesのidx_scanが0の行です。ただ、0であることは「消してよい」とは別の話です。カウンターがいつから数えているか、レプリカで使われていないか、制約を支える索引ではないか。この3点を確かめないまま削ると、月末のバッチだけが遅くなる、といった事故になります。

この記事では、候補を出すSQL、観測期間の確かめ方、レプリカの扱い、重複索引の見つけ方を順に書きます。Claude Codeには、できあがった一覧を読ませて、候補ごとに判断材料を整理させます。DBへの接続とDROPは人の手に残します。

idx_scanが0でも消せない理由

idx_scanは「その索引に対して始まったスキャンの回数」です。pg_stat_all_indexesに1索引1行で載り、pg_stat_user_indexesはそのうちユーザーテーブル分だけを見せるビューです。同じビューにlast_idx_scan(最後にスキャンされた時刻)もあります。

注意点は4つあります。

どの前提も、SQLの結果だけでは見えません。次の節の抽出クエリは、この前提を踏まえて除外条件と補助の列を持たせています。

未使用候補を出すSQL

pg_stat_user_indexesとpg_indexを結び、idx_scanが0で、一意・主キー・排他制約のどれでもない索引を、大きい順に並べます。pg_indexのindisuniqueは一意索引、indisprimaryは主キー、indisexclusionは排他制約を表す列です。

SELECT s.schemaname,
       s.relname        AS table_name,
       s.indexrelname   AS index_name,
       s.idx_scan,
       s.last_idx_scan,
       pg_relation_size(s.indexrelid) AS index_bytes,
       pg_get_indexdef(s.indexrelid)  AS definition
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
  AND NOT i.indisexclusion
ORDER BY index_bytes DESC;

pg_get_indexdefの列を付けるのが実用上の要点です。定義が手元に残っていれば、削除後に困ったとき同じCREATE INDEXをそのまま流せます。last_idx_scanの列は、お使いのバージョンのドキュメントで存在を確かめてください。無い場合は、この列を外します。

idx_scanが0より大きい索引は、この一覧に出てきません。「ほとんど使われていない」索引を探したいときは、idx_scan < 10のように閾値を緩めます。閾値は観測期間と業務の山に依存するので、固定値にはしません。

統計のリセット日と観測期間を先に確かめる

候補の一覧より先に、カウンターがいつから積まれているかを見ます。pg_stat_databaseのstats_resetは、そのデータベースの統計が最後にリセットされた時刻です。

SELECT datname, stats_reset
FROM pg_stat_database
WHERE datname = current_database();

目安は、業務の最長サイクルを丸ごと含む期間です。月次の締め処理、四半期の集計、年に一度のバッチがあるなら、その分まで観測が要ります。stats_resetが先週なら、週次ジョブしか見ていません。

リセットの手段も押さえておきます。

観測を始めたいだけなら、全体リセットは避けます。統計が飛ぶ副作用が大きく、autovacuumの判断にも響くためです。すでに積まれた数字を使い、stats_resetから十分な期間が経っていれば、リセットは不要です。短ければ、リセットするより待つほうが安全です。

レプリカ側の利用を確かめる

リードレプリカへ参照クエリを逃がしている構成では、プライマリのidx_scanが0でも、レプリカでは頻繁に使われていることがあります。ホットスタンバイ中も累積統計は動いており、スキャンや読み取りといった索引の使用状況は、スタンバイ側に通常どおり記録されます。

つまり、同じSQLを各スタンバイでも流します。結果の集計は、次のように読みます。

もう1つの事情は、スタンバイにだけ別の索引を作れないことです。索引の追加や削除はプライマリで行い、変更がスタンバイへ伝わります。削除の判断はレプリカ全部の統計をそろえてからプライマリで一度だけ、という形になります。

ベースバックアップから作ったばかりのレプリカは、統計が0から始まる点にも気をつけます。起動から日が浅いレプリカの「0」は、使われていない証拠になりません。

重複索引を見つける

未使用とは別に、中身が同じ索引も削除候補です。pg_indexには索引ごとの列(indkey)、演算子クラス(indclass)、式索引の式(indexprs)、部分索引の条件(indpred)が入っています。これらがそろって一致する索引は、同じ働きをする2本目です。

SELECT i.indrelid::regclass          AS table_name,
       array_agg(i.indexrelid::regclass) AS same_definition
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
GROUP BY i.indrelid, i.indkey::text, i.indclass::text,
         i.indcollation::text, coalesce(i.indexprs::text, ''),
         coalesce(i.indpred::text, ''), c.relam
HAVING count(*) > 1;

これはあくまで例です。本番で流す前に、検証環境で意図した行が返ることを確かめてください。

完全一致の次に迷うのが、先頭の列が共通する索引です。複数列のB-tree索引は、先頭(左端)の列に条件があるときに最も効率よく使われます。たとえば(a)と(a, b)が並んでいるとき、(a)は(a, b)に包含されているように見えます。ただし、索引の幅が違えば読み取る量も変わります。こうした組は「重複確定」ではなく「要判断」の列に分け、クエリの形を見て決めます。

一意索引が絡む場合は、さらに慎重にします。一意制約を支える索引は、同じ列の通常の索引を残すために消す、という判断にはなりません。消すなら、制約側ではなく通常側です。

Claude Codeに読ませる範囲を絞る

ここからがClaude Codeの出番です。SQLの結果をCSVにして渡すと、候補ごとの判断材料を表にまとめさせられます。接続を任せるのではなく、ファイルで渡す形にします。

権限は、.claude/settings.jsonに書きます。psqlの実行は拒否に入れ、CSVの置き場だけを読めるようにします。

{
  "permissions": {
    "allow": ["Read(./index-audit/**)"],
    "deny": ["Bash(psql *)", "Bash(pg_dump *)"]
  }
}

ルールは、拒否、確認、許可の順で評価され、先に当たったものが結果を決めます。許可ルールで拒否ルールの例外を作ることはできません。

ただし、拒否ルールはセキュリティの境界ではありません。Bashのルールはコマンドの文字列に一致するもので、/usr/bin/psqlのように別の形で呼べば当たりません。DBを守るのは、そもそもClaude Codeの環境にDBの認証情報を置かないことと、抽出に使うロールを読み取り専用にすることです。

渡すプロンプトは、判断の観点を指定します。

claude -p "index-audit/ のCSVを読み、プライマリでidx_scan=0の索引について、
各レプリカの値と突き合わせた表を作って。レプリカのどれかでidx_scanが
0より大きい索引は『除外』、統計の開始日が14日未満のサーバーが
ひとつでもある索引は『観測不足』と分類して。DROP文は書かないこと。" \
  --allowedTools "Read"

出力は「削除候補」「除外」「観測不足」の3区分になります。Claude Codeが分類した結果は、そのまま信じず、stats_resetの日付と元のCSVを見て人が確かめます。分類は機械的な突き合わせなので、ここまでは任せてよい部分です。「業務上この索引は要る」という判断は、Claude Codeからは見えません。

削除する前後の手順

削除は、生成された一覧とは別の手順で行います。使うのはDROP INDEX CONCURRENTLYです。通常のDROP INDEXはテーブルにACCESS EXCLUSIVEロックを取り、他のアクセスを止めます。CONCURRENTLYを付けると、読み書きを止めずに削除でき、競合するトランザクションの完了を待ちます。

-- 戻せるように、先に定義を保存する
SELECT pg_get_indexdef('public.orders_status_idx'::regclass);
 
DROP INDEX CONCURRENTLY IF EXISTS public.orders_status_idx;

制約があります。CONCURRENTLYは1回に1つの索引しか指定できず、トランザクションブロックの中では実行できません。パーティションテーブル上の索引や、一意・主キー制約を支える索引にも使えません。複数を消すときは、1本ずつ別の文として流します。

削除の後は、pg_stat_statementsやauto_explainで遅くなったクエリが出ていないかを数日見ます。気になる退行が出たら、保存しておいた定義で作り直します。ロックの観点でこの種のDDLを確かめる方法はPostgresのマイグレーションをレビューする手順にあります。

つまずきやすい点

月末だけ使う索引を落とす。観測期間が短いと起きる典型です。last_idx_scanとstats_resetを並べ、月次・四半期のジョブを含む期間になっているかを見ます。

ビットマップスキャンの数字を読み違える。ビットマップスキャンは、使った索引のidx_tup_readを増やしますが、索引のidx_tup_fetchは増やしません。「フェッチが0だから使われていない」とは言えないので、判断はidx_scanで行います。

1回のスキャンが複数回数えられる。1回の実行で複数の索引検索が起きることがあり、その1検索ごとにidx_scanが増えます。この数字は実行ノード数より大きくなりえます。「何回使われたか」の厳密な数ではなく、「0かどうか」の目安として見ます。

追加案との取り違え。遅いクエリから索引を足す側の手順は、MySQLのスロークエリを読んで索引案を出す手順(MySQLの場合)や、PostgreSQLのEXPLAIN ANALYZEで索引案を検証する手順にあります。削除の判断と追加の判断は、見る統計が逆です。

ツールによる一括チェックとの使い分け

Postgres MCP Proのanalyze_db_healthは、索引の健全性として重複・未使用・無効の検出を含みます。導入すれば一覧は手元のSQLなしで得られますが、MCPでDBに接続する構成が前提です。Postgres MCP Proの使い方に導入方法があります。

一方、この記事の手順は、接続を渡さずにCSVで受け取る形です。レプリカの数字を自分で突き合わせるので、観測期間とレプリカの扱いを自分の目で確かめられます。リードレプリカを持つ構成や、本番の認証情報をClaude Codeの環境に置けない運用では、この形のほうが組み込みやすくなります。

まとめ

idx_scan = 0の一覧は、削除候補の入口にすぎません。カウンターの起点(stats_reset)が業務の最長サイクルを覆っているか、全レプリカで0か、制約を支えていないか。この3つを満たしたものだけが、実際に消す対象になります。

Claude Codeに任せるのは、CSVの突き合わせと3区分への分類までです。DBの認証情報は渡さず、DROP INDEX CONCURRENTLYは人が1本ずつ流します。消す前にpg_get_indexdefの出力を保存しておけば、退行が出ても元に戻せます。