This script is to check unused DB indexes in Postgresql DB through rails console

Rails_postgresql_check_unused_indexes_db.rb

results = ActiveRecord::Base.connection.execute("
    SELECT
    idstat.relname AS TABLE_NAME,
    indexrelname AS index_name,
    idstat.idx_scan AS index_scans_count,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    tabstat.idx_scan AS table_reads_index_count,
    tabstat.seq_scan AS table_reads_seq_count,
    tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
    n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
    pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
    pg_stat_user_indexes AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC")

results.to_a.select{|x| x["index_scans_count"] == 0}



# Now in case u r looking for unused indexes with index size is Greater than some threashold value (Eg: 100 MB)

def convert_size_to_bytes(size)
  size_value, size_unit = size.split(" ")
  bytes = size_value.to_i

  case size_unit
  when "KB"
    bytes *= 1024
  when "MB"
    bytes *= 1024 * 1024
  when "GB"
    bytes *= 1024 * 1024 * 1024
  end

  bytes
end

threshold_size = 10 * 1024 * 1024  # 100MB in bytes

unused_r = results.to_a.select do |x|
  size_in_bytes = convert_size_to_bytes(x["index_size"])
  x["index_scans_count"] == 0 && size_in_bytes >= threshold_size
end.map do |x|
  {
    "table_name" => x["table_name"],
    "index_name" => x["index_name"],
    "index_scans_count" => x["index_scans_count"],
    "index_size" => x["index_size"]
  }
end

unused_r