> ## Content Index
> Fetch the complete content index at: https://debugly.dev/llms.txt
> Use this file to discover other available public pages before exploring further.

# Finding the N+1 Queries You Do Not Know You Have
- URL: https://debugly.dev/finding-n-plus-one-queries/
- Published: 2026-02-03T14:00:00.000Z
- Updated: 2026-10-17T05:58:58.000Z
- Description: The code reads beautifully and issues four thousand queries. Here is how to detect them automatically instead of discovering them in production.
- Author: Rohit Bhadani
- Tags: Databases, PostgreSQL, Performance, Debugging

## The quick version

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:

```javascript
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.

```python
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](https://debugly.dev/abstraction-cost-debugging/), 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](https://debugly.dev/shopify-liquid-n-plus-one-performance/). 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:

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

```

For SQLAlchemy:

```python
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

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

```

```javascript
// 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:

```javascript
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

```sql
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](https://debugly.dev/postgres-index-not-being-used-explain-analyze/). An N+1 has a perfect plan. The problem is the count.

## Fixing them

### Eager load the relationship

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

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

```

```javascript
// 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

```python
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.

```javascript
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](https://debugly.dev/bugs-ai-coding-agents-write/).

**A middleware that fails loudly in development** above a threshold:

```javascript
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.

---

Published on [debugly.dev](https://debugly.dev/) by [Rohit Bhadani](https://debugly.dev/about/) | Founder of [Krova Cloud](https://krova.cloud/?ref=debugly.dev)