PostgreSQL查看索引的使用情况

查看某个表的索引使用情况

select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = table_name order by idx_scan asc, idx_tup_read asc, idx_tup_fetch asc;

查看所有表的索引的使用情况

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

存放索引详细信息的表

1data_name=# \d+ pg_stat_user_indexes;
2           View "pg_catalog.pg_stat_user_indexes"
3    Column     |  Type  | Modifiers | Storage | Description 
4---------------+--------+-----------+---------+-------------
5 relid         | oid |           | plain | 
6 indexrelid    | oid |           | plain | 
7 schemaname    | name |           | plain | 
8 relname       | name |           | plain | 
9 indexrelname  | name |           | plain | 
10 idx_scan      | bigint |           | plain | 
11 idx_tup_read  | bigint |           | plain | 
12 idx_tup_fetch | bigint |           | plain | 
13View definition:
14 SELECT pg_stat_all_indexes.relid,
15    pg_stat_all_indexes.indexrelid,
16    pg_stat_all_indexes.schemaname,
17    pg_stat_all_indexes.relname,
18    pg_stat_all_indexes.indexrelname,
19    pg_stat_all_indexes.idx_scan,
20    pg_stat_all_indexes.idx_tup_read,
21    pg_stat_all_indexes.idx_tup_fetch
22   FROM pg_stat_all_indexes
23  WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;

pg_stat_user_indexs 表的详细解释参考官方文档：

https://www.postgresql.org/docs/9.2/monitoring-stats.html