Support Optimizer Hints

We as Arm Treasure Data are using Optimizer Hints with a monkey patch
(https://gist.github.com/kamipo/4c8539f0ce4acf85075cf5a6b0d9712e),
especially in order to use `MAX_EXECUTION_TIME` (refer #31129).

Example:

```ruby
class Job < ApplicationRecord
  default_scope { optimizer_hints("MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(jobs)") }
end
```

Optimizer Hints is supported not only for MySQL but also for most
databases (PostgreSQL on RDS, Oracle, SQL Server, etc), it is really
helpful to turn heavy queries for large scale applications.

20 files changed, 184 insertions, 6 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 3d7508252a..148b3800a8 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,28 @@
+*   Support Optimizer Hints.
+
+    In most databases, there is a way to control the optimizer is by using optimizer hints,
+    which can be specified within individual statements.
+
+    Example (for MySQL):
+
+        Topic.optimizer_hints("MAX_EXECUTION_TIME(50000)", "NO_INDEX_MERGE(topics)")
+        # SELECT /*+ MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(topics) */ `topics`.* FROM `topics`
+
+    Example (for PostgreSQL with pg_hint_plan):
+
+        Topic.optimizer_hints("SeqScan(topics)", "Parallel(topics 8)")
+        # SELECT /*+ SeqScan(topics) Parallel(topics 8) */ "topics".* FROM "topics"
+
+    See also:
+
+    * https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
+    * https://pghintplan.osdn.jp/pg_hint_plan.html
+    * https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/influencing-the-optimizer.html
+    * https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-2017
+    * https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.perf.doc/doc/c0070117.html
+
+    *Ryuta Kamizono*
+
 *   Fix query attribute method on user-defined attribute to be aware of typecasted value.
 
     For example, the following code no longer return false as casted non-empty string:
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
index 202187a047..7aad306d50 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb
@@ -384,6 +384,11 @@ module ActiveRecord
         false
       end
 
+      # Does this adapter support optimizer hints?
+      def supports_optimizer_hints?
+        false
+      end
+
       def supports_lazy_transactions?
         false
       end
diff --git a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
index a518b897a0..6eefda763f 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract_mysql_adapter.rb
@@ -103,6 +103,11 @@ module ActiveRecord
         mariadb? || version >= "5.7.5"
       end
 
+      # See https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html for more details.
+      def supports_optimizer_hints?
+        !mariadb? && version >= "5.7.7"
+      end
+
       def supports_advisory_locks?
         true
       end
diff --git a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
index 672f11cfcb..2905660499 100644
--- a/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb
@@ -351,6 +351,13 @@ module ActiveRecord
         postgresql_version >= 90400
       end
 
+      def supports_optimizer_hints?
+        unless defined?(@has_pg_hint_plan)
+          @has_pg_hint_plan = extension_available?("pg_hint_plan")
+        end
+        @has_pg_hint_plan
+      end
+
       def supports_lazy_transactions?
         true
       end
@@ -381,9 +388,12 @@ module ActiveRecord
         }
       end
 
+      def extension_available?(name)
+        query_value("SELECT true FROM pg_available_extensions WHERE name = #{quote(name)}", "SCHEMA")
+      end
+
       def extension_enabled?(name)
-        res = exec_query("SELECT EXISTS(SELECT * FROM pg_available_extensions WHERE name = '#{name}' AND installed_version IS NOT NULL) as enabled", "SCHEMA")
-        res.cast_values.first
+        query_value("SELECT installed_version IS NOT NULL FROM pg_available_extensions WHERE name = #{quote(name)}", "SCHEMA")
       end
 
       def extensions
diff --git a/activerecord/lib/active_record/querying.rb b/activerecord/lib/active_record/querying.rb
index 7a8d0cb663..86021f0a80 100644
--- a/activerecord/lib/active_record/querying.rb
+++ b/activerecord/lib/active_record/querying.rb
@@ -14,7 +14,7 @@ module ActiveRecord
       :find_each, :find_in_batches, :in_batches,
       :select, :reselect, :order, :reorder, :group, :limit, :offset, :joins, :left_joins, :left_outer_joins,
       :where, :rewhere, :preload, :eager_load, :includes, :from, :lock, :readonly, :extending, :or,
-      :having, :create_with, :distinct, :references, :none, :unscope, :merge, :except, :only,
+      :having, :create_with, :distinct, :references, :none, :unscope, :optimizer_hints, :merge, :except, :only,
       :count, :average, :minimum, :maximum, :sum, :calculate,
       :pluck, :pick, :ids
     ].freeze # :nodoc:
diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb
index ac37f73b76..37179774fa 100644
--- a/activerecord/lib/active_record/relation.rb
+++ b/activerecord/lib/active_record/relation.rb
@@ -5,7 +5,7 @@ module ActiveRecord
   class Relation
     MULTI_VALUE_METHODS  = [:includes, :eager_load, :preload, :select, :group,
                             :order, :joins, :left_outer_joins, :references,
-                            :extending, :unscope]
+                            :extending, :unscope, :optimizer_hints]
 
     SINGLE_VALUE_METHODS = [:limit, :offset, :lock, :readonly, :reordering,
                             :reverse_order, :distinct, :create_with, :skip_query_cache]
diff --git a/activerecord/lib/active_record/relation/query_methods.rb b/activerecord/lib/active_record/relation/query_methods.rb
index 0f2cff4c9c..2603fac8b2 100644
--- a/activerecord/lib/active_record/relation/query_methods.rb
+++ b/activerecord/lib/active_record/relation/query_methods.rb
@@ -901,6 +901,29 @@ module ActiveRecord
       self
     end
 
+    # Specify optimizer hints to be used in the SELECT statement.
+    #
+    # Example (for MySQL):
+    #
+    #   Topic.optimizer_hints("MAX_EXECUTION_TIME(50000)", "NO_INDEX_MERGE(topics)")
+    #   # SELECT /*+ MAX_EXECUTION_TIME(50000) NO_INDEX_MERGE(topics) */ `topics`.* FROM `topics`
+    #
+    # Example (for PostgreSQL with pg_hint_plan):
+    #
+    #   Topic.optimizer_hints("SeqScan(topics)", "Parallel(topics 8)")
+    #   # SELECT /*+ SeqScan(topics) Parallel(topics 8) */ "topics".* FROM "topics"
+    def optimizer_hints(*args)
+      check_if_method_has_arguments!(:optimizer_hints, args)
+      spawn.optimizer_hints!(*args)
+    end
+
+    def optimizer_hints!(*args) # :nodoc:
+      args.flatten!
+
+      self.optimizer_hints_values += args
+      self
+    end
+
     # Reverse the existing order clause on the relation.
     #
     #   User.order('name ASC').reverse_order # generated SQL has 'ORDER BY name DESC'
@@ -977,6 +1000,7 @@ module ActiveRecord
 
         build_select(arel)
 
+        arel.optimizer_hints(*optimizer_hints_values) unless optimizer_hints_values.empty?
         arel.distinct(distinct_value)
         arel.from(build_from) unless from_clause.empty?
         arel.lock(lock_value) if lock_value
diff --git a/activerecord/lib/arel/nodes/select_core.rb b/activerecord/lib/arel/nodes/select_core.rb
index 73461ff683..5814cae98a 100644
--- a/activerecord/lib/arel/nodes/select_core.rb
+++ b/activerecord/lib/arel/nodes/select_core.rb
@@ -4,7 +4,7 @@ module Arel # :nodoc: all
   module Nodes
     class SelectCore < Arel::Nodes::Node
       attr_accessor :projections, :wheres, :groups, :windows
-      attr_accessor :havings, :source, :set_quantifier
+      attr_accessor :havings, :source, :set_quantifier, :optimizer_hints
 
       def initialize
         super()
@@ -42,7 +42,7 @@ module Arel # :nodoc: all
 
       def hash
         [
-          @source, @set_quantifier, @projections,
+          @source, @set_quantifier, @projections, @optimizer_hints,
           @wheres, @groups, @havings, @windows
         ].hash
       end
