Kyle d’Oliveira · 2021-06-30 · rails, backend

90% of Rails N+1 queries solved with a drop-in fix

N+1 queries come up very often when working with Rails. N+1 queries are a silent performance tax both for your application and for developers. If a developer writes new code that introduces an N+1 query, it only slows down performance. It does not influence behavior, so it is easy to sneak into production. Because it is so easy to make it to production, developers need to take extra time to be vigilant when writing and reviewing code. This is a lot of unnecessary work that we can avoid very easily with two simple lines.

What are the two lines?

The first line is an addition of a gem, JitPreloader, to your Gemfile.

gem 'jit_preloader'

The second line is in an initializer, such as config/initializer/jit_preloader.rb , that will enable it throughout your application.

JitPreloader.globally_enabled = true

With those two lines, the N+1 queries will disappear.

Before:

User.limit(10).each do |user|
  user.tasks.each do |task|
    ...
  end
end

[17:39:34.648]   User Load (1.0ms)  SELECT  "users".* FROM "users" LIMIT $1  [["LIMIT", 10]]

[17:39:34.707]   Task Load (1.6ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 1]]
[17:39:34.803]   Task Load (0.6ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 2]]
[17:39:34.805]   Task Load (0.4ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 3]]
[17:39:34.808]   Task Load (0.5ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 4]]
[17:39:34.810]   Task Load (0.3ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 5]]
[17:39:34.813]   Task Load (1.1ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 6]]
[17:39:34.815]   Task Load (0.3ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 7]]
[17:39:34.817]   Task Load (0.2ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 8]]
[17:39:34.818]   Task Load (0.2ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 9]]
[17:39:34.854]   Task Load (0.2ms)  SELECT "tasks".* WHERE "tasks"."user_id" = $1  [["user_id", 10]]

After:

User.limit(10).each do |user|
  user.tasks.each do |task|
    ...
  end
end

[17:42:25.668]   User Load (0.8ms)  SELECT  "users".* FROM "users" LIMIT $1  [["LIMIT", 10]]

[17:42:25.734]   Task Load (0.7ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]

You no longer need to use includes , preload , or eager_load . You will not need to audit the code to figure out what associations are being used or to find hidden N+1 queries. It is all handled automatically and it will only preload the association if you use it. You will always be preloading the exact right amount of data. We added these lines and immediately saw noticeable performance improvements:

Average PX Response Time	Improvement %
P75	8%
P90	22%
P95	31%
P99	38%

This is a huge benefit for the amount of work required.

How does it work?

The gem takes advantage of a standard preloading mechanism that Rails uses but that is not widely known. You typically have to deal with an N+1 query by locating where is generated and adding on includes, preload , or eager_load . E.g.:

User.includes(:tasks).limit(10) do |user|
  user.tasks.each do |task|
    ...
  end
end

Alternatively, you can preload the association later on using ActiveRecord::Associations::Preloader . E.g.:

users = User.limit(10)

ActiveRecord::Associations::Preloader.new.preload(users, :tasks)

users.each do |user|
  user.tasks.each do |task|
    ...
  end
end

With the JitPreloader, each ActiveRecord object returned from a query will have a reference to the other objects returned by the same query. If you access an association that has not been loaded yet, the gem will automatically load that association using ActiveRecord::Associations::Preloader across all of those objects. This works across all associations and only happens for associations that are accessed.

Does this get rid of all N+1 queries?

No, it does not. Our team at Aha! removed about 90% of the N+1 queries using the above two lines. Most N+1 queries can be fixed immediately and automatically, but there are certain patterns that are not automatically solved by this. These require more manual effort to fix.

It will not automatically fix N+1 queries that are generated through the use of aggregate methods. e.g.

User.limit(10).each do |user|
  user.tasks.count
end

[20:45:43.620]    (0.8ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 1]]
[20:45:43.623]    (0.5ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 2]]
[20:45:43.625]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 3]]
[20:45:43.627]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 4]]
[20:45:43.628]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 5]]
[20:45:43.630]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 6]]
[20:45:43.631]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 7]]
[20:45:43.633]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 8]]
[20:45:43.635]    (0.5ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 9]]
[20:45:43.637]    (0.4ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."user_id" = $1  [["user_id", 10]]

Luckily the JitPreloader offers a solution here as well. You can setup a special aggregation method that on the model that will allow you to preload this

class User < ApplicationRecord
  has_many_aggregate :tasks, :count_all, :count, "*", default: 0
end

# Now you can write
User.limit(10).each do |user|
  user.tasks_count_all
end

[20:57:26.235]   User Load (0.9ms)  SELECT  "users".* FROM "users" LIMIT $1  [["LIMIT", 10]]
[20:57:26.301]    (1.5ms)  SELECT COUNT(*) AS count_all, tasks.user_id AS tasks_user_id FROM "tasks" WHERE "tasks"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) GROUP BY task_users.user_id  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]

It will not automatically fix N+1 queries due to an association being further queried

User.limit(10).each do |user|
  user.tasks.where("created_at > '2021-05-27 00:00:00'").each do |task|
    ...
  end
end

There is also a new pattern available from the JitPreloader that can help with this.

User.limit(10).each do |user|
 user.preload_scoped_relation(
    name: "Newly created tasks",
    base_association: :tasks,
    preload_scope: Task.where("created_at > '2021-05-27 00:00:00'")
  ).each do |task|
    ...
  end
end

[21:03:16.148]   User Load (0.5ms)  SELECT  "users".* FROM "users" LIMIT $1  [["LIMIT", 10]]

[21:03:16.200]   Task Load (0.4ms)  SELECT "tasks".* FROM "tasks" WHERE (created_at > '2021-05-27 00:00:00') AND "tasks"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) GROUP BY task_users.user_id  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]

It will not automatically fix N+1 queries that are explicitly told to go back to the database

User.limit(10).each do |user|
  user.tasks.reload.each do |task|
    ...
  end
end

It will not automatically fix N+1 queries if you don't go through an association

User.limit(10).each do |user|
  Task.where(user_id: user.id).each do |task|
    ...
  end
end

Let's remove most of the N+1 queries from your application quickly, and give you some tools to tackle some of the more complicated N+1 query cases today.

I wrote the JitPreloader gem five years ago when I got tired of having to hunt down N+1 queries by hand. It is a piece of work that I am incredibly proud of, and I am consistently surprised how much value we can get from it. It is easy for everyone to take advantage of this work and get value from it as well. I want to thank my colleagues at Clio for helping redefine this concept into a production ready package.

If you've also felt that dissatisfaction with other tools, give Aha! Develop a try. Our early access program is open now and there are a limited number of spots available — sign up quickly if you are interested. Learn more about Aha! Develop and how you can request access so your team can start using it: https://www.aha.io/develop/overview