Report index usage (save every few hours and see difference to spot unused indexes)

index_usage.rb

sql = <<-SQL
SELECT
  s.indexrelname AS indexname,
  pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
  s.idx_scan AS number_of_scans
FROM
  pg_catalog.pg_stat_user_indexes s
  JOIN pg_catalog.pg_index i ON s.indexrelid = i.indexrelid
WHERE
  0 <> ALL (i.indkey) -- no index column is an expression
  AND NOT i.indisunique -- is not a UNIQUE index
  AND NOT EXISTS -- does not enforce a constraint
  (
    SELECT 1 FROM pg_catalog.pg_constraint c
    WHERE c.conindid = s.indexrelid
  )
  AND NOT EXISTS -- is not an index partition
  (
    SELECT 1 FROM pg_catalog.pg_inherits AS inh WHERE inh.inhrelid = s.indexrelid
  )
ORDER BY
  indexrelname;

SQL
results= ActiveRecord::Base.connection.execute(sql)
results.values.each {|f| puts "#{f[0].to_s.ljust(70)} | #{f[1].to_s.rjust(10)} | #{f[2].to_s.rjust(10)}"};nil