PostgreSQL インデックスが使われているかの確認

postgresql.conf で stats_row_level = on にしてpostgresを再起動する。
(設定のリロードでもいいかも)

次のSQLを実行する。

select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
from pg_stat_user_indexes order by relname, indexrelname;

例:

stock=> select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes order by relname, indexrelname;
    relname     |     indexrelname     | idx_scan | idx_tup_read | idx_tup_fetch
----------------+----------------------+----------+--------------+---------------
 accounts       | accounts_pkey        |  1635951 |      1608087 |       1604745
 execution      | execution_pkey       |        3 |            3 |             3
 idx_dji        | idx_dji_pkey         |        0 |            0 |             0
 idx_nk         | idx_nk_pkey          |     3547 |         4761 |          3762
 industry_tse   | industry_tse_pkey    |        0 |            0 |             0
 price_history  | price_history_pkey   |  8612746 |      9661302 |       8645936
 stock          | stock_pkey           |   365375 |       365151 |        364996
 stock_kininaru | stock_kininaru_pkey  |       42 |         2536 |          2530
 tdnet          | tdnet_idx_stock_code |     1561 |        18914 |             0
(9 rows)