The N+1 query problem: why your API runs 101 queries
From the work/ 13 min read
The N+1 query problem in Postgres: spot it in the log and in pg_stat_statements, fix it with a JOIN, ANY or your ORM's with, and measure the result.
- Databases
- Node.js
- Performance
In this article (9)
The N+1 query problem happens when your API loads a list with one query and then runs another query for each item: 100 orders become 101 queries. You fix it by loading the relation in one go, with a JOIN, with WHERE order_id = ANY($1) or with your ORM's option to include relations.
Below we reproduce it on PostgreSQL with 1,000 sample orders, spot it in the log and in pg_stat_statements, and measure the fixes. All the code ran against PGlite (PostgreSQL 18.6 compiled to WebAssembly) over TCP, with the postgres.js driver and with Drizzle ORM.
What the N+1 query problem is
An orders screen needs every order with its line items. The most natural code fetches the list, then loops over the orders and fetches each one's items:
// GET /api/orders (postgres.js)
const orders = await sql`SELECT id, created_at, total FROM orders ORDER BY id DESC LIMIT 100`;
for (const o of orders) {
o.items = await sql`SELECT sku, qty, price FROM order_items WHERE order_id = ${o.id} ORDER BY id`;
}
return orders;
- The first query is the "1": it fetches 100 orders.
- The
forloop walks those 100 orders. - Every iteration sends another query with one order's id. Those are the "N".
- Result: 1 + 100 = 101 queries for a single request to your API.
Each query on its own is fast, so nothing looks broken. The cost is the round trips: your server sends a query, waits for the answer and only then sends the next one. With the database on the same machine you barely notice; with the database on another server, every trip adds network latency, and there are 101 of them in a row. N also grows with the data: if the page shows 500 orders tomorrow, that is 501 queries without touching a line of code.
The pattern is not limited to hand-written SQL. It shows up the same way with an ORM when you read a relation inside a loop, in GraphQL resolvers that load a field per item, and in templates that fetch data while rendering each row.
Scroll the figure sideways to see all of it
How to spot N+1 queries in the logs
N+1 hides from the usual logging setup. Per the PostgreSQL documentation, log_min_duration_statement logs statements that ran for at least the threshold you set, for example 250ms; -1, the default, logs nothing, and 0 logs every statement's duration. Since each N+1 query takes under a millisecond, a 250 ms threshold never catches one. To hunt it down, set it to 0 in development or in your test session. This is what the server wrote while the code above ran:
SET log_min_duration_statement = 0;
LOG: duration: 0.000 ms execute 84rutxrwln63: SELECT id, created_at, total FROM orders ORDER BY id DESC LIMIT 100
LOG: duration: 0.000 ms parse 84rutxrwln64: SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
LOG: duration: 1.000 ms bind 84rutxrwln64: SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
DETAIL: Parameters: $1 = '1000'
LOG: duration: 0.000 ms execute 84rutxrwln64: SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
DETAIL: Parameters: $1 = '1000'
LOG: duration: 0.000 ms bind 84rutxrwln64: SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
DETAIL: Parameters: $1 = '999'
LOG: duration: 0.000 ms execute 84rutxrwln64: SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
DETAIL: Parameters: $1 = '999'
SET log_min_duration_statement = 0lowers the threshold for your session only. Changing it takes a superuser or the matchingSETprivilege on that parameter.- The first
LOGline is the order list. parseshows up once: postgres.js prepares the statement and reuses it. With the extended query protocol, PostgreSQL logs the parse, bind and execute steps separately.- Then
bindandexecuterepeat with the same statement and a different$1: 1000, 999, 998, all the way down to 901. That repetition is the N+1 signature. - We cut the timestamp from each line. In this test PGlite rounded durations to 0.000 or 1.000 ms; a regular server shows fractions.
The log works for one request. To see the pattern across all traffic, pg_stat_statements merges statements that differ only in their values into one row and counts how many times each ran. You enable it with shared_preload_libraries = 'pg_stat_statements' in postgresql.conf, which needs a server restart, and CREATE EXTENSION pg_stat_statements in the database. On a managed database, check your provider's documentation for how to load it.
SELECT calls, rows, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;
calls | rows | query
-------+------+------------------------------------------------------------------------
100 | 300 | SELECT sku, qty, price FROM order_items WHERE order_id = $1 ORDER BY id
1 | 100 | SELECT id, created_at, total FROM orders ORDER BY id DESC LIMIT 100
callsis how many times the statement ran, androwshow many rows it returned in total.- The id is replaced with
$1, so the 100 per-order queries are one row withcalls = 100. - The tell: a statement with far more calls than the list query that triggers it, and few rows per call (3 here).
SELECT pg_stat_statements_reset()zeroes the counters. Run it before testing a specific route.
Scroll the figure sideways to see all of it
Fix 1: a JOIN
A JOIN fetches orders and items in one query. The catch: if you want 100 orders, limit the orders before joining. A LIMIT 100 on the joined result limits rows, not orders; in our test it returned 100 rows that covered only 33 orders.
const rows = await sql`
SELECT o.id, o.created_at, o.total, i.sku, i.qty, i.price
FROM (SELECT * FROM orders ORDER BY id DESC LIMIT 100) o
LEFT JOIN order_items i ON i.order_id = o.id
ORDER BY o.id DESC, i.id`;
const orders = new Map();
for (const r of rows) {
if (!orders.has(r.id)) orders.set(r.id, { id: r.id, created_at: r.created_at, total: r.total, items: [] });
if (r.sku !== null) orders.get(r.id).items.push({ sku: r.sku, qty: r.qty, price: r.price });
}
return [...orders.values()];
- The subquery picks the 100 most recent orders, so the
LIMITcounts orders. LEFT JOINadds their items. An order with no items still shows up, with the item columns set toNULL.- The result is flat: one row per item, with the order's fields repeated. The
Mapgroups them back by order. if (r.sku !== null)keeps an empty item out of orders that have none.
The price of a JOIN is that repetition. If an order has many columns and many items, the same order data travels many times, and with two one-to-many relations in the same JOIN the rows multiply against each other.
Fix 2: WHERE id = ANY or IN
The other way out is two queries: the list, then every item for those orders at once. That is 2 queries whether the page has 10 orders or 500.
const orders = await sql`SELECT id, created_at, total FROM orders ORDER BY id DESC LIMIT 100`;
const ids = orders.map((o) => o.id);
const items = await sql`
SELECT order_id, sku, qty, price FROM order_items
WHERE order_id = ANY(${ids}::bigint[]) ORDER BY id`;
const byOrder = Map.groupBy(items, (i) => i.order_id);
for (const o of orders) o.items = (byOrder.get(o.id) ?? []).map(({ order_id, ...i }) => i);
return orders;
- The first query is the same order list.
idscollects the 100 ids into an array.order_id = ANY($1)compares against every element of the array, which travels as a singlebigint[]parameter.Map.groupBy(Node.js 21 or later) groups the items by order.- The last loop gives each order its items, or an empty array, and drops
order_id, which was only there for grouping.
IN gives the same result. In postgres.js you write WHERE order_id IN ${sql(ids)}, which expands to IN ($1, $2, …, $100): one parameter per id. The PostgreSQL documentation defines IN with a list as = comparisons joined with OR, and = ANY as the comparison against an array. We prefer ANY for two reasons we checked: the query text stays the same with 10 ids or 500, so it remains one row in pg_stat_statements, and it never hits the parameter limit. With 70,000 ids, the IN version failed in postgres.js with MAX_PARAMETERS_EXCEEDED (the maximum is 65,534) and the ANY version answered.
Both fixes look up rows by order_items.order_id. Declaring the foreign key does not create an index on that column: without one, our EXPLAIN showed Seq Scan on order_items, and in the N+1 version that happens once per order. How to read the plan and pick the index is in Postgres EXPLAIN ANALYZE.
Your ORM's option to include relations
An ORM does not prevent N+1 on its own. With Drizzle ORM 0.45.4, the current stable release on npm, a loop running db.select() per order sent 101 queries, exactly like the hand-written SQL. What fixes it is the option to include relations. In Drizzle's relational queries you declare the relation once and ask for the items with with, as its relations v1 to v2 guide shows:
import { relations, desc } from "drizzle-orm";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import { orders, orderItems } from "./schema.js"; // each table's pgTable(...)
export const ordersRelations = relations(orders, ({ many }) => ({ items: many(orderItems) }));
export const orderItemsRelations = relations(orderItems, ({ one }) => ({
order: one(orders, { fields: [orderItems.orderId], references: [orders.id] }),
}));
const db = drizzle({
client: postgres(process.env.DATABASE_URL),
schema: { orders, orderItems, ordersRelations, orderItemsRelations },
});
const list = await db.query.orders.findMany({
orderBy: [desc(orders.id)],
limit: 100,
with: { items: { columns: { sku: true, qty: true, price: true } } },
});
relations(orders, …)says an order has many items (many).relations(orderItems, …)says each item belongs to one order (one), joined onorderIdandid.drizzle()takes the client and aschemawith tables and relations; without the relations,db.querydoes not know aboutitems.findManyasks for 100 orders andwithincludes their items, with three columns only.
With Drizzle's logger on, that findMany sent a single query: a LEFT JOIN LATERAL that builds each order's items with json_agg inside PostgreSQL, with the LIMIT applied to orders. In Drizzle 1.0, currently published as a release candidate (1.0.0-rc.5), relations are declared with defineRelations and passed as { relations }, and orderBy takes { id: "desc" }; with stays the same (current docs). We ran that too: one query.
In Prisma, the equivalent is include: { items: true }. Its relation queries documentation describes two strategies under relationLoadStrategy: join, one query with a LATERAL JOIN on PostgreSQL, and query, one query per table merged in the application; that option is still in Preview (relationJoins). Neither is N+1. We did not run Prisma for this article.
Scroll the figure sideways to see all of it
Measure before and after
Two numbers matter: how many queries the route runs and how long it takes. We timed the four versions against the same database, 30 runs each, and kept the median. First directly over localhost, then through a local proxy that adds 0.5 ms each way, to mimic a database on another server in the same network (a SELECT 1 went from 0.47 to 1.51 ms). All four returned the same 100 orders and 300 items. The timings are illustrative: they depend on the machine, and PGlite is not a production server.
| Version | Queries | Localhost | At ~1.5 ms per round trip |
|---|---|---|---|
| N+1 | 101 | 36.6 ms | 161.5 ms |
| JOIN | 1 | 1.8 ms | 3.5 ms |
= ANY($1) | 2 | 1.8 ms | 4.7 ms |
IN (...) | 2 | 1.8 ms | 4.3 ms |
How to read it: in the N+1 version, the time is almost all network wait. 101 trips at about 1.5 ms come to about 150 ms, close to the 161.5 measured. With one or two queries you pay the latency once or twice. That is why N+1 goes unnoticed on your laptop and shows up in production.
To measure it in your own project:
- Count queries per request. postgres.js takes a
debugfunction that is called for every query, and Drizzle alogger; add them up per request and warn when a route crosses a threshold. The full, tested counter is in the guide. - On a test database, run
SELECT pg_stat_statements_reset(), call the route once and readcalls. Before the change you will see 100; after, 1. - Time the route before and after, against the database where it runs in production, not only locally.
If what you do per item is not a read but slow work, such as sending an email or calling an external API, neither a JOIN nor ANY fixes it: move it out of the request with a Postgres job queue.
Common mistakes
- Trusting the slow query log. With a 250 ms threshold, the 100 fast queries never show up. Use
0in development, orpg_stat_statements. - Putting the
LIMITafter the JOIN. It limits rows, not orders: in the test, 100 rows were 33 orders. - Assuming the ORM prevents it. A loop that queries the relation sends N queries with or without an ORM; use
withorinclude. INwith thousands of ids. Each id is a parameter and the driver has a limit; an array withANYis a single parameter.- Forgetting the foreign key index. Without an index on
order_items.order_id, every lookup scans the table. - Hiding it with
Promise.all. Sending the 100 queries in parallel cuts the wait, but they are still 100 queries and they take more connections from the pool. - Measuring only locally. Without network latency, 101 queries look cheap.
Frequently asked questions
Which is better, a JOIN or two queries with ANY?
For a one-to-many relation, both work. A JOIN is a single round trip but repeats the parent's data on every row. Two queries with ANY repeat nothing and scale better when you load several relations at once. In our measurement they came out very close.
Why doesn't it show up in my slow query log?
Because no single query is slow. The problem is the count, not the duration of each one. log_min_duration_statement only logs what crosses the threshold, so you have to set it to 0 in development or look at calls in pg_stat_statements.
Does the N+1 problem happen with GraphQL too?
Yes, and often: each resolver for a related field can run its own query for every item in the list. The fix is the same idea: collect the ids in the request and load them with one ANY query, which is what batch-loading libraries do.
How many queries per request is too many?
There is no official number. The signal is not the total but that it grows with the size of the list. If 10 orders give you 11 queries and 100 give you 101, you have an N+1. A warning threshold, such as 10 per request, helps you find them.
Checklist
- You count queries per request in development and warn when a route crosses your threshold.
pg_stat_statementsis enabled and you review the statements with the mostcalls.- No route runs a query inside a loop over a list.
- Relations load with a JOIN, with
= ANY($1)or with the ORM'swith/include. - If you use a JOIN, the
LIMITapplies to the main table before joining. - Lists of ids travel as an array, not as thousands of parameters.
- The foreign key column has an index.
- You measured queries and time before and after, with the production database on another machine.
Once the queries are batched, the next step is making each one use its index: start by reading the plan with EXPLAIN ANALYZE.
Saw this on Instagram? Comment CONSULTAS on the Reel and I'll send you the guide.
Written by

Published November 19, 2026