lib/dexter/indexer.rb in pgdexter-0.3.1 vs lib/dexter/indexer.rb in pgdexter-0.3.2

@@ -28,25 +28,15 @@
       # reset hypothetical indexes
       reset_hypothetical_indexes
 
       tables = Set.new(database_tables)
 
-      if @include_tables
-        include_set = Set.new(@include_tables)
-        tables.keep_if { |t| include_set.include?(t) || include_set.include?(t.split(".")[-1]) }
-      end
-
-      if @exclude_tables.any?
-        exclude_set = Set.new(@exclude_tables)
-        tables.delete_if { |t| exclude_set.include?(t) || exclude_set.include?(t.split(".")[-1]) }
-      end
-
       # map tables without schema to schema
       no_schema_tables = {}
       search_path_index = Hash[search_path.map.with_index.to_a]
       tables.group_by { |t| t.split(".")[-1] }.each do |group, t2|
-        no_schema_tables[group] = t2.sort_by { |t| search_path_index[t.split(".")[0]] || 1000000 }[0]
+        no_schema_tables[group] = t2.sort_by { |t| [search_path_index[t.split(".")[0]] || 1000000, t] }[0]
       end
 
       # filter queries from other databases and system tables
       queries.each do |query|
         # add schema to table if needed
@@ -57,15 +47,33 @@
       end
 
       # set tables
       tables = Set.new(queries.reject(&:missing_tables).flat_map(&:tables))
 
+      # must come after missing tables set
+      if @include_tables
+        include_set = Set.new(@include_tables)
+        tables.keep_if { |t| include_set.include?(t) || include_set.include?(t.split(".")[-1]) }
+      end
+
+      if @exclude_tables.any?
+        exclude_set = Set.new(@exclude_tables)
+        tables.delete_if { |t| exclude_set.include?(t) || exclude_set.include?(t.split(".")[-1]) }
+      end
+
+      # remove system tables
+      tables.delete_if { |t| t.start_with?("information_schema.") || t.start_with?("pg_catalog.") }
+
+      queries.each do |query|
+        query.candidate_tables = !query.missing_tables && query.tables.any? { |t| tables.include?(t) }
+      end
+
       # analyze tables if needed
       analyze_tables(tables) if tables.any? && (@analyze || @log_level == "debug2")
 
       # create hypothetical indexes and explain queries
-      candidates = tables.any? ? create_hypothetical_indexes(queries.reject(&:missing_tables), tables) : {}
+      candidates = tables.any? ? create_hypothetical_indexes(queries.select(&:candidate_tables), tables) : {}
 
       # see if new indexes were used and meet bar
       new_indexes = determine_indexes(queries, candidates, tables)
 
       # display and create new indexes
@@ -342,29 +350,30 @@
         end
         index_queries.each do |query|
           log "-" * 80
           log "Query #{query.fingerprint}"
           log "Total time: #{(query.total_time / 60000.0).round(1)} min, avg time: #{(query.total_time / query.calls.to_f).round} ms, calls: #{query.calls}" if query.total_time
-          if tables.empty?
+
+          if query.fingerprint == "unknown"
+            log "Could not parse query"
+          elsif query.tables.empty?
+            log "No tables"
+          elsif query.missing_tables
+            log "Tables not present in current database"
+          elsif !query.candidate_tables
             log "No candidate tables for indexes"
           elsif query.explainable? && !query.high_cost?
             log "Low initial cost: #{query.initial_cost}"
           elsif query.explainable?
             query_indexes = query.indexes || []
             log "Start: #{query.costs[0]}"
             log "Pass1: #{query.costs[1]} : #{log_indexes(query.pass1_indexes || [])}"
             log "Pass2: #{query.costs[2]} : #{log_indexes(query.pass2_indexes || [])}"
             log "Final: #{query.new_cost} : #{log_indexes(query.suggest_index ? query_indexes : [])}"
-            if query_indexes.any? && !query.suggest_index
+            if query_indexes.size == 1 && !query.suggest_index
               log "Need 50% cost savings to suggest index"
             end
-          elsif query.fingerprint == "unknown"
-            log "Could not parse query"
-          elsif query.tables.empty?
-            log "No tables"
-          elsif query.missing_tables
-            log "Tables not present in current database"
           else
             log "Could not run explain"
           end
           log
           log query.statement
@@ -450,13 +459,11 @@
         SELECT
           table_schema || '.' || table_name AS table_name
         FROM
           information_schema.tables
         WHERE
-          table_catalog = current_database() AND
-          table_schema NOT IN ('pg_catalog', 'information_schema')
-          AND table_type = 'BASE TABLE'
+          table_catalog = current_database()
       SQL
       result.map { |r| r["table_name"] }
     end
 
     def stat_statements
@@ -547,10 +554,10 @@
       SQL
       ).map { |v| v["columns"] = v["columns"].sub(") WHERE (", " WHERE ").split(", ").map { |c| unquote(c) }; v }
     end
 
     def search_path
-      execute("SHOW search_path")[0]["search_path"].split(",").map(&:strip)
+      execute("SELECT current_schemas(true)")[0]["current_schemas"][1..-2].split(",")
     end
 
     def unquote(part)
       if part && part.start_with?('"') && part.end_with?('"')
         part[1..-2]