From 97347d8c409f14b682dd9ec52ded3c869d0ba479 Mon Sep 17 00:00:00 2001
From: Ryuta Kamizono <kamipo@gmail.com>
Date: Wed, 13 Mar 2019 09:29:00 +0900
Subject: 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.
---
 .../cases/adapters/mysql2/optimizer_hints_test.rb  | 24 +++++++++++++++++++
 .../adapters/postgresql/optimizer_hints_test.rb    | 28 ++++++++++++++++++++++
 activerecord/test/cases/helper.rb                  |  1 +
 .../test/cases/invertible_migration_test.rb        |  2 ++
 4 files changed, 55 insertions(+)
 create mode 100644 activerecord/test/cases/adapters/mysql2/optimizer_hints_test.rb
 create mode 100644 activerecord/test/cases/adapters/postgresql/optimizer_hints_test.rb

(limited to 'activerecord/test/cases')

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")
-- 
cgit v1.2.3