index_scan.rb

# A dumb script to scan your Rails schema file
# and detect indexes which may be unnecessary.
# Made with reckless abandon by Tom Gamon
# http://tomgamon.com

# ruby index_scan.rb db/schema/rb

schema = ARGF.read

creates_table = /create_table.*?end/ms
table_name = /create_table "(.*?)".*?end/ms
index_name = /t\.index (.*?), n/
column = /\w+/

# Loop over the table definitions
schema.scan(creates_table).each do |table|
  # Pull out the columns used in the indexes
  indexes = table.scan(index_name).map do |index|
    index.first.scan(column).join("_")
  end
  
  # Sort so we deal with the shortest indexes first
  indexes.sort_by!(&:length)
  
  covered_indexes = indexes.map do |index|
    # Check to see if our index is covered by another index
    covering_index = indexes.difference([index])&.find{|x| x.start_with?(index)}
    if covering_index
      [index, covering_index]
    end
  end

  covered_indexes.compact!

  unless covered_indexes.empty?
    puts
    puts table.scan(table_name)
    covered_indexes.each do |index, covering_index|
      puts "Index on #{index} covered by #{covering_index}"
    end
  end
end