Remove N+1 queries in your Ruby on Rails app
Table of content
Introduction
Ever wondered why your page loads slower than it should when you do simple data representation or iteration over a bunch of data? One answer might be the N+1 problem: N+1 queries can slow your page significantly. But what is the N+1 problem, and how do you resolve it? That is what we will take a look at.
Example setup
For this post we will be using this database structure @gist and this seed data @gist.
N+1 problem
The N+1 query problem is a situation where you make extra calls to the database when you want to get specific associated data over and over again. For example, in Ruby:
irb(main):001:0> Item.all.each { |item| item.category.title } Item Load (0.9ms) SELECT "items".* FROM "items" Category Load (0.1ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 1]] Category Load (0.0ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 1]] Category Load (0.0ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 1]] Category Load (0.0ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 2]] Category Load (0.0ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 2]] Category Load (0.0ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" = ? LIMIT 1 [["id", 2]]
This example gets all items, then iterates over each of those records and gets the item’s category from the database. Imagine this happening for 50 or 100 items, with a heavier category model.
Common solutions
The most common solution is to use eager loading methods:
preload
Preload is the default strategy of the #includes method: it creates two separate queries, one for the main query and another for associated data. That means we can’t add #where({ categories: { title: “Fruits” } }); it will raise an error.
irb(main):001:0> Item.preload(:category).all.each { |item| item.category.title } Item Load (0.9ms) SELECT "items".* FROM "items" Category Load (0.1ms) SELECT "categories".* FROM "categories" WHERE "categories"."id" IN (1, 2)
An example will load all items with a preloaded category.
includes
Unlike #preload, #includes switches to a single query when the situation requires it, for example when you add a #where clause on the association. This makes a more complex SQL query:
irb(main):001:0> Item.includes(:category).where(categories: { title: 'Fruits' }).each { |item| item.category.title } SQL (0.2ms) SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id" WHERE "categories"."title" = ? [["title", "Fruits"]]
The example will load only items with the preloaded category whose title is Fruits.
You can also force #includes to make one query with the #references method (using LEFT OUTER JOIN):
irb(main):001:0> Item.includes(:category).references(:categories).each { |item| item.category.title } SQL (0.1ms) SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id"
eager_load
This method is the same as a combination of #includes and #references as it makes one query with LEFT OUTER JOIN:
irb(main):001:0> Item.eager_load(:category).each { |item| item.category.title } SQL (0.1ms) SELECT "items"."id" AS t0_r0, "items"."title" AS t0_r1, "items"."description" AS t0_r2, "items"."price" AS t0_r3, "items"."category_id" AS t0_r4, "categories"."id" AS t1_r0, "categories"."title" AS t1_r1, "categories"."description" AS t1_r2 FROM "items" LEFT OUTER JOIN "categories" ON "categories"."id" = "items"."category_id"
These are the most common N+1 occurrences, and they are easy to notice if you take a look at the Rails console logs, but some don’t look like this or are harder to notice.
Other solutions
I gathered these solutions and workarounds while fixing a project that had a lot of N+1 issues or many equal queries.
Count/sum/max/etc. with a dynamic condition
In a lot of places, I saw this or similar code:
def specific_date_wrong(date) Item.where("DATE(expire_at) = ?", date).count end
and then called on a single page for the whole month, so 30 queries to get the count for each day. Wasteful, if you ask me. This can be fixed with simple grouping:
def specific_date_right(date) @specific_date ||= Item.group('DATE(expire_at)').count @specific_date[date] || 0 end
it creates an instance variable with a hash where keys are dates and values are occurrences. All that with one query.
Join multiple queries into one
There might be queries that can’t be grouped but can be joined at the SQL level. For complicated things, ActiveRecord isn’t the tool you should be counting on. Instead, use plain SQL to get needed things from a database (be careful, if in future you decide to change database type, this might not work anymore).
Silly example of what I mean:
Item.where(validated: true).count Item.where(validated: false).count Item.sum(:count_1) Item.sum(:count_2) Item.sum(:count_3) Item.sum(:count_4)
which can be written in one query:
Item.select("count(case when validated = 't' then 1 else null end) as valid, count(case when validated = 'f' then 1 else null end) as invalid") .select("sum(count_1) as total_1, sum(count_2) as total_2, sum(count_3) as total_3, sum(count_4) as total_4") .take
It is a bit more complicated or messier, but that is how SQL rolls. Calling the #attributes method on the result will show that it has attributes we aliased.
Bullet
There is a gem called bullet that helps you find N+1 queries in your application in development mode by showing you notifications in the browser. Bullet will tell you when you need to eager load something and when you don’t need to. You can find more about bullet at github.
Summing it up
ActiveRecord is an easy-to-learn tool for making Rails applications but it still requires you to know some things before writing fast pages. These examples are what I have gathered. Share your thoughts and the examples you use in day-to-day Ruby programming.
Update (October 2026): These eager loading methods work the same way in current Rails, and Bullet is still maintained. Since Rails 6.1, Active Record also has strict loading: with strict_loading enabled on a record, an association or the whole app, a lazily loaded association raises an error, so N+1 queries fail in development and tests instead of reaching production.