has_one generates an ORDER BY clause in Rails 4 that may cause performance issues #12623

Closed
unspongeful opened this Issue Oct 24, 2013 · 5 comments

Comments

Projects
None yet
4 participants
@unspongeful

I'm not sure if this is an intended change from Rails 3.2, as it doesn't appear in the CHANGELOG, but I'm seeing a performance regression with Rails 4 on large tables using Postgresql because of the added 'ORDER BY' clause.

Example code:

require 'bundler'
Bundler.setup(:default)

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: ':memory:')

ActiveRecord::Schema.define do
  create_table :users
  create_table :profiles do |t|
    t.integer :user_id
  end
end

class User < ActiveRecord::Base
  has_one :profile
end
class Profile < ActiveRecord::Base
  belongs_to :user
end

User.create!(profile: Profile.new)

user = User.first

queries = []
subscriber = ActiveSupport::Notifications.subscribe('sql.active_record') do |name, s, f, id, values|
  queries << values[:sql]
end

user.profile

puts queries.first

I get:

-- Rails 4.0.0 and 4.0.0rc3
SELECT  "profiles".* FROM "profiles"  WHERE "profiles"."user_id" = ?  ORDER BY "profiles"."id" ASC LIMIT 1


-- Rails 3.2.15
SELECT  "profiles".* FROM "profiles"  WHERE "profiles"."user_id" = 1 LIMIT 1

To return to pre-4.0 behaviour I've changed has_one to the following:

class User < ActiveRecord::Base
  has_one :profile, -> { order(nil) }
end
@al2o3cr

This comment has been minimized.

Show comment
Hide comment
@al2o3cr

al2o3cr Oct 24, 2013

Contributor

Peculiar that ordering on an indexed field would cause a performance hit; can you try running EXPLAIN against the 4.0.0 query vs. the 3.2.15 one in your DB to see what's changed?

Contributor

al2o3cr commented Oct 24, 2013

Peculiar that ordering on an indexed field would cause a performance hit; can you try running EXPLAIN against the 4.0.0 query vs. the 3.2.15 one in your DB to see what's changed?

@rafaelfranca

This comment has been minimized.

Show comment
Hide comment
@rafaelfranca

rafaelfranca Oct 24, 2013

Member

This is intentional but it is not related to has_one but with the frist method.

You can find in the CHANGELOG entry:

Added default order to `first` to assure consistent results among different database engines. Introduced `take` as a replacement to the old behavior of first.
Member

rafaelfranca commented Oct 24, 2013

This is intentional but it is not related to has_one but with the frist method.

You can find in the CHANGELOG entry:

Added default order to `first` to assure consistent results among different database engines. Introduced `take` as a replacement to the old behavior of first.
@MSch

This comment has been minimized.

Show comment
Hide comment
@MSch

MSch Jan 21, 2014

Contributor

@rafaelfranca just ran into this. I read through #5153 and I can see the reasoning for .first but not why it should apply to has_one?

Contributor

MSch commented Jan 21, 2014

@rafaelfranca just ran into this. I read through #5153 and I can see the reasoning for .first but not why it should apply to has_one?

@rafaelfranca

This comment has been minimized.

Show comment
Hide comment
@rafaelfranca

rafaelfranca Jan 21, 2014

Member

I think I misunderstood the issue. I'll reopen

Member

rafaelfranca commented Jan 21, 2014

I think I misunderstood the issue. I'll reopen

@rafaelfranca rafaelfranca reopened this Jan 21, 2014

@ghost ghost assigned rafaelfranca Jan 21, 2014

rafaelfranca added a commit that referenced this issue Jan 21, 2014

`has_one` and `belongs_to` accessors don't add ORDER BY to the querie…
…s anymore.

Since Rails 4.0, we add an ORDER BY in the `first` method to ensure consistent
results among different database engines. But for singular associations this
behavior is not needed since we will have one record to return. As this
ORDER BY option can lead some performance issues we are removing it for singular
associations accessors.

Fixes #12623.

Conflicts:
	activerecord/CHANGELOG.md
	activerecord/lib/active_record/associations/singular_association.rb
@MSch

This comment has been minimized.

Show comment
Hide comment
@MSch

MSch Jan 21, 2014

Contributor

@rafaelfranca THANKS THANKS THANKS!!!!!!

Contributor

MSch commented Jan 21, 2014

@rafaelfranca THANKS THANKS THANKS!!!!!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment