Fixing N+1 Queries: Before and After

Nothing kills Rails application performance faster than N+1 queries. I learned this the hard way when my simple blog took 3 seconds to load a page with 10 posts. The culprit? One innocent-looking line in my view that triggered 11 database queries instead of 1.

In this post, I'll show you real examples of N+1 queries I've encountered and exactly how to fix them.

What Are N+1 Queries?

An N+1 query happens when your code executes one query to get a list of records, then executes N additional queries (one for each record) to get related data.

Example scenario: Show 10 blog posts with their authors
- 1 query to get the posts
- 10 queries to get each post's author
- Total: 11 queries (1 + 10 = N+1)

Example 1: Blog Posts with Authors

The Problem Code

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    @posts = Post.limit(10)
  end
end

<!-- app/views/posts/index.html.erb -->
<% @posts.each do |post| %>
  <div class="post">
    <h2><%= post.title %></h2>
    <p>By <%= post.author.name %></p>  <!-- N+1 query here! -->
    <p><%= post.excerpt %></p>
  </div>
<% end %>

What Happens in the Database

-- First query: get the posts
SELECT * FROM posts LIMIT 10;

-- Then one query for each post's author
SELECT * FROM users WHERE id = 1;  -- Post 1's author
SELECT * FROM users WHERE id = 2;  -- Post 2's author
SELECT * FROM users WHERE id = 1;  -- Post 3's author (duplicate!)
-- ... 7 more queries

The Fix: Use includes

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author).limit(10)
  end
end

What Happens Now

-- Just 2 queries total
SELECT * FROM posts LIMIT 10;
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

Performance improvement: 11 queries → 2 queries (80% reduction)

Example 2: Posts with Comments Count

The Problem Code

<!-- app/views/posts/index.html.erb -->
<% @posts.each do |post| %>
  <div class="post">
    <h2><%= post.title %></h2>
    <p>By <%= post.author.name %></p>
    <p><%= post.comments.count %> comments</p>  <!-- Another N+1! -->
  </div>
<% end %>

Database Queries

SELECT * FROM posts LIMIT 10;
SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);

-- Count queries for each post
SELECT COUNT(*) FROM comments WHERE post_id = 1;
SELECT COUNT(*) FROM comments WHERE post_id = 2;
-- ... 8 more count queries

The Fix: Counter Cache

Option 1: Counter Cache (Best for frequently accessed counts)

# Add counter cache column
rails generate migration AddCommentsCountToPosts comments_count:integer

# Migration
class AddCommentsCountToPosts < ActiveRecord::Migration[7.0]
  def change
    add_column :posts, :comments_count, :integer, default: 0

    # Populate existing data
    Post.find_each do |post|
      post.update(comments_count: post.comments.count)
    end
  end
end

# Update models
class Post < ApplicationRecord
  has_many :comments, dependent: :destroy
end

class Comment < ApplicationRecord
  belongs_to :post, counter_cache: true
end

<!-- Now just use the cached count -->
<p><%= post.comments_count %> comments</p>

Option 2: Eager Loading with Group (For dynamic counts)

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    @posts = Post.includes(:author)
                 .left_joins(:comments)
                 .select('posts.*, COUNT(comments.id) as comments_count')
                 .group('posts.id')
                 .limit(10)
  end
end

<!-- Access the calculated count -->
<p><%= post.comments_count %> comments</p>

Example 3: Nested Associations

The Problem Code

# Show posts with their comments and comment authors
class PostsController < ApplicationController
  def show
    @post = Post.find(params[:id])
  end
end

<!-- app/views/posts/show.html.erb -->
<h1><%= @post.title %></h1>
<p>By <%= @post.author.name %></p>

<div class="comments">
  <% @post.comments.each do |comment| %>
    <div class="comment">
      <p><%= comment.content %></p>
      <small>By <%= comment.author.name %></small>  <!-- N+1 for comment authors -->
    </div>
  <% end %>
</div>

Database Queries

SELECT * FROM posts WHERE id = 1;
SELECT * FROM users WHERE id = 1;  -- Post author
SELECT * FROM comments WHERE post_id = 1;
-- Then for each comment:
SELECT * FROM users WHERE id = 2;  -- Comment 1 author
SELECT * FROM users WHERE id = 3;  -- Comment 2 author
-- ... etc

The Fix: Nested Includes

class PostsController < ApplicationController
  def show
    @post = Post.includes(:author, comments: :author).find(params[:id])
  end
end

Result

-- Just 3 queries
SELECT * FROM posts WHERE id = 1;
SELECT * FROM users WHERE id = 1;  -- Post author
SELECT comments.*, users.* 
FROM comments 
LEFT JOIN users ON users.id = comments.author_id 
WHERE comments.post_id = 1;

Example 4: Complex Dashboard

The Problem Code

# app/controllers/dashboard_controller.rb
class DashboardController < ApplicationController
  def index
    @user = current_user
  end
end

<!-- app/views/dashboard/index.html.erb -->
<h1>Welcome, <%= @user.name %>!</h1>

<div class="stats">
  <p>Your posts: <%= @user.posts.count %></p>
  <p>Your comments: <%= @user.comments.count %></p>
  <p>Total likes: <%= @user.posts.sum { |post| post.likes.count } %></p>
</div>

<div class="recent-posts">
  <h2>Your Recent Posts</h2>
  <% @user.posts.recent.limit(5).each do |post| %>
    <div class="post-summary">
      <h3><%= post.title %></h3>
      <p><%= post.comments.count %> comments</p>
      <p><%= post.likes.count %> likes</p>
    </div>
  <% end %>
</div>

Multiple N+1 Problems

1. @user.posts.sum { |post| post.likes.count } - loads all posts, then queries likes for each
2. post.comments.count in the loop - queries comments for each post
3. post.likes.count in the loop - queries likes for each post

