【テクニカル・上級編】 pg_stat_user_indexesビュー – PostgreSQL

インデックスの「死に体」を見抜く：pg_stat_user_indexesの深淵

データベースエンジニアとして長く現場に立っていると、ふとした瞬間に背筋が寒くなることがある。「このインデックス、本当に必要だろうか？」という疑念だ。

開発初期に念入りに設計したインデックスも、アプリケーションの進化やクエリパターンの変容とともに、いつの間にか「ただの重り」に成り下がっていることが少なくない。書き込みのたびに更新コストを支払い、ストレージを消費し、バッファキャッシュを汚染し続ける……。そんな「死に体」のインデックスを放置するのは、プロフェッショナルとしてあまりに忍びないものだ。

そんな時、我々がまず立ち返るべき場所が `pg_stat_user_indexes` だ。今日はこのビューを単なる「使用状況確認ツール」としてではなく、運用中のデータベースの健康を保つための「診察チャート」として活用する知見を共有したい。

—

統計情報の「嘘」を見抜く眼力

`pg_stat_user_indexes` は、その名の通りインデックスの利用状況を覗く窓口だ。`idx_scan`（スキャン回数）がゼロに近いなら、それは確かに不要なインデックスかもしれない。しかし、現場ではそんな単純な話ばかりではない。

まず心に留めておいてほしいのは、「スキャン回数はゼロではないが、実は役に立っていない」というケースだ。

統計情報の保持期間: `pg_stat_reset()` がいつ実行されたか、あるいはデータベースが最後に再起動したのはいつか。統計情報はあくまで「その時点からの累積」に過ぎない。デプロイ直後の計測ではインデックスの真価は見えてこない。
プランナの気まぐれ: 特定の条件下でのみ実行されるバッチ処理でしか使われないインデックスは、通常時の `idx_scan` には現れにくい。不用意に削除して、月末の集計処理が阿鼻叫喚の地獄絵図になる……なんて話は、エンジニア界隈の笑えない都市伝説だ。

内部アーキテクチャから読み解くパフォーマンスへの影響

インデックスの評価をする際、`idx_scan` だけを見て判断するのは素人仕事だ。我々が見るべきは、その裏側にある「コスト」である。

PostgreSQLにおいてインデックスは、書き込み処理（INSERT/UPDATE）が発生するたびにオーバーヘッドを生む。`pg_stat_user_indexes` を見るときは、以下の観点を常にセットで考えるべきだ。

HOT (Heap Only Tuple) 更新の阻害要因になっていないか:

もしインデックスキーが頻繁に更新されるカラムであれば、そのインデックスはHOT更新を阻害し、断片化を加速させる。統計情報と合わせて、そのテーブルの `n_tup_upd` と照らし合わせるのが定石だ。

インデックスの「密度」とキャッシュ効率:

`idx_tup_read`（インデックスをスキャンして読み取った行数）と `idx_tup_fetch`（その結果、実際にテーブルから読み出した行数）の乖離が大きい場合、そのインデックスは「絞り込み性能」が低いことを示唆している。インデックスは存在するものの、結局テーブルの大部分をスキャンしてしまっているなら、インデックスを再設計するか、あるいはそもそもインデックスを外してシーケンシャルスキャンに任せた方が早い場合すらある。

現場で使える「インデックス精査」の流儀

私が未使用インデックスを整理する際は、以下のステップを泥臭く実行する。

1. 「猶予期間」を設ける: 統計情報をリセットしてから、少なくとも1サイクル（日次、週次、月次のバッチが一周する期間）は様子を見る。
2. `pg_stat_user_indexes` を横断的に分析する: 以下のクエリで、スキャン回数が極端に低いインデックスを抽出する。

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan < 10 -- 閾値は業務に合わせて調整 AND indexrelname NOT LIKE '%_pkey' -- PKは消さないのが鉄則 AND indexrelname NOT LIKE '%_key'; -- ユニーク制約も慎重に 3. まずは「無効化」で検証する: いきなり `DROP` する勇気は必要ない。`ALTER INDEX … SET (visible = false)`（PostgreSQL 11以降）を使うんだ。インデックスを削除せず、プランナから隠すことで、万が一の際に即座に復旧できる。「消す」のではなく「見えなくする」というステップを挟むことで、心理的にも技術的にも安全な運用が可能になる。

最後に：データベースは生き物である

インデックスの断捨離は、単なる掃除ではない。データベースという複雑怪奇なシステムを、よりシンプルで高速な状態へとチューニングしていく「彫刻」のような作業だ。

`pg_stat_user_indexes` を眺めていると、そのテーブルがどのようなクエリに晒され、どのような負荷を受け止めているのか、テーブルの「呼吸」が聞こえてくるような気がする。

エンジニアとして、常にデータベースの健康状態に気を配ろう。不要なものを削ぎ落とし、必要な場所にだけリソースを注ぐ。その積み重ねが、何年経っても揺るがない、最高峰のパフォーマンスを生み出すのだから。