「ねえ、データベースがなんだか最近重いんだよね」……そんな相談を受けたとき、真っ先に僕が確認するのが「そのインデックス、本当に必要？」という視点です。
PostgreSQLを運用していると、パフォーマンス向上のためにインデックスをどんどん追加したくなる気持ち、すごくよく分かります。でも、インデックスは「読み取りを速くする魔法の杖」であると同時に、「書き込みを遅くする重石」でもあるんですよね。
今日は、そんなインデックスの「断捨離」に欠かせない、現場のエンジニアなら絶対におさえておくべき `pg_stat_user_indexes` という相棒について語らせてください。
—
インデックスは「作って終わり」じゃない
インデックスを作成すると、テーブルへの `INSERT` や `UPDATE` が発生するたびに、データベースはそのインデックスも更新しなきゃいけない。つまり、使われていないインデックスは、ただの「書き込み性能を奪うだけの無駄な荷物」なんです。
これを特定するために、PostgreSQLには非常に優秀なシステムビューが用意されています。それが `pg_stat_user_indexes` です。
まずは「使われていないやつ」を炙り出そう
早速ですが、実務で一番よく使うクエリを共有します。これ、僕の管理画面の「お気に入り」に入っているSQLです。
SELECT
relname AS table_name,
indexrelname AS index_name,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM
pg_stat_user_indexes
WHERE
idx_scan = 0 — スキャン回数がゼロ
AND indexrelname NOT LIKE ‘%_pkey’ — 主キーは除外
ORDER BY
relname;
このクエリを流したとき、もし「おっ、意外と使われてないインデックスがあるな」と気づいたら、それが改善のチャンスです。
注意！「ゼロ」を鵜呑みにするのは危険
ここで一つ、現場の先輩として重要なアドバイスがあります。
`idx_scan = 0` だからといって、即座に `DROP INDEX` してはいけません。
なぜなら、この統計情報は「PostgreSQLが起動してからの累積」だからです。もし、そのクエリが「四半期に一度しか走らない集計バッチ」で使われているものだったら？ あるいは、月次の売上締め処理でしか使われないものだったら？
うっかり消してしまうと、そのタイミングでシステムが悲鳴を上げることになります。
- 運用期間を確認する: `pg_stat_database` などで統計情報がいつリセットされたか確認しましょう。
- デプロイサイクルを考える: 少なくとも、業務サイクル（月次、四半期など）を一周する期間は様子を見るのが鉄則です。
「使われている」けど「効率が悪い」ケース
`idx_scan` がゼロじゃなくても、安心はできません。
`idx_tup_read`（インデックスから読み取った行数）と `idx_tup_fetch`（そこから実際にヒープ（テーブル本体）まで読みに行った行数）を比較してみてください。
もし `idx_tup_read` がやたらと多いのに `idx_tup_fetch` が少ないなら、それはインデックスが広範囲をスキャンしすぎていて、効率が落ちている証拠かもしれません。複合インデックスの順序を見直すか、`WHERE` 句の条件を見直すタイミングです。
最後に：インデックスは「庭の手入れ」と同じ
インデックスの整理って、庭の手入れに似ています。放っておくと雑草（未使用インデックス）が伸び放題になって、大事な花（クエリのパフォーマンス）が育たなくなる。
面倒に感じるかもしれませんが、定期的に `pg_stat_user_indexes` を眺めて、「お前、最近働いてないな？」と対話してあげる。そうやってデータベースを身軽に保つのが、熟練エンジニアの流儀です。
もし「インデックスを消すのが怖い」というなら、まずは `SET enable_indexscan = off;` してテスト環境でクエリを流してみるのも一つの手です。
皆さんのデータベースが、今日も健やかに爆速でありますように。また、現場で役立つTIPSがあれば共有しますね！
コメント