Class: SqlGenius::Core::Analysis::DuplicateIndexes

Inherits:
Object
  • Object
show all
Defined in:
lib/sql_genius/core/analysis/duplicate_indexes.rb

Overview

Detects indexes whose columns are a left-prefix of another index on the same table (meaning the shorter index is redundant — the longer one can satisfy the same queries). Preserves unique indexes: a unique index is never flagged as redundant when only covered by a non-unique index.

Takes a Core::Connection plus a list of tables to exclude from the scan. Returns an array of hashes describing each duplicate pair, with the (duplicate_index, covered_by_index) pair deduplicated across symmetrical relationships.

Instance Method Summary collapse

Constructor Details

#initialize(connection, blocked_tables:) ⇒ DuplicateIndexes

Returns a new instance of DuplicateIndexes.



19
20
21
22
23
 
# File 'lib/sql_genius/core/analysis/duplicate_indexes.rb', line 19

def initialize(connection, blocked_tables:)
  @connection = connection
  @blocked_tables = blocked_tables
  @builder = QueryBuilders.for(connection)
end

Instance Method Details

#call ⇒ Object 



25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# File 'lib/sql_genius/core/analysis/duplicate_indexes.rb', line 25

def call
  duplicates = []

  queryable_tables.each do |table|
    indexes = @connection.indexes_for(table)
    next if indexes.size < 2

    indexes.each do |idx|
      indexes.each do |other|
        next if idx.name == other.name
        next unless covers?(other, idx)

        duplicates << {
          table: table,
          duplicate_index: idx.name,
          duplicate_columns: idx.columns,
          covered_by_index: other.name,
          covered_by_columns: other.columns,
          unique: idx.unique,
          drop_sql: @builder.drop_index_sql(table: table, index_name: idx.name),
        }
      end
    end
  end

  deduplicate(duplicates)
end