indexer.rb in pgdexter-0.3.5

- old
+ new

@@ -12,10 +12,11 @@
       @min_time = options[:min_time] || 0
       @min_calls = options[:min_calls] || 0
       @analyze = options[:analyze]
       @min_cost_savings_pct = options[:min_cost_savings_pct].to_i
       @options = options
+      @mutex = Mutex.new
 
       create_extension unless extension_exists?
       execute("SET lock_timeout = '5s'")
     end
 
@@ -179,11 +180,11 @@
         end
         begin
           query.plans << plan(query.statement)
           if @log_explain
             # Pass format to prevent ANALYZE
-            puts execute("EXPLAIN (FORMAT TEXT) #{safe_statement(query.statement)}").map { |r| r["QUERY PLAN"] }.join("\n")
+            puts execute("EXPLAIN (FORMAT TEXT) #{safe_statement(query.statement)}", pretty: false).map { |r| r["QUERY PLAN"] }.join("\n")
           end
         rescue PG::Error, JSON::NestingError => e
           if @log_explain
             log e.message
           end
@@ -360,15 +361,16 @@
                 new_cost > 100 && winning_cost < new_cost * savings_ratio
               else
                 winning_cost < query.initial_cost * savings_ratio
               end
 
+            query_indexes = [winning_index]
+            new_cost3 = winning_cost
+            query.pass3_indexes = query_indexes
+
             if use_winning
-              query_indexes = [winning_index]
               cost_savings3 = true
-              new_cost3 = winning_cost
-              query.pass3_indexes = query_indexes
             else
               suggest_index = false
             end
           end
 
@@ -454,11 +456,11 @@
             log "Pass2: #{query.costs[2]} : #{log_indexes(query.pass2_indexes || [])}"
             if query.costs[3]
               log "Pass3: #{query.costs[3]} : #{log_indexes(query.pass3_indexes || [])}"
             end
             log "Final: #{query.new_cost} : #{log_indexes(query.suggest_index ? query_indexes : [])}"
-            if query_indexes.size == 1 && !query.suggest_index
+            if (query.pass1_indexes.any? || query.pass2_indexes.any?) && !query.suggest_index
               log "Need #{@min_cost_savings_pct}% cost savings to suggest index"
             end
           else
             log "Could not run explain"
           end
@@ -514,25 +516,28 @@
       end
     rescue PG::ConnectionBad => e
       abort e.message
     end
 
-    def execute(query)
+    def execute(query, pretty: true)
       # use exec_params instead of exec for security
       #
       # Unlike PQexec, PQexecParams allows at most one SQL command in the given string.
       # (There can be semicolons in it, but not more than one nonempty command.)
       # This is a limitation of the underlying protocol, but has some usefulness
       # as an extra defense against SQL-injection attacks.
       # https://www.postgresql.org/docs/current/static/libpq-exec.html
-      query = squish(query)
+      query = squish(query) if pretty
       log "SQL: #{query}" if @log_sql
-      conn.exec_params(query, []).to_a
+
+      @mutex.synchronize do
+        conn.exec_params(query, []).to_a
+      end
     end
 
     def plan(query)
       # strip semi-colons as another measure of defense
-      JSON.parse(execute("EXPLAIN (FORMAT JSON) #{safe_statement(query)}").first["QUERY PLAN"], max_nesting: 1000).first["Plan"]
+      JSON.parse(execute("EXPLAIN (FORMAT JSON) #{safe_statement(query)}", pretty: false).first["QUERY PLAN"], max_nesting: 1000).first["Plan"]
     end
 
     # TODO for multicolumn indexes, use ordering
     def create_hypothetical_indexes_helper(columns_by_table, n, candidates)
       columns_by_table.each do |table, cols|