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

📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram

Originally published on software-engineer-blog.com.

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.