インデックスの「死に体」をあぶり出す――`pg_stat_user_indexes`と向き合う夜
データベースを長く運用していると、必ず「肥満」の問題に直面します。テーブルのデータが増えるのは業務の成長だから喜ばしい。けれど、インデックスの数まで際限なく増えていくのは、大抵の場合、過去の誰かが「とりあえず」で貼った付箋の成れの果てです。
今日は、そんな「お荷物インデックス」を静かに、かつ確実に葬り去るための話をしましょう。PostgreSQLの深淵を覗く、`pg_stat_user_indexes`という名のコンパスについて。
インデックスは「負債」になり得る
「インデックスは読み込みを高速化する魔法だ」と信じているエンジニアほど、実は危うい実装をしています。インデックスは、書き込み（INSERT/UPDATE/DELETE）のたびにコストを支払う「維持費のかかる不動産」です。
特に、更新頻度が高いテーブルにおいて、使われていないインデックスは単なるノイズです。不要なインデックスが存在するだけで、WALの生成量が増え、ページキャッシュの効率が落ち、VACUUMの負荷さえ高まる。つまり、使われないインデックスは、システム全体のパフォーマンスを食いつぶす「負債」そのものなのです。
`pg_stat_user_indexes`が語る真実
PostgreSQLには、インデックスの運用状況を監視するための非常に強力な統計ビューが用意されています。
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_user_indexes
WHERE
schemaname = ‘public’
ORDER BY
idx_scan ASC;
このクエリを叩いたとき、`idx_scan`（スキャン回数）が極端に低いインデックスを見つけたら、まずは疑ってください。
ただし、注意が必要です。ここでの罠は「期間」です。サーバーが再起動した瞬間、これらの統計値はリセットされます。もし直近1ヶ月でスキャン回数が0だとしても、それが「過去1年間一度も使われていない」ことを意味するとは限りません。四半期決算の時だけ動くバッチ処理で使われている可能性だってあります。
経験則として言えるのは、「インデックスの利用状況は、最低でも業務サイクルを一周させるまでは判断を保留すべき」ということです。
慎重を期すための「無効化」アプローチ
もし、不要と思われるインデックスを見つけたとしても、いきなり`DROP INDEX`を叩くのは素人のやり方です。DBAの嗜みとして、まずは「使われない状態」を作って様子を見ましょう。
PostgreSQL 14以降であれば、`pg_index`の`indisvalid`フラグをいじる荒業もありますが、もっと安全なのはインデックスの利用を抑止することです。
1. インデックスを不可視にする（PostgreSQL 11以降）
`ALTER INDEX index_name SET (visible = false);`
これを実行すると、オプティマイザはクエリプランの作成時にそのインデックスを無視します。ですが、インデックス自体はバックグラウンドで更新され続けます。
2. 影響を計測する
この状態で数日間運用し、スロークエリが発生しないか、監視ツール（`pg_stat_statements`など）で確認します。もし何も問題が起きなければ、そのインデックスは本当に不要だったという証拠です。
3. ドロップする
晴れて、安心して`DROP INDEX`を実行できます。
パフォーマンスチューニングの極意
私が現場でよくやるのは、`idx_scan`の回数だけでなく、`idx_tup_read`と`idx_tup_fetch`の比率を見ることです。
- `idx_tup_read`: インデックスを通って読み込まれたインデックスエントリ数
- `idx_tup_fetch`: インデックスを通じて実際にテーブルから取得された行数
この二つの数値が乖離している場合、インデックスは効いているけれど、不必要に広範囲をスキャンしている可能性があります。複合インデックスの順序を見直すべきサインです。
最後に
データベースエンジニアの仕事は、インデックスを増やすことではありません。「本当に必要なインデックスだけを残し、システムの呼吸を軽くすること」こそが、熟練の職人の領域です。
定期的に`pg_stat_user_indexes`を眺める時間は、データベースという巨大な機械の「健康診断」です。皆さんの環境でも、今日、一度その統計を覗いてみてはいかがでしょうか。そこには、忘れ去られたインデックスたちの、ひっそりとした溜息が聞こえるかもしれませんよ。
コメント