db-index-hygiene

db-index-hygiene (M12)

M11とは逆の機能です。M11は不足しているインデックスを追加し、M12はコストしか発生させないインデックスを削除します。すべてのインデックスは書き込みを遅くし、ストレージとキャッシュを消費します。重複したインデックスや一度も読み取られないインデックスは、純粋なオーバーヘッドです。Performance & Scale スコア（軸 performance、リレーショナルの Higiene de índices w16）に反映されます。

チェック内容

1. 完全な重複 — キー列リスト（および型／述語）が同一のインデックスが2つある場合、片方は冗長です。
2. 冗長なプレフィックス — (a) のインデックスが既存の (a, b) のインデックスで完全にカバーされている場合、通常は短い方を削除できます（B-tree の左端プレフィックスルール）。
3. 未使用 — 代表的な期間にスキャンされていないインデックス（Tier-2 の idx_scan = 0）。これは実行時の実態です。静的解析だけで未使用と判断することはありません。

スコア／軸

performance のみに反映されます（リレーショナルの Higiene de índices w16。NoSQL プロファイルでは Indexación に統合されます）。

Tier-0（静的）

宣言されたインデックスを解析し、DDL のみから完全な重複と左端プレフィックスの冗長性を検出します（重複は純粋に構造上のものなので established）。未使用インデックスの検出は実行時のみ可能であり、Tier-0 では status: needs_api を出力します（黙って合格にはしません）。Tier-2 を促すメッセージも付けます。

Tier-1/2（検証クエリ、Postgres）

重複／冗長（Tier-1 カタログ）：

SELECT indrelid::regclass AS tbl, array_agg(indexrelid::regclass) AS dupes
FROM pg_index GROUP BY indrelid, indkey, indclass, indpred
HAVING count(*) > 1;

未使用（Tier-2、継続的な統計情報が必要）：

SELECT relname AS tbl, indexrelname AS idx, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS sz
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

メソッドは index_check / query_stat です。Tier-2 の結果は M12.*.unused を established として確認します。代表的な期間のデータがない場合は directional のままとなり、判断に統計情報が必要な場合は needs_api になります。

検出結果

schema/finding.schema.json に従って検出結果を出力します。例：

• M12.users.duplicate_email_index — 同じキーを持つ2つのインデックス（severity:2、warn、DDL に基づく信頼度 established、fixable: proposed — DROP INDEX）。
• M12.orders.redundant_prefix_index — (status) が (status, created_at) でカバーされている場合（severity:1、warn、established／directional、fixable: proposed）。
• M12.events.unused_index — 期間中のスキャンがゼロの場合（severity:2、warn、Tier-2 では established／Tier-0 では needs_api、fixable: proposed）。

各検出結果では、evidence.observed にインデックスの DDL またはカタログ／統計情報の行を一字一句そのまま引用します。verification.reproduce は $DATABASE_URL を参照する上記のクエリです。expected_impact は段階と信頼度を付与します（裸の % は使用しません）。

信頼性

• 短い期間に未使用のインデックスでも、月末／四半期ごとのジョブで使われる可能性があります。必ず代表的な期間を確認し、短期間の判定は established ではなく directional とします。
• スキャンで「未使用」であっても、一意性／制約を支えるインデックスは削除できません。整合性を担保するものなので、削除を推奨してはいけません。
• 削除は proposed（auto にはしません）とします。大規模環境では削除を簡単には元に戻せず、意図的に設けられている可能性もあります。修正プレビューでは DROP INDEX CONCURRENTLY を提案します。