SELECT  c.relname tablename,c2.relname indexname, a.idx_scan, a.idx_tup_read, idx_tup_fetch, c2.relpages*8/1024 as index_size_mb
FROM pg_class c
         inner join pg_index i on c.oid = i.indrelid
         inner join pg_class c2 on c2.oid = i.indexrelid
         inner join pg_indexes iv on c2.relname=iv.indexname
         left join pg_stat_user_indexes a on  c2.relname=a.indexrelname
where c.relname not like('pg_%') and c2.relname not like('%_pkey')
order by c2.relpages desc;
  • idx_scan 索引被扫描次数
  • idx_tup_read 命中索引的条数
  • idx_tup_fetch 命中索引后的实际数据量
  • index_size_mg 索引占用储存空间(MB)