@@ -51,6 +51,7 @@ module Arel # :nodoc: all
         self.class == other.class &&
           self.source == other.source &&
           self.set_quantifier == other.set_quantifier &&
+          self.optimizer_hints == other.optimizer_hints &&
           self.projections == other.projections &&
           self.wheres == other.wheres &&
           self.groups == other.groups &&
diff --git a/activerecord/lib/arel/nodes/unary.rb b/activerecord/lib/arel/nodes/unary.rb
index 00639304e4..6d1ac36b0e 100644
--- a/activerecord/lib/arel/nodes/unary.rb
+++ b/activerecord/lib/arel/nodes/unary.rb
@@ -35,6 +35,7 @@ module Arel # :nodoc: all
       Not
       Offset
       On
+      OptimizerHints
       Ordering
       RollUp
     }.each do |name|
diff --git a/activerecord/lib/arel/select_manager.rb b/activerecord/lib/arel/select_manager.rb
index 0da52ca91b..32286b67f4 100644
--- a/activerecord/lib/arel/select_manager.rb
+++ b/activerecord/lib/arel/select_manager.rb
@@ -146,6 +146,13 @@ module Arel # :nodoc: all
       @ctx.projections = projections
     end
 
+    def optimizer_hints(*hints)
+      unless hints.empty?
+        @ctx.optimizer_hints = Arel::Nodes::OptimizerHints.new(hints)
+      end
+      self
+    end
+
     def distinct(value = true)
       if value
         @ctx.set_quantifier = Arel::Nodes::Distinct.new
diff --git a/activerecord/lib/arel/visitors/depth_first.rb b/activerecord/lib/arel/visitors/depth_first.rb
index 92d309453c..892d670c79 100644
--- a/activerecord/lib/arel/visitors/depth_first.rb
+++ b/activerecord/lib/arel/visitors/depth_first.rb
@@ -35,6 +35,7 @@ module Arel # :nodoc: all
         alias :visit_Arel_Nodes_Ascending         :unary
         alias :visit_Arel_Nodes_Descending        :unary
         alias :visit_Arel_Nodes_UnqualifiedColumn :unary
+        alias :visit_Arel_Nodes_OptimizerHints    :unary
 
         def function(o)
           visit o.expressions
diff --git a/activerecord/lib/arel/visitors/dot.rb b/activerecord/lib/arel/visitors/dot.rb
index 6389c875cb..ffcbb7a7ab 100644
--- a/activerecord/lib/arel/visitors/dot.rb
+++ b/activerecord/lib/arel/visitors/dot.rb
@@ -82,6 +82,7 @@ module Arel # :nodoc: all
         alias :visit_Arel_Nodes_Offset            :unary
         alias :visit_Arel_Nodes_On                :unary
         alias :visit_Arel_Nodes_UnqualifiedColumn :unary
+        alias :visit_Arel_Nodes_OptimizerHints    :unary
         alias :visit_Arel_Nodes_Preceding         :unary
         alias :visit_Arel_Nodes_Following         :unary
         alias :visit_Arel_Nodes_Rows              :unary
diff --git a/activerecord/lib/arel/visitors/ibm_db.rb b/activerecord/lib/arel/visitors/ibm_db.rb
index 73166054da..0ffc0725f7 100644
--- a/activerecord/lib/arel/visitors/ibm_db.rb
+++ b/activerecord/lib/arel/visitors/ibm_db.rb
@@ -4,6 +4,14 @@ module Arel # :nodoc: all
   module Visitors
     class IBM_DB < Arel::Visitors::ToSql
       private
+        def visit_Arel_Nodes_SelectCore(o, collector)
+          collector = super
+          maybe_visit o.optimizer_hints, collector
+        end
+
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/* <OPTGUIDELINES>#{sanitize_as_sql_comment(o).join}</OPTGUIDELINES> */"
+        end
 
         def visit_Arel_Nodes_Limit(o, collector)
           collector << "FETCH FIRST "
@@ -16,6 +24,10 @@ module Arel # :nodoc: all
           collector = visit [o.left, o.right, 0, 1], collector
           collector << ")"
         end
+
+        def collect_optimizer_hints(o, collector)
+          collector
+        end
     end
   end
 end
diff --git a/activerecord/lib/arel/visitors/informix.rb b/activerecord/lib/arel/visitors/informix.rb
index 208fa15aef..cd43be8858 100644
--- a/activerecord/lib/arel/visitors/informix.rb
+++ b/activerecord/lib/arel/visitors/informix.rb
@@ -42,10 +42,15 @@ module Arel # :nodoc: all
           collector
         end
 
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/*+ #{sanitize_as_sql_comment(o).join(", ")} */"
+        end
+
         def visit_Arel_Nodes_Offset(o, collector)
           collector << "SKIP "
           visit o.expr, collector
         end
+
         def visit_Arel_Nodes_Limit(o, collector)
           collector << "FIRST "
           visit o.expr, collector
diff --git a/activerecord/lib/arel/visitors/mssql.rb b/activerecord/lib/arel/visitors/mssql.rb
index fdd864b40d..85815baca2 100644
--- a/activerecord/lib/arel/visitors/mssql.rb
+++ b/activerecord/lib/arel/visitors/mssql.rb
@@ -76,6 +76,15 @@ module Arel # :nodoc: all
           end
         end
 
+        def visit_Arel_Nodes_SelectCore(o, collector)
+          collector = super
+          maybe_visit o.optimizer_hints, collector
+        end
+
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "OPTION (#{sanitize_as_sql_comment(o).join(", ")})"
+        end
+
         def get_offset_limit_clause(o)
           first_row = o.offset ? o.offset.expr.to_i + 1 : 1
           last_row  = o.limit ? o.limit.expr.to_i - 1 + first_row : nil
@@ -103,6 +112,10 @@ module Arel # :nodoc: all
           end
         end
 
+        def collect_optimizer_hints(o, collector)
+          collector
+        end
+
         def determine_order_by(orders, x)
           if orders.any?
             orders
diff --git a/activerecord/lib/arel/visitors/to_sql.rb b/activerecord/lib/arel/visitors/to_sql.rb
index d0dec63860..7e3e265208 100644
--- a/activerecord/lib/arel/visitors/to_sql.rb
+++ b/activerecord/lib/arel/visitors/to_sql.rb
@@ -219,6 +219,7 @@ module Arel # :nodoc: all
         def visit_Arel_Nodes_SelectCore(o, collector)
           collector << "SELECT"
 
+          collector = collect_optimizer_hints(o, collector)
           collector = maybe_visit o.set_quantifier, collector
 
           collect_nodes_for o.projections, collector, SPACE
@@ -236,6 +237,10 @@ module Arel # :nodoc: all
           collector
         end
 
+        def visit_Arel_Nodes_OptimizerHints(o, collector)
+          collector << "/*+ #{sanitize_as_sql_comment(o).join(" ")} */"
+        end
+
         def collect_nodes_for(nodes, collector, spacer, connector = COMMA)
           unless nodes.empty?
             collector << spacer
@@ -799,6 +804,14 @@ module Arel # :nodoc: all
           @connection.quote_column_name(name)
         end
 
+        def sanitize_as_sql_comment(o)
+          o.expr.map { |v| v.gsub(%r{ /\*\+?\s* | \s*\*/ }x, "") }
+        end
+
+        def collect_optimizer_hints(o, collector)
+          maybe_visit o.optimizer_hints, collector
+        end
+
         def maybe_visit(thing, collector)
           return collector unless thing
           collector << " "
