When limit(1).first is orders of magnitude slower than limit(1)[0] in Rails.
We just got very burnt by this issue.
-
Foobar.where(x: 'y').first= 1 minute -
Foobar.where(x: 'y').limit(1).first= 1 minute -
Foobar.where(x: 'y').limit(1)[0]= 0.2ms -
Foobar.find_by(x: 'y')= 0.2ms -
Foobar.find_by_x('y')= 0.2ms
This is in Postgres. Column x has an index on it. Orders of magnitudes of difference. What's going on?
It turns out that .first adds an ORDER BY id to the SQL query, presumably to make it idempotent. However, find_by and [0] don't. Postgres uses it's index without the ORDER BY but chooses not to when it is present.
Not a bug in Rails really (although I wonder if .first and [0] should be equivalent). However, it is a massive GOTCHA.
Hope this saves someone some time!
Written by Jeremy Walker
Related protips
3 Responses
I prefer the current behavior where .first is not longer the same as .randompickone (strangely enough, .last always had the order by id built in, .first only since Rails 4, http://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-first)
For the "give me one, I don't care which one", that would be .take or .take(1)
http://api.rubyonrails.org/classes/ActiveRecord/FinderMethods.html#method-i-take
@peter_v - Do you not thing .first and [0] should be equivalent?
I have no real opinion on [0] ... (I presume it first reifies the relationship (without the order by id) and then applies the [0] to the first and single one of that reified list; that amounts to pick_a_random_one).
So, all these amounts to the fast pick_a_random_one
- Foobar.where(x: 'y').limit(1).to_a[0]
- Foobar.where(x: 'y').limit(1).to_set[0]
- Foobar.where(x: 'y').limit(1).to_a.first
What matters is if the .first operates on the
ActiveRecord_Relation before the reification
(the introduces the default order by :id), or if
the first (or [0]) operate after the reification,
on an Array of Set then.
