KDOC 662: 使われていないインデックスは pg_stat_user_indexes で見つけられる

この文書のステータス

  • 作成
    • 2026-10-14 貴島
  • レビュー
    • 2026-10-14 貴島

概要

使われていないインデックスを見つけるために pg_stat_user_indexes.idx_scan が使えるという。

idx_scan bigint

インデックスに対して開始されたインデックススキャンの実行回数です。

27.2. 累積統計システム

2つのインデックスのうち片方 t_a だけを使う問い合わせを100回流して、idx_scan を見る。統計は待機状態へ入る直前に反映されるが、1秒に1回までなので、流した直後に同じ接続で読むと 0 に見える。pg_stat_force_next_flush() で次の反映を早めてから読む。 

CREATE TABLE t (a int, b int);
CREATE INDEX t_a ON t (a);
CREATE INDEX t_b ON t (b);
INSERT INTO t
SELECT i, i FROM generate_series(1, 10000) i;
ANALYZE t;

-- t_a だけを100回使う
DO $$
BEGIN
  FOR i IN 1..100 LOOP
    PERFORM * FROM t WHERE a = i;
  END LOOP;
END $$;
SELECT pg_stat_force_next_flush();

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;

|                                                 |          |
|-------------------------------------------------+----------|
| > CREATE TABLE t (a int, b int);                |          |
| CREATE TABLE                                    |          |
|                                                 |          |
| > CREATE INDEX t_a ON t (a);                    |          |
| CREATE INDEX                                    |          |
|                                                 |          |
| > CREATE INDEX t_b ON t (b);                    |          |
| CREATE INDEX                                    |          |
|                                                 |          |
| > INSERT INTO t                                 |          |
| > SELECT i, i FROM generate_series(1, 10000) i; |          |
| INSERT 0 10000                                  |          |
|                                                 |          |
| > ANALYZE t;                                    |          |
| ANALYZE                                         |          |
|                                                 |          |
| > DO $$                                         |          |
| > BEGIN                                         |          |
| >   FOR i IN 1..100 LOOP                        |          |
| >     PERFORM * FROM t WHERE a = i;             |          |
| >   END LOOP;                                   |          |
| > END $$;                                       |          |
| DO                                              |          |
|                                                 |          |
| > SELECT pg_stat_force_next_flush();            |          |
| pg_stat_force_next_flush                        |          |
|                                                 |          |
|                                                 |          |
| > SELECT indexrelname, idx_scan                 |          |
| > FROM pg_stat_user_indexes                     |          |
| > ORDER BY idx_scan;                            |          |
| indexrelname                                    | idx_scan |
| t_b                                             |        0 |
| t_a                                             |      100 |

100回使った t_a は idx_scan が 100、使っていない t_b は 0 だった。0 のインデックスは消す候補になる。

関連