Skip to content

Instantly share code, notes, and snippets.

@owaiswiz
Last active September 5, 2024 04:42
Show Gist options
  • Save owaiswiz/89c13a55a663962f86f349fcac27de07 to your computer and use it in GitHub Desktop.
Save owaiswiz/89c13a55a663962f86f349fcac27de07 to your computer and use it in GitHub Desktop.
Print redundant indexes in a Rails app.
# 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/
@navidemad
Copy link

navidemad commented Mar 4, 2024

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

@owaiswiz
Copy link
Author

owaiswiz commented Mar 4, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment