Understanding and fixing the N+1 query in Rails

When it comes to backend performance, there is one problem everybody has heard of at least once: the N+1 query. It is easy to write by accident, invisible in development with ten rows in the database, and brutal at a thousand.

An N+1 query happens when your code runs N additional statements to fetch data the first query could have brought back on its own. One query for the list, then one more for every item in it.

What N+1 actually means

The name is literal. The 1 is the query that loads your collection. The N is the queries you then fire, one per record, because you asked each record for something it did not already have in memory. An ORM makes this comfortable to write, which is exactly why it happens — nothing in the code looks like a database call.

The grocery run

Imagine cooking a meal and forgetting an ingredient. You drive back to the shop. Then you notice another one missing, so you drive back again. Each trip is short and each trip feels reasonable, but you have now spent an hour on what should have been one list and one journey.

The fix is not to drive faster. It is to write the list down before you leave.

Eager loading is the list. You tell ActiveRecord up front what you are going to need, and it fetches all of it in one or two queries instead of discovering the requirement fifteen times.

What it looks like in code

Take a blog with two models. A Post has many Comments, and the index page shows each post with a count of its comments.

@posts = Post.all

@posts.each do |post|
  post.title
  post.comments.count
end

# SELECT * FROM posts
# SELECT COUNT(*) ... post_id = 1
# SELECT COUNT(*) ... post_id = 2
# ...and so on, forever

@posts = Post.includes(:comments)

@posts.each do |post|
  post.title
  post.comments.size
end

# SELECT * FROM posts
# SELECT * FROM comments
#   WHERE post_id IN (1,2,3,...)

Two things changed. includes tells ActiveRecord to load the comments alongside the posts, and count became size. That second change matters more than it looks: count always issues a COUNT query, even when the records are already loaded. size uses what is in memory if it can, and only queries when it cannot.

Spotting it before production does

You do not have to find these by reading. The Bullet gem watches your queries in development and tells you where you should have eager loaded — and, usefully, where you eager loaded something you never used.

group :development, :test do
  gem 'bullet'
end

# config/environments/development.rb
# Bullet.enable = true
# Bullet.bullet_logger = true
# Bullet.raise = true  # fail the test suite instead of warning

Setting Bullet.raise in the test environment is the part worth doing. A warning in a log scrolls past; a failing test does not.

includes, preload, eager_load

Rails gives you three, and they are not interchangeable:

• preload always uses a separate query per association. Two queries, no join, and you cannot reference the association in a where.
• eager_load always uses a single LEFT OUTER JOIN. One query, and you can filter on the association.
• includes picks one of the two for you, and switches to the join if it sees you referencing the association. This is the one to reach for by default.

The exception: if you are filtering on the association, say so explicitly. includes(:comments).where(comments: { spam: false }) works, but includes(:comments).references(:comments) tells Rails what you meant rather than leaving it to infer.

And when eager loading is not enough, the problem is usually one layer down: a missing index on the foreign key. Eager loading turns fifteen queries into two, but a comments.post_id with no index makes both of them slow.