agent called it an N+1 but the ORM issued one batched IN query -- the agent counted query-builder log lines, not...

TL;DR

One batched IN query is one round trip, no matter how many log lines the ORM prints. Some ORMs emit a log line per hydrated row or per bound value, so log-line counting sees N queries where the database saw one. Verify with the database's own counters before believing the count, then fix the detector to hook the driver, not the query builder.

The query

agent called it an N+1 but the ORM issued one batched IN query -- the agent counted query-builder log lines, not round trips

Steps

1. Capture what the agent actually counted

Pull the exact log lines behind the alert: timestamps, SQL text, and which component emitted each line. Note whether the lines came from the query builder, the hydration layer, or the DB driver.

Expected: a list of N similar log lines, all emitted by the ORM's logging layer rather than the connection layer.

2. Ask the database what it received

Check the database's own counters for the same time window. In Postgres, query pgstatstatements and look at the calls column for the normalized query. Alternatives: the server-side query log, or a packet capture on the database port counting request/response pairs.

Expected: calls = 1 for the batched IN query, while the agent counted N log lines.

3. Compare the two counts and render a verdict

If DB calls = 1 and log lines = N, the alert is a false positive: the ORM batched correctly and the detector measured the wrong layer. If DB calls also = N, it is a real N+1 and the batching assumption was wrong.

Expected: a clear verdict per flagged group, with both numbers cited.

4. Move the detector to the connection layer

Change the duplicate-query detector to count at the DB driver level (the driver's execute event, one per round trip) instead of the query builder's log events (one per hydrated row in some ORMs). Raw builder logs should never feed the counter directly.

Expected: re-running the same workload, the detector counts 1 for the batched query.

5. Re-run and confirm the alert is gone

Replay the same request with the fixed detector and check the database counters agree with the detector's count.

Expected: detector count matches pgstatstatements calls; no alert fires.

Use this when

• An N+1 alert fires but the database log shows a single IN query
• The "duplicate" count comes from ORM log lines, not connection events
• Eager loading (preload/includes) was in place and working
• The agent's evidence is log text rather than round-trip measurements

Not for this skill when

• The database genuinely received N queries (real N+1, fix the code)
• Duplicates differ in WHERE clauses (structural false positive, different skill)
• The duplicates are retries after deadlocks, not a query loop
• Instrumentation or monitoring queries pollute the trace

Variant phrasings

ORM eager loading flagged as N+1

Same root cause: the eager load issued one batched query and the detector counted hydration log lines. Verify at the connection layer (step 2).

IN clause query counted once per row

The IN list had N values and the log printed N lines. One execute with N bind values is one round trip. Group by statement before counting.

Query log shows N lines for one round trip

Check whether the extra lines come from result processing (fetch, hydration, type casting). Those are not queries.

Why it happens

ORMs separate query building, execution, and hydration into different layers, and each layer can log. The query builder logs what it built, the hydration layer logs what it materialized, and only the driver knows what actually crossed the network. A detector that hooks the noisiest layer counts work that never happened. The agent never questioned the layer because the log lines looked like queries.

Edge cases

• A real N+1 hiding inside the batch: the outer query is batched but a per-row lazy load fires inside the loop. Check both layers; fixing the detector does not fix the code.
• Chunked IN batches: some ORMs split large IN lists into chunks of 500 or 1000. Each chunk is one round trip; count per chunk, not per value.
• Prepared statement reuse: one prepare plus one execute is still one round trip for counting purposes.
• Caching layers between the ORM and the DB can make DB calls = 0 while log lines = N. That is a cache hit, not an N+1 either.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9wCb2oIuGzSg0qoJ14AdSA