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 Velloreth
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:
| ORM | Syntax |
|---|---|
| Sequelize | include |
| Prisma | include |
| TypeORM | relations |
| ActiveRecord | includes |
| Django | select_related |
| Eloquent | with |
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.
Related reading
For the related topics, see database-indexes-explained and caching-strategies-guide. For the broader context, graphql-basics and mongodb-basics.