How to Fix an N+1 Query

What an N+1 query is

You query a list (1 query), then loop over the results and query a related record for each row (N queries). For 100 orders that's 1 + 100 = 101 round-trips to the database - each one a network hop. The endpoint feels slow for no obvious reason because no single query is slow; there are just too many of them.

# BAD: N+1 - one query for orders, then one per order for its user
cur.execute("SELECT id, user_id, product FROM orders")
for order in cur.fetchall():
    cur.execute("SELECT name, email FROM users WHERE id = %s", (order[1],))  # +1 each
    user = cur.fetchone()

How to spot it

• A loop with a query inside it is the tell.
• Turn on SQL logging (or an APM trace) and you'll see the same query repeated with different ids, dozens or hundreds of times per request.
• In an ORM, it's a lazy relationship accessed inside a loop.

Fix 1: a single JOIN

Let the database do the join once:

cur.execute("""
    SELECT o.id, o.product, u.name, u.email
    FROM orders o
    JOIN users u ON u.id = o.user_id
""")
rows = cur.fetchall()   # 1 query, all the data

101 queries become 1.

Fix 2: batch with IN (when a JOIN doesn't fit)

Collect the ids and fetch them all at once, then stitch in memory:

user_ids = {o["user_id"] for o in orders}
cur.execute("SELECT id, name, email FROM users WHERE id = ANY(%s)", (list(user_ids),))
users = {u[0]: u for u in cur.fetchall()}     # 2 queries total

In an ORM

Use eager loading instead of lazy access in a loop:

• SQLAlchemy: selectinload() / joinedload()
• Django: select_related() (joins) / prefetch_related() (batched IN)

Why it matters

N+1 is the single most common reason a "simple" endpoint is slow. The fix is almost always free - one JOIN or one batched query - and it scales: the JOIN stays one query whether there are 10 rows or 10,000.