【PostgreSQL】pg_stat_user_indexesで使われていないインデックスを見つける

pg_stat_user_indexesとは

pg_stat_user_indexesは、インデックスごとの使用状況を集計するビューである。idx_scan列でそのインデックスが何回検索に使われたかを確認できる。

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'accounts'
ORDER BY idx_scan;

 schemaname | relname  |     indexrelname     | idx_scan | idx_tup_read | idx_tup_fetch 
------------+----------+----------------------+----------+--------------+---------------
 public     | accounts | accounts_pkey        |        0 |            0 |             0
 public     | accounts | idx_accounts_email   |        0 |            0 |             0
 public     | accounts | idx_accounts_balance |        3 |            0 |             0

idx_scan: インデックスが検索に使われた回数
idx_tup_read: インデックスから読み取られたエントリ数
idx_tup_fetch: インデックス経由でテーブル本体から取得された行数

balance列を条件に3回検索したidx_accounts_balanceはidx_scanが3になっている一方、email列を条件にした検索を一度も実行していないidx_accounts_emailはidx_scanが0のままである。

未使用インデックスを絞り込む

idx_scanが0のインデックスは、サーバー起動後（または統計リセット後）一度も検索に使われていないインデックスである。ただし主キーやUNIQUE制約用のインデックスは、検索に使われていなくても制約の維持に必要なため、除外して確認する。

SELECT
  s.schemaname,
  s.relname,
  s.indexrelname,
  s.idx_scan,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

 schemaname | relname  |    indexrelname    | idx_scan | index_size 
------------+----------+--------------------+----------+------------
 public     | accounts | idx_accounts_email |        0 | 680 kB

pg_indexと結合してindisunique・indisprimaryが偽の行だけに絞ることで、accounts_pkeyのような制約用インデックスを除外し、idx_accounts_emailのような未使用インデックスだけを抽出できる。index_sizeでサイズも確認できるため、削除の優先順位付けにも使える。

統計情報の注意点

idx_scanが0でも、そのインデックスが不要とは断定できない。以下の点に注意する。

統計はクラッシュや異常終了でクリアされる: 正常なシャットダウン・再起動では統計情報はpg_statディレクトリに保存され、再起動後も引き継がれる。クラッシュやimmediateシャットダウンからの起動、ベースバックアップからの起動、ポイントインタイムリカバリ、およびpg_stat_reset()の実行時には統計がリセットされる。特定のインデックスだけをリセットするにはpg_stat_reset_single_table_countersを使う。

SELECT pg_stat_reset_single_table_counters('idx_accounts_balance'::regclass);

実行頻度の低いバッチ処理: 月次バッチや年次処理でしか使われないインデックスは、観測期間が短いと未使用に見えてしまう。十分な期間統計を蓄積してから判断する。
レプリカでは別集計になる: レプリケーション構成では、各サーバーが自身で実行したクエリの統計のみを保持する。読み取りをレプリカに向けている場合、プライマリ側のidx_scanだけを見ると実際より使われていないように見える。

これらの点を踏まえたうえで、十分な期間idx_scanが0のままのインデックスは、削除の候補として検討する。