How to Find, Debug and Fix N+1 Queries in Rails

N+1 queries are a common performance issue in Rails applications, where the database is queried multiple times for related records, resulting in slower response times.

The reason for this is how ActiveRecord (which is a library in the Rails framework that helps connect data to the application) works. By default, ActiveRecord uses lazy loading, which means that it only loads associated records when they are accessed. This can lead to a situation where, for example, a list of records is loaded with a single query, but then for each record, an additional query is made to load its associated records. As a result many queries are executed to load associated records and it can slow down the application.

Another reason for the prevalence of N+1 queries is that they can be difficult to detect and optimize. It often requires careful analysis of the queries being executed and the data relationships in the application to identify where N+1 queries are occurring and how to optimize them.

Here are some steps to find, debug and fix N+1 queries in Rails:

1. Identify the N+1 queries: Start by identifying the queries that are causing the N+1 problem. One way to do this is to use the bullet gem, which helps to detect N+1 queries in your Rails application.
2. Analyze the queries: Once you have identified the N+1 queries, analyze them to understand the data that is being queried and the relationships between the records. This will help you to determine the optimal way to load the data and avoid N+1 queries.
3. Use eager loading: One way to avoid N+1 queries is to use eager loading. Eager loading loads all the associated records in a single query, rather than querying the database for each record individually. You can use the includes method to load associated records with the main query.
4. Use joins: Another way to optimize queries and avoid N+1 queries is to use joins. Joins allow you to combine related records into a single query, rather than querying the database for each record individually. You can use the joins method to specify the associations to be joined.
5. Use counter_cache: In situation where you need to count the number of associated records, you can use counter_cache to avoid N+1 queries. Counter_cache keeps a count of the associated records in a separate column, which is updated when a new record is added or deleted.
6. Test and measure: After making changes to your queries, test and measure the performance of your application to ensure that the changes have improved performance and resolved the N+1 queries.

By following these steps, you can identify, debug, and fix N+1 queries in your Rails application, and improve the performance of your application.

So let’s dive deeper into each of these!

Identify the N+1 queries #

Identifying N+1 queries in a Rails application is the first step in addressing this common performance issue. Here are some additional tips on how to identify N+1 queries.

Look for repeated queries #

One common symptom of N+1 queries is repeated queries with the same pattern. For example, if you have a list of posts and you’re displaying the author’s name for each post, you might see repeated queries for the author’s record.

Let’s say you have a Post model that belongs to a Author:

class Author < ApplicationRecord
    has_many :posts
end

class Post < ApplicationRecord
    belongs_to :author
end

And you have a view that displays a list of posts, along with the author’s name for each post:

<% @posts.each  do |post| %>
  <div class="post">
    <h2><%= post.title %></h2>
    <p>By <%= post.author.name %></p>
    <p><%= post.body %></p>
  </div>
<% end %>

If you load the posts using a simple @posts = Post.all query in your controller, this will result in an N+1 query where one query is executed to load all the posts, and N additional queries are executed to load the author’s name for each post.

Use logs #

Another way to identify N+1 queries is to look at your application logs. The logs will show all the queries that are executed when a request is made to your application. Look for repeated queries or queries that are executed multiple times for related records.

1. Make sure that logging is enabled in your Rails application. Logging is usually enabled by default in development and test environments but may need to be configured in production environments.
2. Generate some traffic to your application by interacting with the pages that you suspect might be causing N+1 queries.
3. Check your application logs for repeated queries or queries that are executed multiple times for related records. Look for patterns in the logs, such as the same query being executed multiple times with only the ID of the record changing.
4. Once you have identified the queries that are causing the N+1 issue, you can optimize them.

Here’s an example of what you might see in your application logs for an N+1 query:

