The N+1 Query Problem: How to Spot It and Fix It

The N+1 query problem is the performance bug that passes every test. On a laptop with ten rows the page loads instantly; in production with five hundred rows it takes seconds, and no single query looks slow in the logs. Interviewers like it because it checks whether you think about what your code sends to the database.

The problem it solves

You need a list of things and, for each one, its related rows: every job with its parts, every post with its author, every order with its items. The natural code fetches the list, then loops over it and asks for each item's children:

• 1 query for the list of jobs.
• N more queries, one per job, for that job's parts.

That is N + 1 statements where one or two would do. The trouble is not the work inside each query. Each one is tiny, probably a lookup on an index. The trouble is everything around it: a network round trip, parsing, planning, and handing back a result, paid again for every row. If a round trip costs one millisecond, 501 of them spend half a second waiting before any real work is counted.

It is easy to write by accident. Object-relational mappers load related objects lazily by default, so for job in jobs: job.parts looks like attribute access and quietly sends a query each time.

The intuition

The lesson's analogy is a plumber who drives to the merchant for one fitting, comes back, finds the next joint needs a washer, and drives out again. The parts are trivial; the driving is the whole day. The fix is a picking list written before leaving.

In SQL, the picking list takes one of two forms:

• A join. Ask for jobs and parts together in one statement: JOIN part ON part.job_id = job.id. One round trip returns every pair. Use a LEFT JOIN if jobs with no parts must still appear.
• A batched second query. Fetch the jobs, collect their ids, then fetch all their parts at once with WHERE job_id IN (...). Two round trips, whatever the size of N. This is what "eager loading" usually does, and it avoids the join repeating each job's columns on every part row.

Both turn a query count that grows with the data into a constant. That is the real point to make in an interview: the problem is not speed per query but the shape of the cost.

Watch it run

The animation opens on the pattern itself: you fetch a list, then loop over it and query once per item, and one query becomes N+1. First, one query for the list of jobs; that is the 1 in N+1. Three ids come back in one trip, and so far this is entirely reasonable. Then the loop starts. Job 1 needs its parts, so that is another round trip. Job 2 goes out on its own, trip three, for one row of parts. And job 3: four statements for three jobs, one for the list and one per row. Each query is trivial; the driving is the whole day, latency and planning every single time. With 500 jobs it is 501 trips, because the query count grows with the size of the result. The fix is a picking list written before you leave, JOIN part ON part.job_id = job.id, and every job and every part comes back together in a single trip. Or WHERE job_id IN (...) when a join is awkward. Either way, fetch the children in one go.

The code

