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 ++++++++++++++++++++++
 2 files changed, 52 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/adapters')

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