Started GET "/posts" for 127.0.0.1 at 2023-03-09 14:30:00 -0500
Processing by PostsController #index as HTML
Post Load (1.0ms)  SELECT "posts".* FROM "posts"
Author Load (1.5ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 1]]
Author Load (1.0ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 2]]
Author Load (1.1ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 3]]
Author Load (1.2ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 4]]
Author Load (1.0ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 5]]
Author Load (1.1ms)  SELECT "authors".* FROM "authors" WHERE "authors"."id" = ?  [["id", 6]]
Rendered posts/index.html.erb (10.0ms)
Completed 200 OK in 100ms (Views: 20.0ms | ActiveRecord: 10.0ms)

In this example, the query for the list of posts is executed first, followed by a separate query for each author. This results in 6 additional queries being executed, which could be optimized using eager loading or joins.

Use database tools #

You can also use database tools to identify N+1 queries. For example, if you’re using PostgreSQL, you can use the pg_stat_statements extension to analyze the queries executed by your application. Here’s how you can use it:

1. Install pg_stat_statements extension.

   class AddPgStatStatementsExtension < ActiveRecord::Migration[6.1]
       def change
           enable_extension :pg_stat_statements
       end
   end

2. After enabling pg_stat_statements, PostgreSQL will start recording statistics about the queries executed by your application. You can view these statistics by querying the pg_stat_statements table.

   SELECT query, total_time, calls FROM pg_stat_statements ORDER BY total_time DESC;

   This will return a list of all the queries executed by your application, along with the total time taken to execute each query and the number of times the query was called.

3. To identify N+1 queries, look for queries that are executed multiple times with different parameter values. For example, if you have a list of posts and you’re displaying the author’s name for each post, you might see a query that looks like this:

   SELECT * FROM authors WHERE id = ?

   If this query is executed multiple times with different author IDs, it’s a sign of an N+1 query.

By using pg_stat_statements to analyze the queries executed by your Rails application, you can quickly identify N+1 queries and take steps to resolve them, which can help to improve the performance of your application.

Use bullet gem #

The bullet gem is a popular Ruby gem that helps detect and optimize N+1 queries in a Rails application. Here are the steps to use the Bullet gem in your Rails application:

1. Add the Bullet gem to your Gemfile and install it:

    
   gem 'bullet', group: 'development'

2. Enable the Bullet gem with generate command:

   bundle exec rails g bullet:install

3. Configure the Bullet gem in your config/environments/development.rb file:

   config.after_initialize do
       Bullet.enable = true
       Bullet.alert = true
   end

   The enable option turns on Bullet, while the alert option displays an alert in your application’s console when N+1 queries are detected. There are more options for configuration, that you can check in the official doc.

4. Use the includes or joins method to eager load associations:

   # Instead of this:
   @posts = Post.all
   @posts.each do |post|
       puts post.comments.to_a
   end
   
   # Use this:
   @posts = Post.includes(:comments).all
   @posts.each do |post|
        puts post.comments.to_a
   end
      

   The includes method tells Rails to load the associated comments with the posts in a single query, instead of loading them one at a time (which would result in N+1 queries).

5. Run your Rails application in development mode and look for Bullet alerts in your console. If Bullet detects N+1 queries, it will display an alert like this:

   N+1 Query detected
   Post => [:comments]
   Add to your finder: :includes => [:comments]

   The alert tells you which association is causing the N+1 query, and suggests using the includes method to eager load the association.

Analyze the queries #

Once you have identified the N+1 queries in your Rails application, it’s important to analyze the queries to understand the data that is being queried and the relationships between the records. This will help you to determine the optimal way to load the data and avoid N+1 queries.

Look at the SQL generated by Rails. #

When Rails executes a query, it generates SQL that you can view in the Rails console or in a tool like pgAdmin for PostgreSQL. Look at the SQL generated by Rails to understand the tables and columns being queried, as well as any joins or conditions that are applied.

Understand the associations between your models. #

To optimize your queries and avoid N+1 queries, it’s important to understand the associations between your models. For example, if you have a Post model that belongs to an Author model, you can use a join to load all posts and their authors in a single query.

Use EXPLAIN ANALYZE to understand query performance. #

