Skip to content

N+1 queries, joins and batching

N+1 is a count that grows with the list, not one slow query. Count statements per request, and load each relation level once.

Predict first

Before you read: predict

ExamHall's proctor dashboard lists the 40 attempts in one room and shows the question each candidate is on. The 40 candidates are on 25 different questions. Each refresh creates L = new DataLoader(questionsByIds), whose batch function sends one SELECT … WHERE id = ANY($1), then runs for (const a of await listAttempts()) a.question = await L.load(a.question_id);.

How many statements does one refresh send, the list query included?

Pick one

The 30-second version

N+1: one query loads N rows, then one query runs per row. Count statements per request; the count grows with N. Fix: a join (rows fan out per child; LIMIT counts rows) or one = ANY query per level. DataLoader batches loads made in one tick and caches each key; a loop awaiting each load sends one batch per new key.

N+1 is a count that grows with the list, not one slow query. Count statements per request, and load each relation level once.

How it works

Step 1 of 11

The shape. One statement loads N parent rows; the code then sends one statement per row for a relation: 1 + N in total.
In the kitchen
  • phone one order per car, for that car's filter → one statement per row (step 1)

Analogy: A car repair workshop and its parts supplier

A mechanic has a job sheet for 40 cars. Parts come from a motor factor across town, and every order arrives on one van run.

In the workshop In the system
phone one order per car, for that car's filter one statement per row (step 1)
count the van runs on the delivery notes, not the minutes count statements per request (step 3)
one order listing all 40 part numbers; one van brings a crate one = ANY query per level (step 8)
the factor's combined invoice: the car's details repeated on every part line join fan-out (step 5)
"send the first 20 invoice lines" stops halfway through a car's parts LIMIT on joined rows (step 7)
one van takes every order phoned in before it leaves, and a part already on the job's bench is not ordered again batch per tick, cached keys (step 10)
a mechanic who waits for each van before phoning the next order awaiting each load in a loop (step 11)
each job gets its own bench, cleared when the job closes one loader per request (step 11)

In real terms: the number of statements a request sends should depend on how many relation levels it loads, not on how many rows it shows.

Breaks down when: you expect to notice the waiting. A local database answers each statement so fast that N+1 feels instant in development. Wrong inference: "if it is not slow here, it is fine." The count still grows with N; only counting shows it.

Contrasting case: a job sheet with one car. It is 2 van runs either way; N+1 is about growth with the list, and N = 1 hides it.

Examples, run for real

4.1 ExamHall: the dashboard at two room sizes

EXH-1230: the proctor dashboard awaited each attempt's flags in a for…of loop. At 40 attempts that is 41 statements per refresh; with 60 proctors refreshing every 5 s, about 490 a second. MR !1430 loads the flags with one = ANY query and adds a statement-count test.

4.1 proctor dashboard refresh: statements sent (rows returned)
  loop (as shipped)            5 attempts: 6 (5 attempts)   40 attempts: 41 (40 attempts)
  one JOIN                     5 attempts: 1 (5 attempts)   40 attempts: 1 (40 attempts)
  = ANY($1) batch              5 attempts: 2 (5 attempts)   40 attempts: 2 (40 attempts)
  DataLoader, loads together   5 attempts: 2 (5 attempts)   40 attempts: 2 (40 attempts)
  all four render the same 40 rows: true
  test "refresh sends <= 3 statements" on loop (as shipped): FAIL (41 statements > budget 3)
  test "refresh sends <= 3 statements" on = ANY($1) batch: PASS (2)

Only the loop grows with the room (steps 1–2). The test fails on it and passes on the batch (step 3); it never reads a clock.

4.2 Ticketing: what a join returns

4.2 4 events with 3, 1, 0 and 2 price tiers
  JOIN       1 statement · 6 rows · events present 3 · each event's columns repeated: Opera×3 Derby×1 Ballet×2
  LEFT JOIN  1 statement · 7 rows · events present 4 · each event's columns repeated: Opera×3 Derby×1 Jazz×1 Ballet×2

One statement, but one row per tier: each event's columns repeat, and the inner join loses the event with no tiers (step 5).

4.3 Ticketing through an ORM

