用于单个sql查询的rails-5 activerecord postgresql-9.6 enable_nestloop

Question

对于activerecord中的单个查询，我尝试使用原始SQL打开并关闭postgresql enable_nestloop。这是命令

 class Segment < ApplicationRecord
   def self.count_payload_kind

     s = "

       SET LOCAL enable_nestloop = off;

       SELECT count(*)
       FROM   segments s
       WHERE  s.payload @> '[{\"kind\":\"person\"}]';

       SET LOCAL enable_nestloop = on;
     "

     ActiveRecord::Base.connection.execute(s).to_a

   end
 end

当我调用ActiveRecord::Base.connection.execute(s).to_a，时，它不会返回任何记录，也不会抛出错误。但是，如果删除对设置本地enable_nestloop的2次调用，则它将正确工作。

如何为一个查询打开和关闭enable_nestloop。

Answer 1

通过在Rails 4.2.6和postgresql 9.5.4上的测试，将调用包装在事务中可能有效。

result = ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute("SET LOCAL enable_nestloop = off;")
  r = ActiveRecord::Base.connection.execute("
    SELECT count(*)
      FROM projects
     WHERE active;
  ")
  ActiveRecord::Base.connection.execute("SET LOCAL enable_nestloop = on;")
  r
end

result.values.flatten
=> ["1"]

SQL输出

(4.3ms)  BEGIN
(6.8ms)  SET LOCAL enable_nestloop = off;
(98.3ms)
  SELECT count(*)
    FROM projects
   WHERE active;

(3.1ms)  SET LOCAL enable_nestloop = on;
(3.0ms)  COMMIT