If you’re using PostgreSQL, you can use the EXPLAIN ANALYZE command to analyze the performance of a query. This command will show you detailed information about how the query was executed, including the time taken to execute each step. To use EXPLAIN ANALYZE, you simply need to prefix your query with the command, like this:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 123;

When you execute this command, PostgreSQL will return a detailed breakdown of how the query was executed, including information about:

• The execution plan: This shows the steps that PostgreSQL took to execute the query, including any joins, sorts, or filters that were applied.
• The time taken for each step: This shows how long each step took to execute, which can help you identify any bottlenecks in the query.
• The estimated and actual number of rows returned by each step: This can help you identify any performance issues related to data volume.

Here’s an example of what PostgreSQL return when you use EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 123;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Index Scan using posts_author_id_idx on posts  (cost=0.42..8.44 rows=1 width=221) (actual time=0.031..0.033 rows=1 loops=1)
Index Cond: (author_id = 123)
Planning Time: 0.055 ms
Execution Time: 0.077 ms
(4 rows)

In this example, PostgreSQL returns a breakdown of how the query was executed. The first line shows that an index scan was used on the posts_author_id_idx index, and that it returned one row (rows=1). The second line shows that the query took 0.031ms to execute, and the third line shows that the overall execution time of the query was 0.077ms. By analyzing this output, you can see that the query was executed efficiently and quickly, which is a good indication that it is well-optimized. However, if the query had taken longer to execute or had a higher estimated cost, you could use this information to identify areas where you could optimize the query to improve its performance.

Here’s an example of what PostgreSQL might return if you have an N+1 query: Let’s say we have a model Post that belongs to a Author:

class Author < ApplicationRecord
    has_many :posts
end

class Post < ApplicationRecord
    belongs_to :author
end

If we have a controller action that loads all the posts for an author but doesn’t use eager loading, it might look something like this:

def index
    @author = Author.find(params[:author_id])
    @posts = @author.posts
end

This would result in an N+1 query, where one query is executed to load the author and N additional queries are executed to load each post associated with the author.

To use EXPLAIN ANALYZE to analyze this query, we could run:

EXPLAIN ANALYZE SELECT * FROM posts WHERE author_id = 123;

And here’s an example of what PostgreSQL might return:

Seq Scan on posts  (cost=0.00..8.44 rows=10 width=221) (actual time=0.002..0.003 rows=10 loops=1)
Filter: (author_id = 123)
Rows Removed by Filter: 990
Planning Time: 0.042 ms
Execution Time: 0.020 ms

In this example, you can see that the query is doing a sequential scan on the posts table to find all posts where author_id is 123. Since there are only 10 posts for this author, the actual time to execute the query is very fast (0.002-0.003ms). However, if there were many more posts associated with this author, or if there were many more authors being loaded with their associated posts, the query time could become much slower, leading to performance issues. To avoid this, you can use eager loading or joins to load all the associated records in a single query, rather than querying the database for each record individually.

Using EXPLAIN ANALYZE can be particularly useful when you’re trying to optimize queries and avoid N+1 queries. By understanding how your queries are executed and their performance characteristics, you can identify areas where you can improve query performance and reduce the number of queries executed by your application. It’s worth noting that EXPLAIN ANALYZE can be quite verbose and provide a lot of information, so it may take some time to interpret the results. However, it can be an invaluable tool for optimizing your queries and improving the performance of your PostgreSQL database.

Identify patterns in your queries. #

Identifying patterns in your queries is an important step in optimizing your application and avoiding N+1 queries. Here are some common patterns to look out for:

1. Repeated association loading: If you’re loading the same associated records multiple times in different parts of your application, it could be a sign of N+1 queries. For example, if you’re loading a author’s posts and then loading the comments for each post separately, you might be better off using eager loading or joins to load all the associated records in a single query.
2. Deeply nested associations: If you’re loading deeply nested associations, it can be easy to accidentally trigger N+1 queries. For example, if you have a Author model that has_many Posts, and each Post has_many Comments, loading all of an author’s comments could require many separate queries. In this case, using eager loading or joins to load the associated records in a single query can be much more efficient.
3. Unnecessary or redundant queries: Look for queries that are being executed unnecessarily or that could be consolidated into a single query. For example, if you’re loading a list of an authors and then separately loading their associated profiles, you might be able to use eager loading or joins to load all the data in a single query.

