Skip to content
Tutorials Center
My learning

Topics

Lesson 22 of 37 ยท Associations and Queries

Eager Loading and the N+1 Query Problem

Find and fix the Rails N+1 query problem with includes, preload and eager_load, strict_loading and counter caches, with a runnable query counter that shows 101 queries shrink to 2.

The most common reason a Rails page is slow is not Ruby. It is the database being asked the same small question hundreds of times. Imagine listing 100 posts with the name of each author. You load the posts (1 query), then, inside the loop, each post.author loads one user (100 more queries). That is N+1 queries: one for the list plus N for the items.

Active Record loads associations lazily, which is a nice default for single records and a trap for lists. This lesson shows how to spot the pattern, three ways to fix it, and how to make Rails complain when it happens. The runnable example is a stand-in with a query counter, since Rails is not available on this page. Background on joins: SQL joins and the previous lessons on associations and queries.

How N+1 happens

ERB
<% @posts.each do |post| %>
  <h2><%= post.title %></h2>
  <p>by <%= post.author.name %></p>      <%# one query per post %>
<% end %>
Ruby
@posts = Post.order(created_at: :desc).limit(100)

In log/development.log you would see one SELECT "posts".*, then SELECT "users".* WHERE "users"."id" = 7 LIMIT 1, repeated with different ids. The page "works", and gets slower as the data grows.

Ruby Stand-in: counting queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

The numbers speak for themselves: 101 queries versus 2. That second pattern, "load the parents, then load every child with a single IN query", is exactly what Rails does for you.

includes, preload and eager_load

Ruby
Post.includes(:author)                    # let Rails choose
Post.preload(:author)                     # always two queries
Post.eager_load(:author)                  # always one query with LEFT OUTER JOIN
Post.includes(:author, comments: :user)   # nested: comments and each comment's user
  • preload runs a separate query per association (WHERE id IN (...)). Good default, and it cannot filter the parent by the association.
  • eager_load uses one LEFT OUTER JOIN query. Use it when you need to filter or sort by the association's columns.
  • includes picks one of the two for you: preload normally, eager_load if your where refers to the other table (use .references(:table) when the condition is a raw SQL string).
Ruby
Post.includes(:author).where(users: { admin: true })           # becomes a JOIN
Post.preload(:author).where(users: { admin: true })            # error: no users table in query
Post.eager_load(:comments).where(comments: { spam: false })    # note: loads only matching comments

The last line shows a gotcha: filtering an eager-loaded association also restricts what appears in post.comments. If you need all comments but filtered posts, filter the posts with joins or where(id: ...) and preload separately.

Joins are not eager loading

Post.joins(:author) adds a JOIN for filtering but does not load authors into the posts. Reading post.author afterwards still triggers one query per post. Use joins to filter and includes/preload to load. You can combine both: Post.joins(:author).preload(:author), or use eager_load, which does the two jobs at once.

Reading less: select and pluck

Sometimes you do not need model objects. If the view needs only the author name, a join with select or pluck is cheaper:

Ruby
Post.joins(:author).pluck("posts.title", "users.name")
Post.joins(:author).select("posts.*, users.name AS author_name")

The second form adds a virtual attribute: post.author_name works without loading a User. It is fast, but the Post objects now carry an attribute that is not a real column, so keep it local to one view.

Counting associations without N+1

Showing "(12 comments)" next to each post creates an N+1 of counts. Options:

Ruby
class Comment < ApplicationRecord
  belongs_to :post, counter_cache: true     # posts.comments_count column
end

Post.left_joins(:comments).group("posts.id").select("posts.*, COUNT(comments.id) AS comments_n")

A counter cache is the best answer when you display the count often. For ad hoc counts, a grouped query returns every count at once: Comment.where(post_id: ids).group(:post_id).count gives {1 => 4, 2 => 0...} in a single query.

Ruby Stand-in: one grouped count instead of N counts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

Making Rails shout: strict_loading and bullet

You will not spot every N+1 by reading code. Two tools help:

Ruby
# Raise instead of lazy loading, for one query
Post.strict_loading.includes(:author).each { |p| p.author.name }   # fine
Post.strict_loading.each { |p| p.author }                          # raises StrictLoadingViolationError

# Or for a whole model
class Post < ApplicationRecord
  self.strict_loading_by_default = true
end

Rails can also be configured to log violations instead of raising: config.active_record.action_on_strict_loading_violation = :log. The bullet gem is a popular development-time detector that prints warnings when it sees N+1 patterns or unused eager loading. Over-eager loading is a real cost too: loading associations nobody reads wastes memory.

When a page is slow, open the log and count the repeated SELECT lines with the same shape. The shape of the repeated query tells you which association needs includes.

Eager loading in controllers and views

The right place for includes is the controller or a scope, not the view:

Ruby
class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author, :tags).published.recent.limit(50)
  end
end

Be careful with nested includes across large has_many chains: every extra level multiplies rows loaded. Prefer to load only what the page shows, paginate the list, and consider caching the rendered fragments (caching).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A page lists 50 posts and prints post.author.name in a loop without eager loading. How many queries does it run?

    One query for the posts plus one author query per post, so 1 + 50.
  2. 2. Which method always uses a single LEFT OUTER JOIN query?

    eager_load joins and loads in one query. preload uses separate IN queries; joins does not load the association.
  3. 3. Does Post.joins(:author) prevent N+1 when you then read post.author?

    joins does not populate the association; use includes, preload or eager_load for that.
  4. 4. What does strict_loading do?

    It turns accidental lazy loads into visible errors or log lines so N+1s are caught early.

Summary

  • N+1 means one query for a list plus one per item, caused by lazy association loading.
  • Fix it with includes, preload or eager_load; nested hashes load deeper levels.
  • joins filters but does not load; eager_load does both with one JOIN.
  • Use counter caches or grouped counts for numbers shown per row.
  • Turn on strict_loading or use a detector gem in development.
  • Load only what the page shows and paginate.

Next: Forms with form_with