diff --git a/activerecord/test/cases/adapters/mysql2/optimizer_hints_test.rb b/activerecord/test/cases/adapters/mysql2/optimizer_hints_test.rb
new file mode 100644
index 0000000000..349de49b36
--- /dev/null
+++ b/activerecord/test/cases/adapters/mysql2/optimizer_hints_test.rb
@@ -0,0 +1,24 @@
+# frozen_string_literal: true
+
+require "cases/helper"
+require "models/post"
+
+if supports_optimizer_hints?
+  class Mysql2OptimzerHintsTest < ActiveRecord::Mysql2TestCase
+    fixtures :posts
+
+    def test_optimizer_hints
+      assert_sql(%r{\ASELECT /\*\+ NO_RANGE_OPTIMIZATION\(posts index_posts_on_author_id\) \*/}) do
+        posts = Post.optimizer_hints("NO_RANGE_OPTIMIZATION(posts index_posts_on_author_id)")
+        posts = posts.select(:id).where(author_id: [0, 1])
+        assert_includes posts.explain, "| index | index_posts_on_author_id | index_posts_on_author_id |"
+      end
+
+      assert_sql(%r{\ASELECT /\*\+ NO_RANGE_OPTIMIZATION\(posts index_posts_on_author_id\) \*/}) do
+        posts = Post.optimizer_hints("/*+ NO_RANGE_OPTIMIZATION(posts index_posts_on_author_id) */")
+        posts = posts.select(:id).where(author_id: [0, 1])
+        assert_includes posts.explain, "| index | index_posts_on_author_id | index_posts_on_author_id |"
+      end
+    end
+  end
+end
diff --git a/activerecord/test/cases/adapters/postgresql/optimizer_hints_test.rb b/activerecord/test/cases/adapters/postgresql/optimizer_hints_test.rb
new file mode 100644
index 0000000000..1bfa815cac
--- /dev/null
+++ b/activerecord/test/cases/adapters/postgresql/optimizer_hints_test.rb
@@ -0,0 +1,28 @@
+# frozen_string_literal: true
+
+require "cases/helper"
+require "models/post"
+
+if supports_optimizer_hints?
+  class PostgresqlOptimzerHintsTest < ActiveRecord::PostgreSQLTestCase
+    fixtures :posts
+
+    def setup
+      enable_extension!("pg_hint_plan", ActiveRecord::Base.connection)
+    end
+
+    def test_optimizer_hints
+      assert_sql(%r{\ASELECT /\*\+ SeqScan\(posts\) \*/}) do
+        posts = Post.optimizer_hints("SeqScan(posts)")
+        posts = posts.select(:id).where(author_id: [0, 1])
+        assert_includes posts.explain, "Seq Scan on posts"
+      end
+
+      assert_sql(%r{\ASELECT /\*\+ SeqScan\(posts\) \*/}) do
+        posts = Post.optimizer_hints("/*+ SeqScan(posts) */")
+        posts = posts.select(:id).where(author_id: [0, 1])
+        assert_includes posts.explain, "Seq Scan on posts"
+      end
+    end
+  end
+end
diff --git a/activerecord/test/cases/helper.rb b/activerecord/test/cases/helper.rb
index a9ec667eba..f95d082907 100644
--- a/activerecord/test/cases/helper.rb
+++ b/activerecord/test/cases/helper.rb
@@ -64,6 +64,7 @@ end
   supports_insert_on_duplicate_skip?
   supports_insert_on_duplicate_update?
   supports_insert_conflict_target?
+  supports_optimizer_hints?
 ].each do |method_name|
   define_method method_name do
     ActiveRecord::Base.connection.public_send(method_name)
diff --git a/activerecord/test/cases/invertible_migration_test.rb b/activerecord/test/cases/invertible_migration_test.rb
index 6cf17ac15d..d68cc40107 100644
--- a/activerecord/test/cases/invertible_migration_test.rb
+++ b/activerecord/test/cases/invertible_migration_test.rb
@@ -308,6 +308,8 @@ module ActiveRecord
         migration2 = DisableExtension1.new
         migration3 = DisableExtension2.new
 
+        assert_equal true, Horse.connection.extension_available?("hstore")
+
         migration1.migrate(:up)
         migration2.migrate(:up)
         assert_equal true, Horse.connection.extension_enabled?("hstore")