Module: PgHero::Methods::SuggestedIndexes

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

Instance Method Summary collapse

Instance Method Details

#autoindex(create: false) ⇒ Object 



78
79
80
81
82
83
84
85
86
87
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 78

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

#best_index(statement) ⇒ Object 



89
90
91
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 89

def best_index(statement)
  best_index_helper([statement])[statement]
end

#suggested_indexes(suggested_indexes_by_query: nil, **options) ⇒ Object 



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

def suggested_indexes(suggested_indexes_by_query: nil, **options)
  indexes = []

  (suggested_indexes_by_query || self.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(queries: nil, query_stats: nil, indexes: nil) ⇒ 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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
# File 'lib/pghero/methods/suggested_indexes.rb', line 9

def suggested_indexes_by_query(queries: nil, query_stats: nil, indexes: nil)
  best_indexes = {}

  if suggested_indexes_enabled?
    # get most time-consuming queries
    queries ||= (query_stats || self.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

          # indexes of same type
          indexes = existing_columns[index[:using] || "btree"][index[:table]]

          if best_index[:structure][:sort].empty?
            # gist indexes without an opclass
            # (opclass is part of column name, so columns won't match if opclass present)
            indexes += existing_columns["gist"][index[:table]]

            # hash indexes work for equality
            indexes += existing_columns["hash"][index[:table]] if best_index[:structure][:where].all? { |v| v[:op] == "=" }

            # brin indexes work for all
            indexes += existing_columns["brin"][index[:table]]
          end

          covering_index = indexes.find { |e| index_covers?(e.map { |v| v.delete_suffix(" inet_ops") }, 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
  else
    raise NotEnabled, "Suggested indexes not enabled"
  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) && Gem::Version.new(PgQuery::VERSION) >= Gem::Version.new("2") && query_stats_enabled?
end