PostgreSQL で使われていないインデックスを調べる

PostgreSQL で使われていないインデックスを調べる方法について。いつも忘れるのでこちらにも書く。

統計情報の収集

まずは、インデックスの使用状況を統計情報として残すよう、PostgreSQL の設定ファイル postgresql.conf で以下の変数を true にする。

• stats_start_collector
• stats_row_level

各々の詳細についてはリンク先の PostgreSQL ドキュメントを参照してほしい。

統計情報ビュー

収集した統計情報の表示のために多くのビューが用意されている。

使われていないインデックスを調べるには pg_stat_user_indexes を見るのが手っ取り早い。

select indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from pg_stat_user_indexes
where
  indexrelname not like '%_pkey' and
  idx_scan = 0;

プライマリーキーっぽいものは除いてみた。

indexrelname がインデックスの名前、relname がテーブルの名前。なので、特定のテーブルのインデックスに絞ることも簡単にできる。

select indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from pg_stat_user_indexes
where
  relname = 'users';