Fixing a Common N+1 Query

ActiveRecord can be so seamless, that it can make something that’s relatively costly in the DB look cheap in terms of characters you need to type. One place where this crops up is in an N+1 Query.

Set the scene

Let’s say we have a common “Posts” with “Comments” structure of something. Here it’s a blog with comments on each post:

create_table "comments", force: :cascade do |t|
  t.text "body"
  t.bigint "post_id", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["post_id"], name: "index_comments_on_post_id"
end

create_table "posts", force: :cascade do |t|
  t.string "title"
  t.text "body"
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

add_foreign_key "comments", "posts"

By default, the index controller gets all posts. In the view then, we can provide an overall list of posts with a link to an individual page showing just that post alone.

def index
  @posts = Post.all
end

The index page template iterates through each post rendering its partial and providing a link to that post’s show page.

<div id="posts">
  <% @posts.each do |post| %>
    <%= render post %>
    <p>
      <%= link_to "Show this post", post %>
    </p>
  <% end %>
</div>

A common thing to add at this point is a count of comments made on each post. Perhaps this helps to show at a glance (read: on the index page) which posts are most discussed.

So in the partial, along with the post’s title, we’ll also add how many comments there are for that post (wrapped in brackets).

Title: <%= post.title %> [<%= pluralize(post.comments.count, 'comment') %>]

And when we load the page in the browser this is what shows up in the logs:

Processing by PostsController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering posts/index.html.erb within layouts/application
  Post Load (0.8ms)  SELECT "posts".* FROM "posts"
  ↳ app/views/posts/index.html.erb:6
  Comment Count (2.5ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 1]]
  ↳ app/views/posts/_post.html.erb:1
  Rendered posts/_post.html.erb (Duration: 14.7ms | Allocations: 8323)
  Comment Count (0.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 2]]
  ↳ app/views/posts/_post.html.erb:1
  Rendered posts/_post.html.erb (Duration: 0.8ms | Allocations: 755)
  Comment Count (0.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 3]]
  ↳ app/views/posts/_post.html.erb:1
  Rendered posts/_post.html.erb (Duration: 0.7ms | Allocations: 755)
  Comment Count (0.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 4]]
  ↳ app/views/posts/_post.html.erb:1
  Rendered posts/_post.html.erb (Duration: 0.6ms | Allocations: 755)
  Rendered posts/index.html.erb within layouts/application (Duration: 30.9ms | Allocations: 17119)
  Rendered layout layouts/application.html.erb (Duration: 31.6ms | Allocations: 17693)
Completed 200 OK in 41ms (Views: 23.0ms | ActiveRecord: 10.9ms | Allocations: 24275)

Notice what’s going on with the SQL:

-- Get list of posts
SELECT "posts".* FROM "posts";

-- Slightly edited:
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 1
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 2
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 3
SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = 4

There are 4 posts and we fire off a COUNT(*) for each one. If there are N posts in the database, we’ll do a total of N + 1 queries. This pattern of discovering some information that then dictates how many queries you must perform is easy to fall into.

Joining in the fun

Ultimately, we know that our Posts view is going to pull in information from a related table, Comments. We can prepare Rails with that information when we do the query initially.

In the Rails documentation, they give a solution to this particular N+1 pattern. They don’t call it out as such, instead focusing on various join operations that Rails supports.

We know that we were listing all posts and we want to ensure the new query also lists each post, even if it doesn’t have related comments. In SQL-terms that’s a LEFT OUTER JOIN. This query goes into a class method in app/models/post.rb:

def self.include_comment_counts
  Post
    .left_outer_joins(:comments)
    .select('posts.*, count(comments.*) as num_comments')
    .group('posts.id')
end

And then we just need a few more little tweaks to install that new query. The controller now uses our new method:

diff --git a/app/controllers/posts_controller.rb b/app/controllers/posts_controller.rb
index 14f9fc8..ff4d51a 100644
--- a/app/controllers/posts_controller.rb
+++ b/app/controllers/posts_controller.rb
@@ -3,7 +3,7 @@ class PostsController < ApplicationController

   # GET /posts
   def index
-    @posts = Post.all
+    @posts = Post.include_comment_counts
   end

   # GET /posts/1

And the view partial uses the new column on posts:

diff --git a/app/views/posts/_post.html.erb b/app/views/posts/_post.html.erb
index 126574e..4389158 100644
--- a/app/views/posts/_post.html.erb
+++ b/app/views/posts/_post.html.erb
@@ -1 +1 @@
-Title: <%= post.title %> [<%= pluralize(post.comments.count, 'comment') %>]
\ No newline at end of file
+Title: <%= post.title %> [<%= pluralize(post.num_comments, 'comment') %>]
\ No newline at end of file

Now, when we load the index page we get a single query for the posts along with comment counts all in one:

Processing by PostsController#index as HTML
  Rendering layout layouts/application.html.erb
  Rendering posts/index.html.erb within layouts/application
  Post Load (2.3ms)  SELECT posts.*, count(comments.*) as num_comments FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" GROUP BY "posts"."id"
  ↳ app/views/posts/index.html.erb:6
  Rendered posts/_post.html.erb (Duration: 0.4ms | Allocations: 235)
  Rendered posts/_post.html.erb (Duration: 0.0ms | Allocations: 30)
  Rendered posts/_post.html.erb (Duration: 0.0ms | Allocations: 30)
  Rendered posts/_post.html.erb (Duration: 0.0ms | Allocations: 30)
  Rendered posts/index.html.erb within layouts/application (Duration: 25.3ms | Allocations: 11290)
  Rendered layout layouts/application.html.erb (Duration: 26.2ms | Allocations: 11864)
Completed 200 OK in 36ms (Views: 18.7ms | ActiveRecord: 9.7ms | Allocations: 18526)

Zooming in on just the SQL:

         SELECT posts.*, count(comments.*) as num_comments
           FROM "posts"
LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
       GROUP BY "posts"."id"

And that’s it! We ran one query to populate the index instead of one query to find 4 posts and then 4 queries to count the number of comments on each.

If you’re looking for a team to help you discover the right thing to build and help you build it, get in touch.