4.3 the same 4 events through Sequelize 6.37.8
  lazy: getTiers() in a loop   5 statements (1× events, 4× one event) → Opera 3, Derby 1, Jazz 0, Ballet 2
  include: [Tier]              1 statement  (1× JOIN) → Opera 3, Derby 1, Jazz 0, Ballet 2
  include, separate: true      2 statements (1× events, 1× IN (...) list) → Opera 3, Derby 1, Jazz 0, Ballet 2
  include + limit: 2           1 statement  (1× JOIN, LIMIT in a subquery on events) → Opera 3, Derby 1

The lazy getter is the N+1 (step 4). Both eager forms stay constant: one join, or one query per level (step 9). With a limit, Sequelize pages the events inside a subquery before joining (step 7). That rewrite is the ORM's: a LIMIT written on the join itself still counts joined rows.

Picture it

a grey-haired woman in a green shirt, among tall shelves of green and blue books, carries one large blue woven basket full of books. On a table beside her sits a stack of small, empty baskets: one trip with everything, instead of many small ones.
An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A shop's page runs SELECT p.id, r.stars FROM products p LEFT JOIN reviews r ON r.product_id = p.id ORDER BY p.id, r.id LIMIT 10. Products 1 to 12 have 4, 0, 3, 5, 2, 1, 3, 0, 2, 1, 4 and 2 reviews.

What does the page show?

Pick one

Question 2 of 5

An invoices page loads a page of invoices, then each invoice's line items with SELECT cents FROM lines WHERE invoice_id = $1, one invoice at a time.

Which change keeps the statements per page constant as the page size grows?

Pick one

Question 3 of 5

A thread shows each message's author through new DataLoader((ids) => db.query('SELECT id, name FROM users WHERE id = ANY($1)', [ids]).then((r) => r.rows)). The messages are by users 7, 3, 5 and 3, and every name shown is wrong.

Which is the bug?

Pick one

Question 4 of 5

An admin page lists each show with its episode count and cast size:

SELECT s.title, count(e.id) AS episodes, count(c.person) AS cast_size
FROM shows s LEFT JOIN episodes e ON e.show_id = s.id
             LEFT JOIN cast_members c ON c.show_id = s.id
GROUP BY s.id;

For Tidewater, the page shows 24 episodes and a cast of 24; its episode guide and its credits both list fewer.

Which is the bug?

Pick one

Question 5 of 5

A seat-map API shows an event's 5 seat rows; each row's resolver loads its 6 seats with seats.load(rowId), and all rows resolve together. Two goals: each request sends at most 2 statements, whatever the number of rows; and a hold that commits between two requests shows in the second.

Where does the seats loader come from?

Pick one

Go deeper

Pro tips
  1. Put a statement budget in the test, not a timer. Wrap the client's query, count per request, and assert a ceiling. When: any list endpoint or resolver (steps 3, 8).
  2. In production, read calls, not only time. With the pg_stat_statements extension loaded, a per-row query shows a calls count far above its parent's, with a tiny mean_exec_time. When: a trace shows many identical short spans in one request (step 3). Status: DOC (PostgreSQL 17, pg_stat_statements); not run here, because the container does not preload the extension.
Source
  • Prisma docs, Query optimization: https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance · fetched 2026-09-27. "The n+1 problem occurs when looping through query results and performing one additional query per result" (step 1); include "only results in two SQL queries"; relationLoadStrategy: "join" ensures "only one query is executed" (step 9); Prisma's own dataloader "batches findUnique() queries in the same tick". Not executed here. Valid as of 2026-09-27.
  • graphql/dataloader README: https://github.com/graphql/dataloader/blob/main/README.md · fetched 2026-09-27. It will "coalesce all individual loads which occur within a single frame of execution (a single tick of the event loop)"; values "must be the same length as the Array of keys" and correspond by index; it "provides a memoization cache for all loads which occur in a single request"; "DataLoader instances are created when a Request begins" (steps 10–11). Valid as of 2026-09-27.
  • PostgreSQL 17, LIMIT and OFFSET: https://www.postgresql.org/docs/17/queries-limit.html · fetched 2026-09-27. "no more than that many rows will be returned"; without ORDER BY "you will get an unpredictable subset of the query's rows" (step 7; every paged query in this lesson orders by a unique key). Valid as of 2026-09-27.
  • Sequelize v6, Eager Loading: https://sequelize.org/docs/v6/advanced-association-concepts/eager-loading/ · fetched 2026-09-27. "At the SQL level, this is a query with one or more joins." Valid as of 2026-09-27.

Related lessons

Checked against the tools and docs as of 2026-09-27.