Killing an N+1 query under load

Prerequisites

• ▹Query logging, a debug bar, or an APM that reports queries-per-request.
• ▹A representative dataset — the bug is invisible on ten rows.
• ▹The ability to add an index to the relevant table.

Procedure

1. 1

   Measure — count the queries per request

   Before changing anything, quantify it. Log every query for the endpoint and count them. An N+1 shows up as the same query shape repeated once per row. You want the number, the shape, and the row that triggers it.

   DB::listen(function ($q) {
       logger()->debug('sql', ['q' => $q->sql, 'ms' => $q->time]);
   });
   // Hit the endpoint once; count identical query shapes in the log.

2. 2

   Eager-load relations to collapse N+1 into constant queries

   The classic N+1 is a loop that lazy-loads a relation per row. Eager-load it up front so the ORM fetches all related rows in one additional query, turning N+1 into 2 — regardless of row count.

   // Before: 1 + N queries (a query per order for its customer).
   $orders = Order::all();
   foreach ($orders as $o) { $o->customer->name; }
   
   // After: 2 queries total, whatever N is.
   $orders = Order::with('customer')->get();

3. 3

   Replace unbounded subqueries with a batched join or aggregate

   A correlated `EXISTS`/subquery evaluated per row, or a filter that scans a large child table per parent, is an N+1 in disguise. Rewrite it as a single grouped query that computes all rows at once — filter by group in one pass instead of once per group.

   -- Before: correlated subquery re-scanned per conversation.
   -- SELECT ... WHERE EXISTS (SELECT 1 FROM events e
   --   WHERE e.conversation_id = c.id AND e.sentiment = 'negative')
   
   -- After: aggregate once, join the result.
   SELECT c.*
   FROM conversations c
   JOIN (
       SELECT conversation_id
       FROM events
       WHERE sentiment = 'negative'
       GROUP BY conversation_id
   ) neg ON neg.conversation_id = c.id;

4. 4

   Add the covering index the scoped query needs

   A join or filter is only fast if the column it hits is indexed. Add a composite index whose leading columns match the query's filter — build it `CONCURRENTLY` on a hot table so it doesn't block writes — and confirm the planner uses it.

   CREATE INDEX CONCURRENTLY idx_events_conv_sentiment
       ON events (conversation_id, sentiment);
   -- Then confirm: EXPLAIN shows an index scan, not a seq scan.

5. 5

   Cache the expensive aggregate, tenant-safe with a TTL

   If the aggregate is still costly and tolerates slight staleness, cache it — with per-tenant keys and a TTL backstop, per the cache-invalidation runbook. Cache last, after the query itself is correct and indexed; caching a bad query just hides it.