unused index会导致不必要的io开销,如写操作(insert、update)期间,必须维护所有index
1.鉴于pg_stat_reset()函数能够reset统计信息到0,查找unused index时,先参考pg_stat_database的stats_reset的栏位的reset时间,只有一定时间跨度的统计信息才有意义
select stats_reset from pg_stat_database
2.之后可以结合pg_index、pg_stat_user_index视图查找未使用的index
select indrelid::regclass tab_name,pi.indexrelid::regclass unused_index,idx_scan
from
pg_index pi,pg_stat_user_indexes psui
where pi.indexrelid=psui.indexrelid and not indisunique and
idx_scan=0
3.注意,定位到未使用的index后,不要急于直接drop这些index,先使用如下sql去mark这些index为invalid,再花一段时间观察应用程序行为,如自从mark index invalid后,是否遇到效能问题等,确认无问题后,可使用concurrently去drop索引
UPDATE pg_index SET indisvalid = false
WHERE indexrelid::regclass::text IN ( < Unused indexes name> );