The Fix: Strategic Eager Loading and Aggregation

# app/controllers/dashboard_controller.rb
class DashboardController < ApplicationController
  def index
    @user = current_user
    @user_stats = calculate_user_stats
    @recent_posts = load_recent_posts_with_counts
  end

  private

  def calculate_user_stats
    {
      posts_count: @user.posts.count,
      comments_count: @user.comments.count,
      total_likes: @user.posts.joins(:likes).count
    }
  end

  def load_recent_posts_with_counts
    @user.posts
         .recent
         .left_joins(:comments, :likes)
         .select('posts.*, 
                  COUNT(DISTINCT comments.id) as comments_count,
                  COUNT(DISTINCT likes.id) as likes_count')
         .group('posts.id')
         .limit(5)
  end
end

<!-- app/views/dashboard/index.html.erb -->
<h1>Welcome, <%= @user.name %>!</h1>

<div class="stats">
  <p>Your posts: <%= @user_stats[:posts_count] %></p>
  <p>Your comments: <%= @user_stats[:comments_count] %></p>
  <p>Total likes: <%= @user_stats[:total_likes] %></p>
</div>

<div class="recent-posts">
  <h2>Your Recent Posts</h2>
  <% @recent_posts.each do |post| %>
    <div class="post-summary">
      <h3><%= post.title %></h3>
      <p><%= post.comments_count %> comments</p>
      <p><%= post.likes_count %> likes</p>
    </div>
  <% end %>
</div>

Detecting N+1 Queries

1. Use the Bullet Gem

# Gemfile
group :development do
  gem 'bullet'
end

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert = true
  Bullet.bullet_logger = true
  Bullet.console = true
  Bullet.rails_logger = true
end

Bullet will alert you when it detects N+1 queries and suggest fixes.

2. Monitor the Rails Log

Look for patterns like this in your development log:

User Load (0.3ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
Post Load (0.2ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]

3. Use Rails Query Trace

# Gemfile
group :development do
  gem 'query_trace'
end

This shows you exactly which line of code triggered each query.

Advanced Techniques

1. Preload vs Includes vs Eager_load

# preload: Always uses 2 separate queries
Post.preload(:author)

# includes: Uses 2 queries, or LEFT JOIN if you filter on the association
Post.includes(:author)
Post.includes(:author).where(users: { active: true })  # Forces LEFT JOIN

# eager_load: Always uses LEFT JOIN
Post.eager_load(:author)

2. Custom Select with Associations

# Get posts with author names in one query
posts = Post.joins(:author)
            .select('posts.*, users.name as author_name')

# Access without additional queries
posts.each do |post|
  puts post.author_name  # No additional query
end

3. Batch Loading for Complex Cases

# If you can't use includes, batch load manually
posts = Post.limit(10)
author_ids = posts.map(&:author_id).uniq
authors = User.where(id: author_ids).index_by(&:id)

posts.each do |post|
  author = authors[post.author_id]
  puts "#{post.title} by #{author.name}"
end

Performance Comparison

Here's a real example from one of my apps:

Before Optimization

• Page load time: 2.3 seconds
• Database queries: 47 queries
• Memory usage: 85MB

After Optimization

• Page load time: 0.4 seconds
• Database queries: 6 queries
• Memory usage: 32MB

Result: 83% faster load time, 87% fewer queries

Best Practices

1. Always Use Includes for Associations You'll Access

# Good
@posts = Post.includes(:author, :tags)

# Bad
@posts = Post.all
# Then accessing post.author or post.tags in views

2. Use Counter Caches for Frequently Accessed Counts

# Instead of post.comments.count everywhere
# Use post.comments_count with counter cache

3. Aggregate in the Database When Possible

# Instead of Ruby calculations
users.sum { |user| user.posts.count }

# Use SQL aggregation
User.joins(:posts).group('users.id').count

4. Profile Before and After

# Use benchmark to measure improvements
Benchmark.measure do
  # Your code here
end

Common Gotchas

1. Polymorphic Associations

# This won't work
Comment.includes(:commentable)

# Use this instead
Comment.includes(:commentable).where(commentable_type: 'Post')

2. Conditional Associations

# Be careful with conditions
has_many :published_posts, -> { where(published: true) }, class_name: 'Post'

# includes might not respect the condition
User.includes(:published_posts)  # May load all posts

3. Order Matters with Complex Includes

# Good
Post.includes(:comments).order('posts.created_at DESC')

# Can be problematic
Post.includes(:comments).order('comments.created_at DESC')

Monitoring Production Performance

1. Use APM Tools

• New Relic - Great query analysis
• Scout APM - Rails-focused monitoring
• Skylight - Built for Rails performance

2. Set Up Query Monitoring

# config/application.rb
config.active_record.warn_on_records_fetched_greater_than = 500

3. Regular Performance Audits

# Create a rake task to check for common issues
task :performance_audit do
  # Check for missing includes
  # Analyze slow queries
  # Review counter cache usage
end

N+1 queries are one of the most common Rails performance problems, but they're also one of the easiest to fix once you know what to look for. Use the Bullet gem during development, monitor your query logs, and always think about what associations you'll need before you fetch data.

Key takeaways:
- Use includes when you'll access associations
- Set up counter caches for frequently counted associations
- Use the Bullet gem to detect N+1 queries
- Profile your code before and after optimizations
- Monitor production performance with APM tools

Start by adding Bullet to your development environment and see what N+1 queries it finds. You might be surprised by how many performance improvements are hiding in your codebase!

---

Next up: "Rails Asset Pipeline Confusion: A Beginner's Guide" - We'll demystify how Rails handles CSS, JavaScript, and images.