By identifying these patterns in your queries, you can better understand where N+1 queries are likely to occur and take steps to optimize your queries to avoid them.

Use eager loading #

Eager loading is a technique used in Rails to avoid N+1 queries. Eager loading loads all the associated records in a single query, rather than querying the database for each record individually. You can use the includes method to load associated records with the main query. Here’s an example of how to use includes to eager load associated records:

# Load all posts and their authors in a single query
@posts = Post.includes(:author)

# Access the author for each post without triggering additional queries
@posts.each do |post|
    puts post.author.name
end

In this example, the includes method is used to load all posts and their authors in a single query. The :author argument tells Rails to load the associated author records along with the posts. When you access the author attribute for each post, Rails will use the pre-loaded author records rather than querying the database for each post individually.

You can also use includes to load multiple levels of associations in a single query. For example, if you have a Post model that belongs to an Author model, and the Author model has many Comments, you can load all posts, their authors, and their comments in a single query like this:

# Load all posts, their authors, and their comments in a single query
@posts = Post.includes(author: :comments)

# Access the comments for each post's author without triggering additional queries
@posts.each do |post|
    puts post.author.comments.pluck(:body)
end

In this example, the includes method is used to load all posts, their authors, and their comments in a single query. The author: :comments argument tells Rails to load the associated author records along with their comments. When you access the comments attribute for each post’s author, Rails will use the pre-loaded comment records rather than querying the database for each comment individually. Using eager loading can significantly reduce the number of queries executed by your Rails application and improve its performance. It’s a best practice to use eager loading whenever possible to avoid N+1 queries.

Use batch processing with large amount of data #

If you load a lot of data don’t forget that with includes you will load even more data, that’s where the batch processing will help to avoid performance issues and N+1 queries.

Batch processing is a technique used to load large sets of records in smaller batches, rather than loading all the records at once. In Rails, you can use the find_each method to load records in batches. This method loads a batch of records and yields them into a block. Once the block is executed, the next batch of records is loaded and the process continues until all the records have been processed.

Here’s an example of using find_each to load records in batches:

# Load all the posts in batches of 100
Post.find_each(batch_size: 100) do |post|
# Do something with each post
end

This code will load the posts in batches of 100 and yield them to the block. Once the block is executed, the next batch of posts is loaded and the process continues until all the posts have been processed. Using batch processing can be especially useful when you need to perform some operation on a large set of records. By loading the records in smaller batches, you can avoid loading all the records at once and potentially running out of memory or causing performance issues.

How it can help with N+1 #

For example, let’s say you have a large set of blog posts, and each post has many comments. Without eager loading, Rails would need to make a separate query for each post’s comments, resulting in an N+1 query problem. With eager loading, you will not have this problem, but most likely there will be permormance issues (with large amount of data).

However, if you use includes with batch processing, Rails will load the posts and comments in batches, avoiding the need for additional queries and save the resources.

In summary, while batch processing is primarily used to manage memory usage, it can also be used to help solve the N+1 query problem when used in conjunction with eager loading techniques.

Use joins #

Joins are another way to optimize queries and avoid N+1 queries in Rails. Joins allow you to combine related records into a single query, rather than querying the database for each record individually. You can use the joins method to specify the associations to be joined.

Here’s an example of how to use joins to avoid N+1 queries:

# Load all posts and their authors using a join
@posts = Post.joins(:author)

# Access the author for each post without triggering additional queries
@posts.each do |post|
    puts post.author.name
end

