Module: PgHero::Methods::SuggestedIndexes

Included in:
Database
Defined in:
lib/pghero/methods/suggested_indexes.rb

Instance Method Summary collapse

Instance Method Details

#autoindex(options = {}) ⇒ Object 



60
61
62
63
64
65
66
67
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 60

def autoindex(options = {})
  suggested_indexes.each do |index|
    p index
    if options[:create]
      connection.execute("CREATE INDEX CONCURRENTLY ON #{quote_table_name(index[:table])} (#{index[:columns].map { |c| quote_table_name(c) }.join(",")})")
    end
  end
end

#autoindex_all(options = {}) ⇒ Object 



69
70
71
72
73
74
75
76
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 69

def autoindex_all(options = {})
  config["databases"].keys.each do |database|
    with(database) do
      puts "Autoindexing #{database}..."
      autoindex(options)
    end
  end
end

#best_index(statement, _options = {}) ⇒ Object 



78
79
80
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 78

def best_index(statement, _options = {})
  best_index_helper([statement])[statement]
end

#suggested_indexes(options = {}) ⇒ Object 



46
47
48
49
50
51
52
53
54
55
56
57
58
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 46

def suggested_indexes(options = {})
  indexes = []

  (options[:suggested_indexes_by_query] || suggested_indexes_by_query(options)).select { |_s, i| i[:found] && !i[:covering_index] }.group_by { |_s, i| i[:index] }.each do |index, group|
    details = {}
    group.map(&:second).each do |g|
      details = details.except(:index).deep_merge(g)
    end
    indexes << index.merge(queries: group.map(&:first), details: details)
  end

  indexes.sort_by { |i| [i[:table], i[:columns]] }
end

#suggested_indexes_by_query(options = {}) ⇒ Object

TODO clean this mess



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 9

def suggested_indexes_by_query(options = {})
  best_indexes = {}

  if suggested_indexes_enabled?
    # get most time-consuming queries
    queries = options[:queries] || (options[:query_stats] || query_stats(historical: true, start_at: 24.hours.ago)).map { |qs| qs["query"] }

    # get best indexes for queries
    best_indexes = best_index_helper(queries)

    if best_indexes.any?
      existing_columns = Hash.new { |hash, key| hash[key] = Hash.new { |hash2, key2| hash2[key2] = [] } }
      indexes = self.indexes
      indexes.group_by { |g| g["using"] }.each do |group, inds|
        inds.each do |i|
          existing_columns[group][i["table"]] << i["columns"]
        end
      end
      indexes_by_table = indexes.group_by { |i| i["table"] }

      best_indexes.each do |_query, best_index|
        if best_index[:found]
          index = best_index[:index]
          best_index[:table_indexes] = indexes_by_table[index[:table]].to_a
          covering_index = existing_columns[index[:using] || "btree"][index[:table]].find { |e| index_covers?(e, index[:columns]) }
          if covering_index
            best_index[:covering_index] = covering_index
            best_index[:explanation] = "Covered by index on (#{covering_index.join(", ")})"
          end
        end
      end
    end
  end

  best_indexes
end

#suggested_indexes_enabled? ⇒ Boolean

Returns:

  • (Boolean) 


4
5
6
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 4

def suggested_indexes_enabled?
  defined?(PgQuery) && query_stats_enabled?
end