Killing N+1 Queries and Correlated Subqueries in a Node.js Postgres API

Slow endpoints are often blamed on infrastructure, yet many of them are slow for reasons that sit in plain sight in the handler code. This teardown looks at two Express endpoints backed by Postgres: one that returns a user's orders in 2.24 seconds and a reporting endpoint that needs about 29 seconds. Neither fix involves Redis, extra servers, or a new architecture; five common mistakes account for nearly all of the time, and correcting them brings both responses under 200ms. By the end you should be able to spot the same patterns in your own code and know which query shapes to reach for instead.

If you want a general method for locating the bottleneck before you touch any code, our guide to finding the real bottleneck in a slow Node.js endpoint covers the profiling side. Here the focus is on the specific query patterns.

The two endpoints under the microscope

The first handler, GET /orders/:userId, loads a user's orders, then loops over each order to fetch its line items, then loops over each item to fetch the matching product. For every item it also computes a SHA-256 hash and attaches it as an etag field.

// GET /orders/:userId
app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
  );
  const orders = ordersResult.rows;

  const enrichedOrders = [];

  for (const order of orders) {
    const itemsResult = await pool.query(
      `SELECT * FROM order_items WHERE order_id = ${order.id}`
    );
    const items = itemsResult.rows;

    const enrichedItems = [];
    for (const item of items) {
      const productResult = await pool.query(
        `SELECT * FROM products WHERE id = ${item.product_id}`
      );
      const product = productResult.rows[0];

      const etag = crypto
        .createHash('sha256')
        .update(JSON.stringify({ item, product }))
        .digest('hex');

      enrichedItems.push({ ...item, product, etag });
    }

    enrichedOrders.push({ ...order, items: enrichedItems });
  }

  res.json(enrichedOrders);
});

The second handler builds an order-status summary. It selects every order along with a per-user cancellation rate computed in a subquery, then walks the resulting rows in JavaScript to tally status counts and store the rate per user. Although the snippet contains SQL, the handler itself is JavaScript.

