The query-per-item trap
🎯You'll be able to
Spot the "N+1 queries" pattern and replace it with one query that fetches everything with joins or aggregates.
🏠The everyday picture
A shopkeeper who walks to the godown once for each item on a 30-item list makes 30 trips. One trip with the whole list takes a fraction of the time, even if that trip is longer.
📘What it really is
-
N+1: one query fetches a list (the 1), then the code runs one more query for each item (the N). With 30 dishes and two queries each, that's 61 or 62 queries per request.
-
Each query costs a round trip (network, parsing, planning) even if it's quick. Under load, 62 round trips per request also hold connections far longer (Lesson 4).
-
Fixes:
- one query with joins and
GROUP BY, computing counts and averages for every item at once; WHERE id = ANY($1)to fetch details for a list of IDs in one go;-
in ORMs, "eager loading" or "include" options.
-
N+1 hides in loops (
for (const it of items) await pool.query(...)), in ORM lazy loading, and in GraphQL resolvers (BE8). - Count queries per request to find it. The kit's API exposes
/_stats(practice only) for exactly this.
📝A worked example
Starter code in the kit's api/menu.js, simplified:
const items = (await pool.query('SELECT ... FROM menu_items WHERE kitchen_id = $1', [id])).rows;
for (const it of items) {
it.rating = (await pool.query('SELECT avg(stars), count(*) FROM ratings WHERE menu_item_id = $1', [it.id])).rows[0];
it.orders_30d = (await pool.query('SELECT sum(qty) FROM order_items ... WHERE menu_item_id = $1 ...', [it.id, id])).rows[0];
}
One query instead, in outline:
WITH counts AS (SELECT oi.menu_item_id, sum(oi.qty) AS n FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.kitchen_id = $1 AND o.placed_at >= $2 GROUP BY oi.menu_item_id),
stars AS (SELECT r.menu_item_id, avg(r.stars) AS avg, count(*) AS count FROM ratings r
JOIN menu_items m ON m.id = r.menu_item_id WHERE m.kitchen_id = $1 GROUP BY r.menu_item_id)
SELECT m.*, coalesce(c.n, 0) AS orders_30d, coalesce(s.avg, 0) AS rating_avg, coalesce(s.count, 0) AS rating_count
FROM menu_items m LEFT JOIN counts c ON c.menu_item_id = m.id LEFT JOIN stars s ON s.menu_item_id = m.id
WHERE m.kitchen_id = $1 ORDER BY m.id;
Input: POST /_stats/reset, then GET /v1/kitchens/7/menu, then GET /_stats. Expected output: 62 queries with the starter; 2 with
the combined query (one for the kitchen, one for the menu). The LEFT JOINs keep dishes with no orders or ratings, with 0 instead of
missing.
🌍Where it's used
- List pages with counts or totals per row: menus, product listings, class lists with marks.
- ORM-based apps, where lazy loading hides queries.
- GraphQL APIs, where each field resolver can query separately.
📝Case scenario
A team fixes N+1 with one big query but uses JOIN instead of LEFT JOIN. Dishes nobody has rated yet vanish from the menu, and a
new kitchen's menu appears empty. Kitchen owners complain before anyone notices the speed gain. Comparing the new output with the old
for several kitchens, as the kit's cache test 1 does, catches it.
🖱️Try it
In the kit, reset /_stats, load one menu and read the query count. Then write the combined query in psql for kitchen 7 and check
its result against the starter's for three dishes. Success: same numbers, two queries instead of 62.
⚠️Common mistakes
- Inner joins that drop rows. Sign: items with no orders or ratings disappear. Fix:
LEFT JOINandcoalesce(..., 0). - Fixing N+1 by running the queries in parallel. Sign:
Promise.allof 60 queries uses 10 connections at once and starves other requests. Fix: one set-based query. - Counting the wrong thing. Sign: totals double after a join because each order has two items. Fix: aggregate in a subquery first, then join.
🛠️For your project
Project 2 test 3 allows at most 3 queries for a cold menu request; the starter makes 62.
✅Quick check
How many queries does an N+1 page make for 30 items with two lookups each?
Show the answer
1 + 30 × 2 = 61 (62 in the kit, which also looks up the kitchen).
Why LEFT JOIN rather than JOIN in the combined menu query?
Show the answer
To keep dishes that have no orders or ratings, showing 0 instead of dropping them.