ActiveRecord includes over join Table very slow in Rails 4.1.2

Question

I just updated from Rails 4.0.2 to Rails 4.1.2 and realized ActiveRecord includes have become unusably slow. What used to take just a view milliseconds now take almost 5 minutes.

I join two tables Item and Keyword over a join table with has_and_belongs_to_many in the model. I have almost 3000 items, 3000 keywords and 8000 join table entries.

Getting all items and including all keywords used to be very fast but now takes forever:

Item.includes(:keywords)

I compared the SQL of both 4.0.2 and 4.1.2 and Rails seems to not use an inner join query in Rails 4.1.2 anymore. Database response time is very fast, so this is not the issue.

SQL for Rails 4.0.2

Item Load (5.8ms) SELECT items.* FROM items

SQL (4.6ms) SELECT keywords.*, t0.item_id AS ar_association_key_name FROM keywords INNER JOIN items_keywords t0 ON keywords.id = t0.keyword_id WHERE t0.item_id IN (<id1>, ...)

SQL for Rails 4.1.2

Item Load (3.7ms) SELECT items.* FROM items

HABTM_Keywords Load (2.8ms) SELECT items_keywords.* FROM items_keywords WHERE items_keywords.item_id IN (<id1>, ...)

Keyword Load (0.6ms) SELECT keywords.* FROM keywords WHERE keywords.id IN (<id1>, ...)

Is this a known issue? I can not find anything on this so I thought it's probably best to ask the community first before reporting a bug report.

For now I changed my Rails version back to 4.0.2.

Thanks Björn

Answer 1

This has been a bug in 4.1.2 and is solved here:

https://github.com/rails/rails/pull/15675

Answer 2

You can avoid the performance regression here by explicitly referencing the association:

Item.includes(:keywords).references(:keywords)

But the problem is Rails-wide, and while there's a fix in, it's not in any release yet so I've put it in an initializer for now.

For me this is still quite slow, but only about half as slow as without the fix.

module ActiveRecord
  # FIXME: this is a fix pulled from https://github.com/rails/rails/pull/15675
  # for a serious performance issue, look to remove it on the next Rails upgrade
  module Associations::Builder
    class HasAndBelongsToMany
      def hash
        object_id.hash
      end

      def ==(other)
        equal?(other)
      end
      alias :eql? :==
    end
  end
  module AttributeMethods
    module PrimaryKey
      def id
        return unless self.class.primary_key
        sync_with_transaction_state
        read_attribute(self.class.primary_key)
      end
    end
  end
end