In this example, the joins method is used to load all posts and their authors using a join. The :author argument tells Rails to join the associated author records with the posts. When you access the author attribute for each post, Rails will use the pre-joined author records rather than querying the database for each post’s author individually. You can also use joins to load multiple levels of associations in a single query. For example, if you have a Post model that belongs to an Author model, and the Author model has many Comments, you can load all posts, their authors, and their comments using a join like this:

# Load all posts, their authors, and their comments using a join
@posts = Post.joins(author: :comments)

# Access the comments for each post's author without triggering additional queries
@posts.each do |post|
    puts post.author.comments.pluck(:body)
end

In this example, the joins method is used to load all posts, their authors, and their comments using a join. The author: :comments argument tells Rails to join the associated author records along with their comments. When you access the comments attribute for each post’s author, Rails will use the pre-joined comment records rather than querying the database for each comment individually.

Using joins can significantly reduce the number of queries executed by your Rails application and improve its performance. It’s a best practice to use joins whenever possible to avoid N+1 queries.

The difference between :joins and :includes #

Both joins and includes can be used to solve the N+1 query problem in a Ruby on Rails application, but they have different use cases.

When you use joins, Rails will perform a SQL join to fetch the associated records, and all the data will be returned in a single query. This can be faster than using includes when you need to query a large amount of data, but it may result in duplicate records being returned if the join results in multiple records with the same ID.

On the other hand, when you use includes, Rails will use a SQL LEFT OUTER JOIN to fetch the associated records, and it will then load the data into memory using a separate query. This can result in slightly slower performance compared to joins, but it avoids the problem of duplicate records and allows you to eager load associated data to reduce the number of queries needed.

In general, you should use joins when you only need to query a small amount of data, and includes when you need to load a larger amount of data or when you want to optimize performance by reducing the number of queries. Ultimately, the best approach will depend on the specific needs of your application and the particular use case.

Use counter_cache #

counter_cache is a useful feature in Rails that can help avoid N+1 queries when you need to count the number of associated records. By using counter_cache, Rails will automatically keep a count of the associated records in a separate column on the parent record. This count will be updated automatically whenever a new associated record is added or deleted. Here’s how you can set up counter_cache on your models:

1. Add a new column to the parent model to store the count of associated records:

   add_column :parent_model, :associated_records_count, :integer, default: 0

2. Add a counter_cache option to the child model’s belongs_to association:

   class ChildModel < ApplicationRecord
       belongs_to :parent_model, counter_cache: true
   end

3. Whenever a new associated record is created or deleted, Rails will automatically update the count in the associated_records_count column on the parent record.

Now, when you need to count the number of associated records, you can simply access the associated_records_count column on the parent record. This will avoid the need to execute additional queries to count the associated records and can help prevent N+1 query problems. Note that counter_cache is only useful for situations where you need to count the number of associated records. If you need to load the associated records themselves, you’ll still need to use eager loading or joins to avoid N+1 queries.

Test and measure #

Testing and measuring the performance of your application is an essential step after making changes to your queries to ensure that you have resolved the N+1 queries and improved performance. This topic is worth a separate article, but here are some tips to help you with testing and measuring:

1. Use benchmarking tools: There are several benchmarking tools available that can help you to measure the performance of your application. Some popular benchmarking tools for Ruby on Rails applications include Apache Bench, Siege, and Wrk.
2. Set up performance metrics: Set up performance metrics for your application, such as response time, request rate, and throughput. You can use tools like New Relic or Datadog to monitor these metrics and alert you to any performance issues.
3. Test with realistic data: When testing the performance of your application, use realistic data that reflects the typical usage patterns of your users. This will help you to identify any performance issues that may arise under normal usage.
4. Compare before and after: Compare the performance of your application before and after making changes to your queries. This will help you to determine the impact of the changes and ensure that they have improved performance.
5. Iterate and optimize: If you find that the changes you made to your queries have not resolved the N+1 queries or improved performance as much as you expected, iterate and optimize your queries further. Testing and measuring your application regularly can help you to identify performance issues and optimize your application for better performance.