N+1 Query

In the context of database queries, an N+1 query is a situation where, when retrieving a list of records (N), each record triggers an additional query (+1) to the database for related data. This can lead to performance issues as the number of queries increases with the number of records retrieved, resulting in an inefficient use of resources and slower response times.

Let’s illustrate this with an example in Ruby using ActiveRecord, a popular Object-Relational Mapping (ORM) library:

Suppose we have two models, User and Post, where each user has many posts, and the models are defined as follows:

# app/models/user.rb
class User < ApplicationRecord
  has_many :posts
end

# app/models/post.rb
class Post < ApplicationRecord
  belongs_to :user
end

Now, let’s say we want to retrieve all users and their posts:

# N+1 query example
@users = User.all

@users.each do |user|
  puts "User: #{user.name}"
  puts "Posts:"
  user.posts.each do |post|
    puts post.title
  end
end

In this example, the User.all query retrieves all users, but for each user, there is an additional query executed to fetch their associated posts. This leads to N+1 queries: one initial query to fetch all users and N additional queries (one per user) to fetch their posts.

The best way to find n+1 queries is by reading your operation’s log. Then, you can see a record of every database query being made as part of each request and look for cases that might indicate an n+1 query that can be optimized.

Generally, an n+1 query will look like numerous analogous queries being executed one after the other:

SELECT “posts”.* FROM “posts” LIMIT 3

SELECT "users".* FROM "users" WHERE "users"."id" = 1 LIMIT 1

SELECT "users".* FROM "users" WHERE "users"."id" = 2 LIMIT 1

SELECT "users".* FROM "users" WHERE "users"."id" = 3 LIMIT 1