Class: SqlGenius::Core::Analysis::UnusedIndexes

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

Overview

Indexes whose scan count is at or below ‘min_scans` (default 0 — never scanned since the underlying stats source was last reset). On MySQL this reads performance_schema.table_io_waits_summary_by_index_usage; on PostgreSQL it reads pg_stat_user_indexes plus pg_relation_size for the index byte size.

Returns a Result with:

indexes        — Array of per-index hashes (sorted by size DESC on PG,
                 by write count DESC on MySQL); each carries a dialect-
                 appropriate `drop_sql` and a `size_bytes` value (nil
                 on MySQL where individual index sizes aren't cheap).
stats_reset_at — Time the underlying stats source was last reset
                 (PG only — pg_stat_database.stats_reset; nil on MySQL).
min_scans      — The scan threshold used for this call, echoed back so
                 callers can display "indexes with ≤ N scans".

Skips primary key indexes on both dialects, plus unique indexes (which are usually backing a constraint the application depends on). Raises if the underlying stats source is unavailable.

Defined Under Namespace

Classes: Result

Instance Method Summary collapse

Constructor Details

#initialize(connection, min_scans: 0) ⇒ UnusedIndexes

Returns a new instance of UnusedIndexes.



28
29
30
31
32
 
# File 'lib/sql_genius/core/analysis/unused_indexes.rb', line 28

def initialize(connection, min_scans: 0)
  @connection = connection
  @builder = QueryBuilders.for(connection)
  @min_scans = [min_scans.to_i, 0].max
end

Instance Method Details

#call ⇒ Object 



34
35
36
37
38
39
40
41
 
# File 'lib/sql_genius/core/analysis/unused_indexes.rb', line 34

def call
  rows = @connection.exec_query(@builder.unused_indexes(@connection, min_scans: @min_scans)).to_hashes
  Result.new(
    indexes: rows.map { |row| transform(row) },
    stats_reset_at: @builder.stats_reset_at(@connection),
    min_scans: @min_scans,
  )
end