使用していないインデックスを見つける

普段は検索性能向上のために黙々と頑張ってくれているインデックス。
だが、中にはこっそりサボっているインデックスもいる。
アプリケーションの設計変更やデータ量、値の偏り、バージョンアップによる実行計画の変更等、
色々なケースでインデックスを使用しなくなることがあり得る。

使用していないインデックスは、役に立たないだけではなく、更新処理が遅くなる、Vacuum時に無駄なサーバーリソースを
消費する等、迷惑きわまりない存在に成り果てる。

なので、定期的にインデックスを使用しているかどうかチェックして、不要な場合は削除しましょう。

ということで、今回は使われていないインデックスを特定する方法について書いてみる。

使用するのは、標準統計情報ビューのpg_stat_user_indexes。

pg_stat_user_indexes

主なカラムは以下の通り。

列	データ型	説明
relname	name	テーブル名
indexrelname	name	インデックス名
idx_scan	bigint	インデックススキャンの実行回数
idx_tup_read	bigint	読みとられたインデックスタプル数
idx_tup_fetch	bigint	このインデックスを使用したインデックススキャンで抽出された有効なテーブル行数

以下が実際に実行したサンプルです。

testdb=# SELECT
testdb-#     relname ,
testdb-#     indexrelname ,
testdb-#     idx_scan ,
testdb-#     idx_tup_read ,
testdb-#     idx_tup_fetch
testdb-# FROM
testdb-#     pg_stat_user_indexes
testdb-# ORDER BY
testdb-#     relname ,
testdb-#     indexrelname;

 relname | indexrelname       | idx_scan  | idx_tup_read | idx_tup_fetch















































































































































-







































































 cnt     | cnt_pkey           | 950457987 |     91594968 |      91562375
 comment | comment_pkey       |     32237 |      7810386 |       7810111
 comment | comment_tid_res_id |  12560263 |     59697535 |      59673333
 comment | comment_uid        |   1718765 |     70085458 |      70083194
 genre   | genre_pkey         | 431746352 |    431746352 |     431746352
 thread  | thread_last_upd    |      2859 |      7176586 |       6969599
 thread  | thread_pkey        |  75243881 |     76019793 |      75202913
 thread  | thread_sum_comment |         0 |            0 |             0
 thread  | thread_uid         |   6604740 |     61243842 |      60595664
 vote    | vote_pkey          |   9042530 |     48153652 |      48153646
 vote    | vote_tid_uid       |         0 |            0 |             0

今回のサンプルでは、threadテーブルのthread_sum_commentインデックスとvoteテーブルのvote_tid_uidインデックスが
idx_scanの値が0なので、今まで一度も使用されていないことが分かる。

注意点1

pg_stat_user_indexesの有効な情報を見るには、以下の設定を行う必要があります。
PostgreSQL8.2以前のバージョンでは、stats_row_levelパラメータがデフォルトでは無効になっているので有効にする
必要があります。PostgreSQL8.3以降では、パラメータの名前がtrack_countsと変更されて、デフォルトで有効となっています。

注意点2

pg_stat_user_indexesは統計情報なので、このビューの値はデータベースの作成時、またはサーバの再起動時からの累積値となっています。
一度も使われていないものは、idx_scanの値が0なので分かりやすいが途中で使われなくなったケースでは1度見ただけでは分かりません。

そのため、pg_stat_reset()関数を使用して統計情報をリセットするか、ある程度の期間を空けて2回以上pg_stat_user_indexesを確認し
差分を取ってみるかしないと分からないケースもあります。
（月次バッチのみ使っているインデックスとかもあり得るのでその辺りはアプリケーションを確認する必要がある）