The N+1 Query Problem: Why 100 Products Cost 101 Queries (and Why an Index Won't Save You)

You wrote one query. The database ran 101. And nothing in the code looks wrong.

This is the N+1 problem — the single most common reason a page that was instant with 10 rows falls over at 1,000. There is no slow query in the log, nothing flagged, nothing to blame. Just a loop that quietly turns one request into a hundred. Let's build it up from first principles, fix it in the query you already wrote, and then — the part most explanations skip — watch the exact same shape appear far away from any database.

---

The innocent loop

Your page shows 100 products. So you write one query:

SELECT * FROM products LIMIT 100;

One query. Then you render the list, and for each product you show its category:

products = db.query(Product).limit(100).all()   # 1 query

for product in products:
    print(product.name, product.category.name)   # ← one more query, every loop

That product.category looks like a field access. It isn't. The category lives in another table, and it wasn't loaded, so the ORM goes and fetches it — once per product. One hundred products, one hundred extra queries. Plus the original list query, that's 1 + N: the N+1 problem, named after exactly this shape.

The trap is that the code reads perfectly. There is no visible loop over the database, no obvious mistake. The extra hundred round-trips are hidden inside an attribute access.

---

The floor nobody starts with: a query's cost is the round-trip

To see why 101 queries is a disaster when each one is "fast," you have to know what one query actually costs.

Ask Postgres to find a single category by its primary key and it does that in about 0.18 ms. The lookup is genuinely fast. But getting the question to the database and the answer back — the network round-trip — costs around 2 ms. Serialize the query, cross the socket, wait, deserialize the result.

The lookup is not the bill. The trip is the bill.

So each of those 100 category fetches is a perfectly indexed, sub-millisecond lookup wrapped in a 2 ms round-trip. Multiply out: ~100 trips × ~2 ms ≈ 512 ms on that page, versus ~12 ms if you'd asked once. Same data. 40× slower, purely in trips.

---

Why an index won't save you

The instinct, when a page is slow, is to reach for an index. Run EXPLAIN and you'll be disappointed: the database is already using the primary-key index for every one of those category lookups. They are as fast as a single query can be.

That's the whole point, and it's worth stating plainly:

An index decides how fast ONE query finds its rows. N+1 decides how many queries you issue at all.

Those are two different problems. An index cannot change a number it doesn't control. 101 indexed queries are still 101 round-trips. You can index every column in the schema and this page stays slow, because the cost was never in the finding — it was in the trips.

---

The fix goes into the query you already wrote

You don't need caching, a queue, or a rewrite. The fix goes right into the query you already have: add a JOIN, and the category rides back in the same result, on one trip.

SELECT products.*, categories.name AS category_name
FROM products
JOIN categories ON categories.id = products.category_id
LIMIT 100;

One query. The category is already attached to each row — no per-item lookup, no loop firing behind your back. 101 queries collapse to 1. ~512 ms becomes ~12 ms.

And you rarely write that SQL by hand. Tell your ORM to eager-load the relationship and it writes the exact join for you — it's one line:

ORM	Eager-load in one line
SQLAlchemy	joinedload(...) / selectinload(...)
Django	select_related(...) / prefetch_related(...)
Rails	includes(...)
Prisma	include: {...}

# SQLAlchemy — one line turns 101 queries into 1
products = (
    db.query(Product)
      .options(joinedload(Product.category))
      .limit(100)
      .all()
)

---

It was never about databases

Here is the part worth carrying out of this article, because it's bigger than SQL: N+1 is not an ORM quirk. It's any loop that makes one round-trip per item.

The database is just where most people notice it first. The same shape shows up everywhere a loop talks to something across a boundary:

• One REST call per row in a list.
• One GraphQL resolver firing per node in a result.
• One object-store GET per key.
• One LLM call awaited at a time.

That last one is where this bites hardest in AI work today. Say you need to classify, embed, or summarize 500 items and you write the obvious loop:

# The N+1 shape, wearing an AI hat
results = []
for item in items:                     # 500 items
    results.append(await llm.complete(item))   # one round-trip each, awaited in turn

Every await waits for a full network round-trip to the model provider before the next one starts. At ~2 seconds each, 500 sequential calls is ~17 minutes of near-pure waiting — for work that could have gone out concurrently. The fix is the same idea as the JOIN: stop issuing one trip per item. Batch the inputs into a single request where the API supports it, or fan the calls out with asyncio.gather / a bounded worker pool and let them fly in parallel. Same disease, same cure — collapse N trips toward 1.

# Fan out instead of awaiting one at a time
results = await asyncio.gather(*(llm.complete(item) for item in items))

Whether the "trip" is a SQL round-trip, an HTTP call, or a token-generation request to a model, the lesson holds: it's the number of trips, not the speed of each, that's killing you.

---

Not a toy problem: Shopify

If this sounds like a beginner mistake, it isn't. Shopify hit exactly this shape in their GraphQL API. In their own words:

"if there were fifty authors, then it would make fifty-one round trips for all the data."

That's N+1, verbatim, at one of the largest commerce platforms on the internet. Their answer was to build graphql-batch — a library whose entire job is to coalesce those per-item trips into batched ones. When a company at that scale ships a library just to stop N+1, that tells you how common and how invisible it is.

---

The verdict

	Add an index	Add a JOIN / eager-load
What it changes	How fast ONE query finds rows	How MANY queries you issue
Round-trips for 100 products	Still 101	1
Fixes N+1?	No	Yes
Where it lives	Schema / migration	The query you already wrote

The real takeaway is a habit, not a keyword: log the round-trip count per request and assert on it in a test. If that number grows when your data grows, you have an N+1 — and it is completely invisible on a laptop seeded with 10 rows, which is precisely why it survives all the way to production.

Count your round trips, not your milliseconds.

Want the 90-second visual version? Watch the reel.