suggested_indexes.rb in pghero-2.7.3

- old
+ new

@@ -28,11 +28,27 @@
 
             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]) }
+
+                # 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.sub(/ inet_ops\z/, "") }, index[:columns]) }
                 if covering_index
                   best_index[:covering_index] = covering_index
                   best_index[:explanation] = "Covered by index on (#{covering_index.join(", ")})"
                 end
               end
@@ -84,10 +100,10 @@
         end
 
         # get stats about columns for relevant tables
         tables = parts.values.map { |t| t[:table] }.uniq
         # TODO get schema from query structure, then try search path
-        schema = connection_model.connection_config[:schema] || "public"
+        schema = PgHero.connection_config(connection_model)[:schema] || "public"
         if tables.any?
           row_stats = Hash[table_stats(table: tables, schema: schema).map { |i| [i[:table], i[:estimated_rows]] }]
           col_stats = column_stats(table: tables, schema: schema).group_by { |i| i[:table] }
         end
 