SQLite's trace callback records every statement it runs, which makes the round trips countable. The lesson's schema, with an index on the foreign key:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE job  (id INTEGER PRIMARY KEY, addr TEXT);
CREATE TABLE part (job_id INTEGER, name TEXT);
CREATE INDEX part_job ON part(job_id);
INSERT INTO job VALUES (1, 'Mill Lane'), (2, 'Quay St'), (3, 'Fern Rd');
INSERT INTO part VALUES (1, 'elbow'), (2, 'washer'), (3, 'valve');
""")
sent = []
db.set_trace_callback(sent.append)                # every statement SQLite runs

def parts_n_plus_one(db):
    out = {}
    for job_id, addr in db.execute("SELECT id, addr FROM job").fetchall():      # 1
        rows = db.execute("SELECT name FROM part WHERE job_id = ?", (job_id,))  # +1 each
        out[addr] = sorted(name for (name,) in rows)
    return out

print(parts_n_plus_one(db))
# {'Mill Lane': ['elbow'], 'Quay St': ['washer'], 'Fern Rd': ['valve']}
print(len(sent))                                  # 4

The join, with LEFT JOIN so a job without parts still appears:

def parts_join(db):
    out = {}
    rows = db.execute("""SELECT job.addr, part.name
                         FROM job LEFT JOIN part ON part.job_id = job.id""")
    for addr, name in rows:
        out.setdefault(addr, [])
        if name is not None:
            out[addr].append(name)
    return {a: sorted(ns) for a, ns in out.items()}

sent.clear()
joined = parts_join(db)
print(len(sent))                                  # 1
print(joined == parts_n_plus_one(db))             # True

The batched version: one query for the list, one IN query for all the children. The placeholders are generated, never the values, so the ids are still bound parameters:

def parts_batched(db):
    jobs = db.execute("SELECT id, addr FROM job").fetchall()                   # 1
    ids = [job_id for job_id, _ in jobs]
    marks = ", ".join("?" * len(ids))
    by_job = {}
    for job_id, name in db.execute(
            f"SELECT job_id, name FROM part WHERE job_id IN ({marks})", ids):  # +1 total
        by_job.setdefault(job_id, []).append(name)
    return {addr: sorted(by_job.get(job_id, [])) for job_id, addr in jobs}

sent.clear()
parts_batched(db)
print(len(sent))                                  # 2

for jobs in (3, 50, 500):
    print(jobs, "jobs:", 1 + jobs, "trips vs 1 or 2")
# 3 jobs: 4 trips vs 1 or 2
# 50 jobs: 51 trips vs 1 or 2
# 500 jobs: 501 trips vs 1 or 2

All three against each other on 300 random databases, including jobs with no parts, checking both the results and the statement counts:

import random

random.seed(19)
ok = True
for _ in range(300):
    t = sqlite3.connect(":memory:")
    t.executescript("""CREATE TABLE job (id INTEGER PRIMARY KEY, addr TEXT);
                       CREATE TABLE part (job_id INTEGER, name TEXT);""")
    n = random.randint(1, 40)
    t.executemany("INSERT INTO job VALUES (?, ?)", [(i, f"addr{i}") for i in range(1, n + 1)])
    t.executemany("INSERT INTO part VALUES (?, ?)",
                  [(random.randint(1, n), f"p{k}") for k in range(random.randint(0, 80))])
    count = []
    t.set_trace_callback(count.append)
    slow = parts_n_plus_one(t)
    ok &= len(count) == n + 1
    count.clear()
    fast = parts_join(t)
    ok &= len(count) == 1 and fast == slow
    count.clear()
    ok &= parts_batched(t) == slow and len(count) == 2
print(ok)                                         # True

The complexity

• Round trips: N + 1 for the loop, 1 for the join, 2 for the batched version. This is the number that matters, because each trip carries fixed latency and planning cost.
• Rows returned: the same in all three. The fix does not fetch less data; it fetches it in fewer trips.
• The join's cost: each job's columns repeat on every part row. With wide parents and many children, the IN version moves fewer bytes.
• Index: without an index on part.job_id, each of the N queries scans the whole table, making the loop O(N·M) work as well as N trips.

Where it goes wrong

• Testing on tiny data. Three rows hide the problem completely. Count statements in tests, not milliseconds.
• Fixing one level and missing the next. Jobs to parts is batched, then parts to suppliers loops again. Each level of nesting can add its own N.
• Using an inner join and losing parents. Jobs without parts vanish. Use LEFT JOIN when the parent must appear.
• Building the IN list by pasting values into the SQL. Generate placeholders and bind the ids, or you have an injection bug.
• Huge IN lists. Databases cap the number of bound parameters or the statement size; chunk very long id lists into batches.

When it shows up in interviews

It comes up in backend and full-stack interviews as "this endpoint is slow, but every query is fast. Why?", or as a code review exercise with a loop over an ORM relation. Expect follow-ups on how you would detect it (query counts per request, slow page traces), whether a join or a batched query is better, and how caching interacts with it. It connects naturally to SQL joins and to indexes.

How to say it in an interview

"The code runs one query for the list and then one per row for the related data, so N rows cost N plus one round trips. Each query is cheap, but every trip pays network latency and planning, and the count grows with the data, so it looks fine in tests and slow in production. I would confirm it by counting queries per request. The fix is to fetch the children in one go: a join, a left join if parents without children must appear, or a second query with WHERE parent_id IN (...) over all the ids, which is what eager loading does. That makes it one or two round trips regardless of N. I would also make sure the foreign key is indexed."