Rails N+1 Queries: joins vs preload vs eager_load vs includes

ActiveRecord gives you four ways to load associations, and they generate different SQL. Here is what each one actually does, straight from the query log, followed by the part that catches people out: the innocent-looking method chains that reintroduce the N+1 you just fixed.

joins

Use this when you only care about the users that have questions and you do not need the questions themselves:

> User.joins(:questions).to_a;0
  User Load (0.8ms)  SELECT "users".* FROM "users" INNER JOIN "questions" ON "questions"."user_id" = "users"."id"

If you only need id and name:

> User.joins(:questions).select(:id, :name).to_a;0
  User Load (1.0ms)  SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "questions" ON "questions"."user_id" = "users"."id"

or

> User.joins(:questions).pluck(:id, :name)

preload: two queries

> User.preload(:questions).to_a;0
  User Load (0.5ms)  SELECT "users".* FROM "users"
  Question Load (0.7ms)  SELECT "questions".* FROM "questions" WHERE "questions"."user_id" IN ($1, $2, $3, $4, $5)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5]]

eager_load: one query with a LEFT OUTER JOIN

> User.eager_load(:questions).to_a
  SQL (0.9ms)  SELECT "users"."id" AS t0_r0, "users"."login" AS t0_r1, "users"."name" AS t0_r2, "users"."slug" AS t0_r3, "users"."email" AS t0_r4, "users"."encrypted_password" AS t0_r5, "users"."reset_password_token" AS t0_r6, "users"."reset_password_sent_at" AS t0_r7, "users"."remember_created_at" AS t0_r8, "users"."created_at" AS t0_r9, "users"."updated_at" AS t0_r10, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" LEFT OUTER JOIN "questions" ON "questions"."user_id" = "users"."id"

It can be combined with joins:

> User.joins(:questions).eager_load(:questions).to_a

includes: Rails decides for you

On its own, includes behaves like preload:

> User.includes(:questions).select(:id, :name).to_a;0
  User Load (0.7ms)  SELECT "users"."id", "users"."name" FROM "users"
  Question Load (0.4ms)  SELECT "questions".* FROM "questions" WHERE "questions"."user_id" IN ($1, $2, $3, $4, $5)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5]]

With references, eager_load is used

> User.includes(:questions).select(:id, :name).references(:questions).to_a;0
  SQL (0.9ms)  SELECT "users"."id", "users"."name", "users"."id" AS t0_r0, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" LEFT OUTER JOIN "questions" ON "questions"."user_id" = "users"."id"

With joins, eager_load is used

> User.joins(:questions).includes(:questions).select(:id, :name).to_a;0
  SQL (0.9ms)  SELECT "users"."id", "users"."name", "users"."id" AS t0_r0, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" INNER JOIN "questions" ON "questions"."user_id" = "users"."id"

Antipatterns that bring the N+1 back

Here is the trap. You eager-loaded the association, but any further method on it that hits the database starts a fresh query per record: count, order, where, and friends. The preloaded data is ignored.

count (use size instead)

> User.includes(:questions).select(:id, :name).references(:questions).map { |user| user.questions.count }
  SQL (1.0ms)  SELECT "users"."id", "users"."name", "users"."id" AS t0_r0, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" LEFT OUTER JOIN "questions" ON "questions"."user_id" = "users"."id"
  Question Count (0.3ms)  SELECT COUNT(*) FROM "questions" WHERE "questions"."user_id" = $1  [["user_id", 2]]
  Question Count (0.2ms)  SELECT COUNT(*) FROM "questions" WHERE "questions"."user_id" = $1  [["user_id", 4]]

order

> User.includes(:questions).select(:id, :name).references(:questions).map { |user| user.questions.order(:id).to_a };0
  SQL (0.8ms)  SELECT "users"."id", "users"."name", "users"."id" AS t0_r0, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" LEFT OUTER JOIN "questions" ON "questions"."user_id" = "users"."id"
  Question Load (0.2ms)  SELECT "questions".* FROM "questions" WHERE "questions"."user_id" = $1 ORDER BY "questions"."id" ASC  [["user_id", 2]]

where

> User.includes(:questions).select(:id, :name).references(:questions).map { |user| user.questions.where(title: '').to_a };0
  SQL (0.8ms)  SELECT "users"."id", "users"."name", "users"."id" AS t0_r0, "questions"."id" AS t1_r0, "questions"."user_id" AS t1_r1, "questions"."title" AS t1_r2, "questions"."body" AS t1_r3, "questions"."answered_at" AS t1_r4, "questions"."views_count" AS t1_r5, "questions"."answers_count" AS t1_r6, "questions"."comments_count" AS t1_r7, "questions"."created_at" AS t1_r8, "questions"."updated_at" AS t1_r9 FROM "users" LEFT OUTER JOIN "questions" ON "questions"."user_id" = "users"."id"
  Question Load (0.3ms)  SELECT "questions".* FROM "questions" WHERE "questions"."user_id" = $1 AND "questions"."title" = $2  [["user_id", 2], ["title", ""]]
  Question Load (0.3ms)  SELECT "questions".* FROM "questions" WHERE "questions"."user_id" = $1 AND "questions"."title" = $2  [["user_id", 4], ["title", ""]]

The same applies to limit, select, distinct, reorder, rewhere, and reselect.

Solutions

Filter or sort the already-loaded records in Ruby instead of asking the database again:

user.questions.sort_by(&:created_at)
# or
user.questions.reject { |question| question.title.empty? }

Or, when a filtered association is used often, define it as its own association and preload that:

has_many(:empty_questions, -> { where(title: '') }, class_name: 'Question')

User.includes(:empty_questions).map { |user| user.empty_questions }