query.sql

query.sql

SELECT
  i.relname AS index_name,
  t.relname AS table_name,
  s.idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(i.oid)) AS index_size         
FROM
  pg_stat_user_indexes s
JOIN
  pg_index ix
ON
  s.indexrelid = ix.indexrelid
JOIN
  pg_class t
ON
  s.relid = t.oid
JOIN
  pg_class i
ON
  s.indexrelid = i.oid
ORDER BY
  s.idx_scan DESC;