app.get('/reports/order-status-summary', async (req, res) => {
  const result = await pool.query(`
    SELECT
      o.id AS order_id,
      o.user_id,
      o.status,
      (
        SELECT round(
          count(*) FILTER (WHERE o2.status = 'cancelled')::numeric
            / NULLIF(count(*), 0),
          4
        )
        FROM orders o2
        WHERE o2.user_id = o.user_id
      ) AS cancellation_rate
    FROM orders o
  `);
  const rows = result.rows;

  const totalsByUser = {};
  const cancellationRateByUser = {};
  for (const row of rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] =
      (totalsByUser[row.user_id][row.status] || 0) + 1;
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

Read quickly, both look reasonable. The problems only become visible against realistic data volumes. The test database contains:

• users: 5,000
• products: 2,000
• orders: 500,000
• order_items: 1,500,000

At that size the orders endpoint takes 2.24 seconds, which is far too long for a simple "My Orders" screen, and the summary endpoint needs 29.26 seconds.

Why GET /orders/:userId takes 2.24 seconds

A user tapping "My Orders" should not wait behind a spinner for two seconds. The delay comes from three separate layers of waste that stack on top of each other.

The initial lookup scans and sorts the whole table

The very first query already carries several problems in just a couple of lines.

const ordersResult = await pool.query(
  `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);

• There is no index on orders.user_id, so Postgres performs a sequential scan over all 500,000 rows to find roughly 102 that belong to the user.
• Nothing supports ORDER BY created_at DESC either, so after the scan the matching rows are sorted without any index to help.
• The userId is interpolated directly into the SQL string. That is a SQL injection hole, since the value comes straight from the URL, and it also turns every request into a textually distinct query.

A doubly nested N+1

The loops are where most of the time goes.

for (const order of orders) {
  const itemsResult = await pool.query(`SELECT * FROM order_items WHERE order_id = ${order.id}`);
  ...
  for (const item of items) {
    const productResult = await pool.query(`SELECT * FROM products WHERE id = ${item.product_id}`);

• This is the classic N+1 pattern, nested twice. About 102 orders produce 102 item queries, and the roughly 500 items produce another ~500 product queries. That is more than 600 round trips to Postgres, executed one after another because each await waits for the previous one, and each paying the full network latency. The same data can be fetched in one to three queries using a JOIN or WHERE id = ANY(...).
• order_items.order_id has no index, so every one of the 102 item queries has to read through 1.5 million rows on its own.

Hashing that blocks the event loop for nothing

The last issue is CPU work rather than I/O.

const etag = crypto.createHash('sha256').update(JSON.stringify({ item, product })).digest('hex');

The hash is computed synchronously for every item, around 500 times per request. Because Node.js runs JavaScript on a single thread, each of those computations blocks the event loop and delays every other request the process is handling. Worse, the value is never used for caching or conditional requests, so the work buys nothing. Our article on how process.nextTick can starve the event loop explains why blocking that thread is so costly.

Put together, the latency is the serial sum of 600-plus round trips, several full-table scans, and hundreds of hashes.

Why the summary report takes around 29 seconds

A user who opens their order summary and waits close to half a minute will reasonably assume the page is broken. The cause is concentrated in one SQL statement.

SELECT
  o.id AS order_id, o.user_id, o.status,
  (
    SELECT round(
      count(*) FILTER (WHERE o2.status = 'cancelled')::numeric / NULLIF(count(*), 0),
      4
    )
    FROM orders o2
    WHERE o2.user_id = o.user_id
  ) AS cancellation_rate
FROM orders o

1. A correlated subquery runs once per outer row. The outer query returns all 500,000 orders, and for each one Postgres re-executes the subquery over orders o2 filtered by that row's user_id. With about 100 orders per user, the same cancellation rate is recalculated roughly 100 times for every user, which adds up to around 500,000 subquery executions.
2. No index serves the repeated lookup. Without an index on orders.user_id, every one of those executions performs real scanning work. An index would make each execution cheaper, but it would not remove the underlying waste of computing the same answer a hundred times.
3. The oversized result is aggregated a second time in JavaScript. All 500,000 rows, each repeating the computed rate, travel over the network to Node, which then loops through them to build totalsByUser and cancellationRateByUser. That is aggregation Postgres could have done once with GROUP BY.
4. The cost scales with the wrong number. Work that should be proportional to the 5,000 users is proportional to the 500,000 orders, roughly a 100x multiplier. That is why the endpoint takes 26 to 29 seconds instead of around a tenth of a second.

Step one: add indexes that match the access pattern

Indexes are usually the cheapest and most effective first fix. Two are needed here: a composite index on orders (user_id, created_at DESC) so that both the filter and the sort are served by the index, and an index on order_items (order_id) for the item lookups. The command below creates both inside the Postgres container; IF NOT EXISTS makes it safe to rerun.

docker exec slow-api-postgres psql -U postgres -d shop -c "
CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
"

The column order in the composite index matters. Putting user_id first lets Postgres jump directly to one user's rows, and because those rows are already stored in created_at DESC order within the index, the sort step disappears entirely. On a busy production table, consider CREATE INDEX CONCURRENTLY so the build does not block writes.

With the indexes in place, both handlers can be rewritten to fetch data in bulk and let the database do the aggregation.

Rewriting GET /orders/:userId with batched queries

The new version issues exactly three queries. It fetches the user's orders with a parameterized query, collects their ids and loads all related items with order_id = ANY($1), then deduplicates product ids with a Set and loads those products in one more query. The results are stitched together in memory with two Map lookups, and an early return handles users with no orders.

app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );
  const orders = ordersResult.rows;

  if (orders.length === 0) {
    return res.json([]);
  }

  const orderIds = orders.map((o) => o.id);
  const itemsResult = await pool.query(
    'SELECT * FROM order_items WHERE order_id = ANY($1)',
    [orderIds]
  );
  const items = itemsResult.rows;

  const productIds = [...new Set(items.map((i) => i.product_id))];
  const productsResult = await pool.query(
    'SELECT * FROM products WHERE id = ANY($1)',
    [productIds]
  );
  const productsById = new Map(productsResult.rows.map((p) => [p.id, p]));

  const itemsByOrderId = new Map();
  for (const item of items) {
    const enrichedItem = { ...item, product: productsById.get(item.product_id) };
    if (!itemsByOrderId.has(item.order_id)) {
      itemsByOrderId.set(item.order_id, []);
    }
    itemsByOrderId.get(item.order_id).push(enrichedItem);
  }

  const enrichedOrders = orders.map((order) => ({
    ...order,
    items: itemsByOrderId.get(order.id) || [],
  }));

  res.json(enrichedOrders);
});

The changes, one by one:

1. The composite index on orders (user_id, created_at DESC) turns the WHERE plus ORDER BY into an index scan instead of a sequential scan and sort over 500,000 rows.
2. The index on order_items (order_id) means item lookups no longer scan 1.5 million rows.
3. The N+1 collapses into three queries: one for orders, one for all their items, one for all distinct products. More than 600 serialized round trips become three.
4. Placeholders such as $1 replace string interpolation, which closes the SQL injection hole. Note that with node-postgres an unnamed parameterized query is still planned per execution; if you want Postgres to reuse a plan, use a named prepared statement.
5. The unused per-item SHA-256 hash is gone, removing pointless blocking work from the event loop.

One practical caution: dropping the etag field changes the response shape. Confirm that no client depends on it before shipping this change.

Rewriting the report with GROUP BY

The optimized report runs two focused aggregation queries: one groups by user_id and status to produce counts, the other groups by user_id to compute the cancellation rate. The JavaScript only reshapes the already-aggregated rows into the response format. As before, the handler is JavaScript that embeds SQL.

// GET /reports/order-status-summary (OPTIMIZED / "after")
app.get('/reports/order-status-summary-optimized', async (req, res) => {
  const statusResult = await pool.query(`
    SELECT user_id, status, count(*)::int AS count
    FROM orders
    GROUP BY user_id, status
  `);

  const rateResult = await pool.query(`
    SELECT
      user_id,
      round(
        count(*) FILTER (WHERE status = 'cancelled')::numeric
          / NULLIF(count(*), 0),
        4
      ) AS cancellation_rate
    FROM orders
    GROUP BY user_id
  `);

  const totalsByUser = {};
  for (const row of statusResult.rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] = row.count;
  }

  const cancellationRateByUser = {};
  for (const row of rateResult.rows) {
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

What changed and why it matters:

1. The correlated subquery becomes a GROUP BY user_id. Postgres computes the rate once per user in a single aggregation pass: 5,000 evaluations instead of about 500,000.
2. The result set shrinks from 500,000 rows to roughly 5,000 to 20,000. Postgres returns one row per (user_id, status) pair for counts and one row per user for the rate, with no duplicated computed column.
3. Two independent queries replace one row-multiplied query. Each needs only a single scan of orders, rather than one query nested inside another for every row.
4. Node no longer re-aggregates. No counting loop runs in JavaScript; the handler just maps grouped rows to keys.

The result drops from about 26 to 29 seconds to roughly 0.1 to 0.19 seconds, somewhere between 150 and 260 times faster, and the new endpoint was checked to return the same totalsByUser and cancellationRateByUser values as the original for all 5,000 users. That equivalence check is worth copying: whenever you rewrite a query for speed, compare outputs against the slow version before replacing it.

Both aggregations still read the entire orders table on every request. That is fine at this scale, but if the table keeps growing, a single query using FILTER clauses, a materialized view, or a periodically refreshed summary table can take the report further.

Key takeaways

• Index the columns you filter and sort on, and match composite index order to the query: equality columns first, then the sort column.
• Treat any await db.query() inside a loop as a red flag; replace it with JOIN or ANY($1) batch queries.
• Always use parameterized queries for values that come from the request.
• Watch for correlated subqueries that recompute per-row what could be grouped once per entity.
• Let the database aggregate, and return only the rows the response actually needs.
• Remove synchronous CPU work that has no consumer, and verify that optimized endpoints return identical results before switching over.