【実務・中級編】 pg_stat_user_indexes – PostgreSQL

「インデックスを作れば速くなる」。エンジニアになりたての頃、僕もそう信じて疑いませんでした。でも、実務で数年揉まれてくると気づくんですよね。「インデックスは、ただ作ればいいってもんじゃない」という事実に。

使われないインデックスは、ただの「お荷物」です。書き込みのたびに更新コストを食い、ストレージを圧迫する。まさに百害あって一利なし。

今日は、そんな「放置されたインデックス」を炙り出し、データベースをスリムに保つための最強の武器、`pg_stat_user_indexes` について深掘りしていきます。

—

なぜインデックスを掃除する必要があるのか？

PostgreSQLに限らず、インデックスは「読み取りを速くする魔法の杖」ですが、同時に「書き込みを遅くする足かせ」でもあります。`INSERT` や `UPDATE` が走るたびに、関連するすべてのインデックスを更新しなきゃいけないからです。

もし、そのインデックスがほとんど検索に使われていなかったら？……もったいないですよね。不要なインデックスを削除するだけで、書き込み性能が劇的に改善することだって珍しくありません。

pg_stat_user_indexes で現状を把握する

PostgreSQLには、インデックスごとの利用状況を記録してくれる素晴らしいビューがあります。それが `pg_stat_user_indexes` です。

まずは、どんなデータが取れるのか覗いてみましょう。

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’;

このクエリで見るべきは、何といっても `idx_scan` です。これは「そのインデックスが何回スキャンに使われたか」を表すカウンターです。

`idx_scan` が「0」に近い: このインデックスは、DBが起動してから一度も（あるいはほとんど）使われていません。
`idx_scan` は多いが `idx_tup_fetch` が極端に少ない: インデックスは使われているけど、結局テーブル本体まで読みに行っているかも？インデックスの効率化（Covering Indexなど）の余地があるかもしれません。

実践：未使用インデックスを叩き出すクエリ

実務でよく使う「削除候補リスト」を作るクエリを書いてみました。これを定期的に実行して、長期間使われていないものを洗い出します。

SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE ‘%_pkey’ — 主キーは消しちゃダメ！
AND indexrelname NOT LIKE ‘%_key’ — ユニーク制約も慎重に
ORDER BY relname;

※注意点ですが、データベースを再起動したり、統計情報をリセットしたりするとこのカウンターもゼロに戻ります。なので、「最近稼働したばかりのDB」でこれを見ると、全部未使用判定されて泣きを見ることになります。最低でも数週間、可能なら繁忙期を跨いで運用した後のデータを見てくださいね。

削除する前の「最後の一押し」

「よし、`idx_scan` が 0 だから削除しよう！」……と勇む前に、一つだけやってほしいことがあります。

それは、`pg_stat_user_indexes` を確認する前に、クエリログや `pg_stat_statements` も併用することです。

ごく稀に、月末のバッチ処理でしか使われない超重要なインデックスがあったりします。そういうのは、統計情報の更新タイミングによっては「使われていない」と誤認されるリスクがあるんです。

僕のオススメは、まずは `DROP` ではなく `REINDEX` や `ALTER INDEX … SET (tablespace = ‘…’)` で退避させたり、あるいは一旦 `ALTER INDEX index_name SET (visible = false);`（PostgreSQL 15以降なら！）で非表示にして様子を見るというアプローチです。

まとめ：インデックスは「庭の草むしり」

DBの運用は、庭の手入れに似ています。インデックスを作りっぱなしにするのは、雑草を放置してジャングルにするようなもの。

1. `pg_stat_user_indexes` で `idx_scan` を定期的にチェックする。
2. 削除候補を見つけたら、本当に使われていないか一応確認する。
3. 勇気を持って削除し、DBを軽量化する。

このサイクルを回せるようになると、パフォーマンスに悩まされることがグッと減りますよ。ぜひ、今のプロジェクトのDBで `SELECT FROM pg_stat_user_indexes` を叩いてみてください。「こんなのあったんだ！」という発見が必ずあるはずです。

それでは、また現場で！