indexer.rb in pgdexter-0.5.2

- old
+ new

@@ -183,11 +183,11 @@
     def create_hypothetical_indexes(queries)
       candidates = {}
 
       # get initial costs for queries
       calculate_plan(queries)
-      explainable_queries = queries.select { |q| q.explainable? && q.high_cost? }
+      explainable_queries = queries.select { |q| q.plans.any? && q.high_cost? }
 
       # filter tables for performance
       tables = Set.new(explainable_queries.flat_map(&:tables))
       tables_from_views = Set.new(explainable_queries.flat_map(&:tables_from_views))
 
@@ -519,33 +519,39 @@
       end
     rescue PG::ConnectionBad => e
       raise Dexter::Abort, e.message
     end
 
-    def execute(query, pretty: true, params: [])
-      # use exec_params instead of exec for security
+    def execute(query, pretty: true, params: [], use_exec: false)
+      # use exec_params instead of exec when possible 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) if pretty
       log colorize("[sql] #{query}#{params.any? ? " /*#{params.to_json}*/" : ""}", :cyan) if @log_sql
 
       @mutex.synchronize do
-        conn.exec_params("#{query} /*dexter*/", params).to_a
+        if use_exec
+          conn.exec("#{query} /*dexter*/").to_a
+        else
+          conn.exec_params("#{query} /*dexter*/", params).to_a
+        end
       end
     end
 
     def plan(query)
       prepared = false
       transaction = false
 
       # try to EXPLAIN normalized queries
       # https://dev.to/yugabyte/explain-from-pgstatstatements-normalized-queries-how-to-always-get-the-generic-plan-in--5cfi
-      explain_normalized = query.include?("$1")
+      normalized = query.include?("$1")
+      generic_plan = normalized && server_version_num >= 160000
+      explain_normalized = normalized && !generic_plan
       if explain_normalized
         prepared_name = "dexter_prepared"
         execute("PREPARE #{prepared_name} AS #{safe_statement(query)}", pretty: false)
         prepared = true
         params = execute("SELECT array_length(parameter_types, 1) AS params FROM pg_prepared_statements WHERE name = $1", params: [prepared_name]).first["params"].to_i
@@ -564,15 +570,17 @@
           execute("ROLLBACK")
           execute("BEGIN")
         end
       end
 
+      explain_prefix = generic_plan ? "GENERIC_PLAN, " : ""
+
       # strip semi-colons as another measure of defense
-      plan = JSON.parse(execute("EXPLAIN (FORMAT JSON) #{safe_statement(query)}", pretty: false).first["QUERY PLAN"], max_nesting: 1000).first["Plan"]
+      plan = JSON.parse(execute("EXPLAIN (#{explain_prefix}FORMAT JSON) #{safe_statement(query)}", pretty: false, use_exec: generic_plan).first["QUERY PLAN"], max_nesting: 1000).first["Plan"]
 
       if @log_explain
         # Pass format to prevent ANALYZE
-        puts execute("EXPLAIN (FORMAT TEXT) #{safe_statement(query)}", pretty: false).map { |r| r["QUERY PLAN"] }.join("\n")
+        puts execute("EXPLAIN (#{explain_prefix}FORMAT TEXT) #{safe_statement(query)}", pretty: false, use_exec: generic_plan).map { |r| r["QUERY PLAN"] }.join("\n")
       end
 
       plan
     ensure
       if explain_normalized