Skip to content

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)
  1. What the N+1 query problem is
  2. How to spot N+1 queries in the logs
  3. Fix 1: a JOIN
  4. Fix 2: WHERE id = ANY or IN
  5. Your ORM's option to include relations
  6. Measure before and after
  7. Common mistakes
  8. Frequently asked questions
  9. Checklist

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;
  1. The first query is the "1": it fetches 100 orders.
  2. The for loop walks those 100 orders.
  3. Every iteration sends another query with one order's id. Those are the "N".
  4. 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.

one request to your API, counted in queriesN+1101 queries161.5 ms at ~1.5 ms a tripJOIN or ANY1 or 2 queriesthe order list+ all their itemsthe same response3.5 to 4.7 ms, same tripN grows with the list: 500 orders mean 501 querieseach query is fast; the trips add up100 orders, PGlite over TCP, median of 30; illustrative

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'
  1. SET log_min_duration_statement = 0 lowers the threshold for your session only. Changing it takes a superuser or the matching SET privilege on that parameter.
  2. The first LOG line is the order list.
  3. parse shows up once: postgres.js prepares the statement and reuses it. With the extended query protocol, PostgreSQL logs the parse, bind and execute steps separately.
  4. Then bind and execute repeat with the same statement and a different $1: 1000, 999, 998, all the way down to 901. That repetition is the N+1 signature.
  5. 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
  1. calls is how many times the statement ran, and rows how many rows it returned in total.
  2. The id is replaced with $1, so the 100 per-order queries are one row with calls = 100.
  3. The tell: a statement with far more calls than the list query that triggers it, and few rows per call (3 here).
  4. SELECT pg_stat_statements_reset() zeroes the counters. Run it before testing a specific route.
log_min_duration_statement = 0execute: SELECT … FROM orders … LIMIT 100execute: SELECT … WHERE order_id = $1 · $1 = '1000'execute: SELECT … WHERE order_id = $1 · $1 = '999'execute: SELECT … WHERE order_id = $1 · $1 = '998'… and 97 more, same statement, another $1each takes < 1 ms: a 250 ms threshold never sees itpg_stat_statementscallsrowsquery100300SELECT … FROM order_items WHERE order_id = $11100SELECT … FROM orders … LIMIT 100the signature: one statement, a hundred timesreal PostgreSQL 18.6 output (PGlite), timestamps cut

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()];
  1. The subquery picks the 100 most recent orders, so the LIMIT counts orders.
  2. LEFT JOIN adds their items. An order with no items still shows up, with the item columns set to NULL.
  3. The result is flat: one row per item, with the order's fields repeated. The Map groups them back by order.
  4. 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;
  1. The first query is the same order list.
  2. ids collects the 100 ids into an array.
  3. order_id = ANY($1) compares against every element of the array, which travels as a single bigint[] parameter.
  4. Map.groupBy (Node.js 21 or later) groups the items by order.
  5. 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 } } },
});
  1. relations(orders, …) says an order has many items (many).
  2. relations(orderItems, …) says each item belongs to one order (one), joined on orderId and id.
  3. drizzle() takes the client and a schema with tables and relations; without the relations, db.query does not know about items.
  4. findMany asks for 100 orders and with includes 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.

how to load 100 orders with their itemswayquerieswhenN+1 (loop)101never over a listJOIN1one relation; LIMIT before joining= ANY($1)2several relations or long listsIN (...)2short lists: one parameter per idwith / include1if you already use an ORM (Drizzle: 1)all five return the same: 100 orders, 300 itemsindex on order_items.order_id for all of themload the relation in one gocounted with pg_stat_statements and Drizzle's logger

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.

VersionQueriesLocalhostAt ~1.5 ms per round trip
N+110136.6 ms161.5 ms
JOIN11.8 ms3.5 ms
= ANY($1)21.8 ms4.7 ms
IN (...)21.8 ms4.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:

  1. Count queries per request. postgres.js takes a debug function that is called for every query, and Drizzle a logger; add them up per request and warn when a route crosses a threshold. The full, tested counter is in the guide.
  2. On a test database, run SELECT pg_stat_statements_reset(), call the route once and read calls. Before the change you will see 100; after, 1.
  3. 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 0 in development, or pg_stat_statements.
  • Putting the LIMIT after 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 with or include.
  • IN with thousands of ids. Each id is a parameter and the driver has a limit; an array with ANY is 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_statements is enabled and you review the statements with the most calls.
  • No route runs a query inside a loop over a list.
  • Relations load with a JOIN, with = ANY($1) or with the ORM's with / include.
  • If you use a JOIN, the LIMIT applies 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

Luis Morales

Luis Morales

Frontend and Shopify developer, EcomLabs

@luismoralesdev on Instagram

Published November 19, 2026

← Blog