Skip to content
Velloreth
Technology 4 min read

The N+1 Query Problem: Finding and Fixing It

One query becomes N+1. Learn how it happens, how to detect it, and the eager loading, batching and join fixes.

By

The problem

One query for the list, plus one per item.

const posts = await db.post.findMany();          // 1 query

for (const post of posts) {
  post.author = await db.user.findById(post.authorId);   // N queries
}

N+1 queries instead of two.

The result is correct and the performance is poor.

Why it happens

An ORM loads a relation lazily by default.

const posts = await Post.findAll();
posts.forEach((post) => console.log(post.author.name));   // a query per post

The code looks like a property access, so the query is invisible.

The cost grows with the list size, which is why it is invisible in development.

The detection

Log the queries and count them.

GET /posts -> 1 query for the posts
           -> 50 queries for the authors
           -> 50 queries for the comments
= 101 queries

A request that issues hundreds of similar queries is the signature.

A development dataset is small, so the queries are fast and the problem is hidden.

A production dataset is large, so the same code is slow.

The fix: eager loading

const posts = await Post.findAll({ include: [{ model: User }] });

It fetches the relation in the same query, usually with a join.

The result: one or two queries.

The ORM syntax varies:

ORMSyntax
Sequelizeinclude
Prismainclude
TypeORMrelations
ActiveRecordincludes
Djangoselect_related
Eloquentwith

The fix: a batching loader

const authorLoader = new DataLoader(async (ids) => {
  const users = await db.user.findByIds(ids);
  return ids.map((id) => users.find((u) => u.id === id));
});

const posts = await db.post.findMany();
await Promise.all(posts.map(async (post) => {
  post.author = await authorLoader.load(post.authorId);
}));

It collects the keys in a tick and issues one query.

It is the fix when the relation is not declared, such as in a GraphQL resolver.

The fix: a join

SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.author_id;

One query, one result set.

The cost: a duplicated parent row per child, which is a larger payload.

A join is the right fix when the relation is one-to-one or many-to-one.

The fix: a second query

const posts = await db.post.findMany();
const authorIds = [...new Set(posts.map((p) => p.authorId))];
const authors = await db.user.findByIds(authorIds);
const byId = new Map(authors.map((a) => [a.id, a]));
posts.forEach((p) => { p.author = byId.get(p.authorId); });

Two queries, and the second fetches all the authors at once.

It avoids the join duplication, which is useful for a one-to-many.

The nested case

A relation of a relation is a second N+1.

const posts = await Post.findAll({ include: [User, Comment] });
// The comments' authors are still lazy

The fix: include the nested relation.

const posts = await Post.findAll({
  include: [{ model: User }, { model: Comment, include: [User] }],
});

A deep include can produce a large result, so it is a trade-off.

The pagination

A large list makes the problem worse.

Paginate the list, so N is bounded.

const posts = await Post.findAll({ limit: 20, offset: 0 });

A bounded N bounds the query count.

The measurement

Count the queries per request.

Set a budget, such as 10 queries for a page.

Fail the test if the count exceeds the budget.

expect(queryCount).toBeLessThan(10);

That test catches a new N+1 before it ships.

Common mistakes

A lazy relation in a loop. N+1.

A nested lazy relation. A second N+1.

No query count. The problem is invisible.

A development dataset. The problem is hidden.

An eager load of everything. A large result.

A deep include. A large payload.

No pagination. An unbounded N.

A batching loader with no cache. A repeated query.

A join for a one-to-many. A duplicated parent.

No test. A regression.

A quotable summary

The N+1 query problem is one query for a list plus one per item, and it happens because an ORM loads a relation lazily by default, so the code looks like a property access and the query is invisible. Eager loading fetches the relation in the same query, a batching loader collects the keys and issues one query, and a join or a second query is the fix at the SQL level. It is invisible in development and obvious in production, so count the queries per request and set a budget.

For the related topics, see database-indexes-explained and caching-strategies-guide. For the broader context, graphql-basics and mongodb-basics.

Frequently asked questions

What is the N+1 query problem?
When a query for a list of N items triggers one additional query per item, so N+1 queries run instead of two. It happens when a relation is loaded lazily, one item at a time. The result is correct and the performance is poor, and the cost grows with the list size.
Why does an ORM cause it?
Because an ORM loads a relation lazily by default, so accessing a related object on each item issues a query. The code looks like a property access, so the query is invisible. The fix is to declare the relation as eager, or to use a batching loader.
How do I detect it?
Log the queries and count them for one request, or use a tool that shows the query count. A request that issues hundreds of similar queries is the signature. A development environment with a small dataset hides it, because the queries are fast.
What is eager loading?
Fetching the related records in the same query, usually with a join or a second query that fetches all the related records at once. In an ORM it is a declaration such as include or with. It turns N+1 queries into one or two.
What is a batching loader?
A component that collects the keys requested in a tick and issues one query for all of them, then distributes the results. DataLoader is the common implementation. It is the fix when the relation is not declared, such as in a GraphQL resolver.
Is N+1 always a problem?
No. If the list is small and the query is fast, the cost is negligible. It becomes a problem when the list grows, because the query count grows with it. The fix is worth it when the list is unbounded or the query is slow.