Print redundant indexes in a Rails app.

print_redundant_indexes.rb

# Unnecessary indexes slows down writes and consumes additional storage and memory.

# Just paste this snippet in your Rails console (bundle exec rails c).
# And it will print all redundant indexes that are already covered by another index on the table:
#   Table `pages`:  index `site_idx` (site_id) already covered by `site_slug_idx`   (site_id,slug)
#   Table `optins`: index `list_idx` (list_id) already covered by `list_active_idx` (list_id,active)


ActiveRecord::Base.connection.tables.map do |table|
  indexes = ActiveRecord::Base.connection.indexes(table).select(&:valid).reject(&:where) 
  indexes.each do |i|
    i_columns = Array(i.columns)
    indexes.each do |j|
      next if i == j
      j_columns = Array(j.columns)
      next unless j_columns.join('///').starts_with?(i_columns.join('///'))
      puts "Table `#{table}`: index `#{i.name}` (#{i_columns.join(',')}) already covered by `#{j.name}` (#{j_columns.join(',')})"
      break
    end
  end
end; nil


## Interested in a powerful Rails UI library? Please check out: https://owaiskhan.me/rails-ui-library/

I got sometimes index like : "lower((email)::text)" which with your script will cause

undefined method 'join' for an instance of String (NoMethodError)

You can patch it with

ActiveRecord::Base.connection.tables.map do |table|
  indexes = ActiveRecord::Base.connection.indexes(table).select(&:valid).reject(&:where)
  indexes.each do |i|
    i_columns = Array(i.columns)

    indexes.each do |j|
      next if i == j

      j_columns = Array(j.columns)
      next if !j_columns.join('///').starts_with?(i_columns.join('///'))

      puts "Table `#{table}`: index `#{i.name}` (#{i_columns.join(',')}) already covered by `#{j.name}` (#{j_columns.join(',')})"
      break
    end
  end
end ; nil

I got sometimes index like : "lower((email)::text)" which with your script will cause undefined method join' for an instance of String (NoMethodError)`

You can patch it with

ActiveRecord::Base.connection.tables.map do |table|
  indexes = ActiveRecord::Base.connection.indexes(table).select(&:valid).reject(&:where)
  indexes.each do |i|
    i_columns = Array(i.columns)

    indexes.each do |j|
      next if i == j

      j_columns = Array(j.columns)
      next if !j_columns.join('///').starts_with?(i_columns.join('///'))

      puts "Table `#{table}`: index `#{i.name}` (#{i_columns.join(',')}) already covered by `#{j.name}` (#{j_columns.join(',')})"
      break
    end
  end
end ; nil

Interesting. Didn't realize #columns would ever not return an array. will update. thanks.