Finding the N+1 Queries You Do Not Know You Have

The short answer

An N+1 is one query to fetch a list, then one more per item. Twenty items in your fixture, forty thousand in production.

The fastest detection is a query count assertion in your tests:

test("order list issues a bounded number of queries", async () => {
  const { queryCount } = await withQueryCounting(() => getOrderList(userId));
  expect(queryCount).toBeLessThan(5);
});

That turns an invisible performance bug into a failing test, which is the only version of this that scales.

Why they are invisible

Because the code looks correct.

for order in orders:
    print(order.customer.name)

That reads like a property access. It is a database round trip per iteration, and nothing at the call site says so.

This is the cost of an abstraction that hides an expensive operation, and ORMs are the canonical example. Lazy loading is a genuinely good feature that also makes the expensive thing look free.

The same trap exists outside ORMs. collection.products in Shopify Liquid looks like an array and is a fetch. A GraphQL resolver that fetches per field does the same thing.

Detection, in order of usefulness

Count queries in tests

The highest value technique, because it is automatic and it fails before merge.

Django has it built in:

def test_order_list_queries(self):
    with self.assertNumQueries(3):
        response = self.client.get("/orders/")

For SQLAlchemy:

from sqlalchemy import event

@contextmanager
def count_queries(engine):
    count = [0]
    def before(*args, **kwargs): count[0] += 1
    event.listen(engine, "before_cursor_execute", before)
    try:
        yield count
    finally:
        event.remove(engine, "before_cursor_execute", before)

def test_orders():
    with count_queries(engine) as n:
        get_orders(user_id)
    assert n[0] < 5

For Prisma or Drizzle in Node, hook the query event and count.

The assertion should be an upper bound, not an exact number, or it becomes noise that people update reflexively without reading.

Seed the test with realistic data. A query count assertion against a two row fixture will pass on an N+1. Twenty rows is enough to make the count obviously wrong.

Log queries in development

# Django
LOGGING = {"loggers": {"django.db.backends": {"level": "DEBUG"}}}

// Prisma
const prisma = new PrismaClient({ log: ["query"] });

Then load a page and count. Seeing forty identical queries scroll past is immediate and convincing in a way a number is not.

Add a request scoped summary so you do not have to count manually:

logger.info({ event: "request.completed", path, queryCount, dbTimeMs });

Log it on every request in development, and in production at a sample rate. A request issuing 300 queries is worth knowing about even when it is fast enough not to alert.

Find them in production

SELECT calls, mean_exec_time, total_exec_time, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

An N+1 shows up as a query with an enormous calls count and a tiny mean_exec_time. That combination is the signature: individually fast, collectively dominant.

Sorting by total_exec_time finds where your database time actually goes, which is frequently not the slow query everyone is worried about but a fast one running a hundred thousand times.

This is a different reading than looking for a slow query plan. An N+1 has a perfect plan. The problem is the count.

Fixing them

Eager load the relationship

# Django
orders = Order.objects.select_related("customer").prefetch_related("items")

# SQLAlchemy
stmt = select(Order).options(selectinload(Order.items), joinedload(Order.customer))

// Prisma
const orders = await prisma.order.findMany({
  include: { customer: true, items: true },
});

The distinction between join based and separate query strategies matters:

select_related and joinedload use a SQL join. Good for to-one relationships. Bad for to-many, because a join multiplies rows and you transfer the parent's columns once per child.

prefetch_related and selectinload issue a second query with WHERE id IN (...). Better for to-many. Two queries total regardless of the number of parents.

Getting this backwards produces a different performance problem: one enormous result set instead of many small queries. Both are slow, in different ways.

Batch manually where the ORM cannot help

customer_ids = {o.customer_id for o in orders}
customers = {c.id: c for c in Customer.objects.filter(id__in=customer_ids)}
for order in orders:
    customer = customers[order.customer_id]

Two queries, explicit, no lazy loading involved. More code and it is obvious what it does, which counts for something.

Watch the IN list size. Postgres handles thousands of values fine and tens of thousands starts to hurt the planner. Chunk above a few thousand.

DataLoader for GraphQL

GraphQL makes N+1 the default, because each resolver runs independently.

const customerLoader = new DataLoader(async (ids) => {
  const rows = await db.customers.findMany({ where: { id: { in: ids } } });
  const byId = new Map(rows.map(r => [r.id, r]));
  return ids.map(id => byId.get(id) ?? null);
});

DataLoader batches all the loads that happen within one tick of the event loop into a single query, and caches within the request.

Two things to get right: create the loader per request, not globally, or you leak one user's data into another's cache. And return results in the same order as the input keys, which is what the final map does. Returning them in database order is a subtle bug that assigns the wrong record to the wrong parent.

The cases people miss

Serialisers. The query looks fine and the serialiser accesses a relationship per object. The N+1 is in the presentation layer, not the query layer, so it does not appear in the code you were reviewing.

Templates. Same problem, in the view.

Permission checks. if user.can_view(order) inside a loop, where can_view hits the database. Extremely common and rarely noticed because it is not obviously data access.

Counts. len(order.items) on a lazy relationship loads every row to count them. Use an annotated count or .count() which issues SELECT COUNT(*).

Nested serialisation. Orders, each with items, each with a product, each with a category. That is an N+1 inside an N+1, and the multiplication is what turns a slow page into a timeout.

Prevention

Query count assertions on every list endpoint. The single most effective control. It is mechanical, requires no reviewer judgement, and it catches the pattern that generated code produces most reliably.

A middleware that fails loudly in development above a threshold:

if (process.env.NODE_ENV === "development" && queryCount > 20) {
  throw new Error(`${queryCount} queries for ${req.path}. Likely an N+1.`);
}

Aggressive, and it means nobody ships one without noticing.

Consider disabling lazy loading entirely. SQLAlchemy's lazy="raise" makes an unexpected lazy load throw rather than silently querying. It is strict and it converts an invisible performance bug into an explicit error at the point where you forgot to eager load.

Review reflex: any I/O inside a loop. Database, HTTP, filesystem. If you see a loop and a call, ask how many iterations there will be in production. The answer is almost never the number in the test fixture.