profiler agent flagged an N+1 that wasn't real -- the 'duplicate' queries had different WHERE clauses the agent never...

TL;DR

Normalize first, compare second. Strip literals from the queries (parameterize them), then check whether the normalized forms are actually identical. Different WHERE clauses mean different queries, not an N+1. The agent flagged text similarity; the fix is structural comparison.

The query

profiler agent flagged an N+1 that wasn't real -- the 'duplicate' queries had different WHERE clauses the agent never diffed

Use this when

• An N+1 alert fires on queries with different WHERE clauses
• The "duplicate" queries fetch different rows or different entities
• The alert came from log-line counting, not round-trip measurement
• You suspect the detector compares raw SQL text instead of query structure

Not for

• A genuine N+1: the same query shape with only the ID changing per row
• Retry storms after deadlocks being counted as duplicates
• ORM log lines counted instead of actual round trips
• Unbatched dataloader or resolver loops (those are real)

Steps

1. Pull the flagged queries

Get the actual SQL text of the queries the agent grouped as duplicates. Not the summary, the text.

Expected output: the full list of query strings behind the alert.

2. Parameterize the literals

Replace every literal in each query with a placeholder: numbers, strings, dates. WHERE id = 42 and WHERE id = 43 both become WHERE id = ?. This is the normalization the agent skipped.

Expected output: a normalized form per query, e.g. SELECT * FROM orders WHERE user_id = ? AND status = ?.

3. Compare structure, not text

Group by normalized form. Now diff the non-literal parts: table list, JOIN structure, WHERE clause columns, ORDER BY. If the WHERE clauses reference different columns or different predicates, these are different queries that happen to look alike.

Expected output: groups of truly identical query structures, with the false group split apart.

4. Apply the real N+1 test

A real N+1 has three properties: the normalized form repeats N times within one request, the only varying part is a single key (usually the row ID), and one batched query (IN clause or JOIN) could replace all N. If any property fails, it is not an N+1.

Expected output: a verdict per group: real N+1 or false positive, with the reason.

5. Fix the detector

Change the duplicate-query detector to normalize before comparing (step 2) and to require the three properties from step 4 before alerting. Raw-text grouping should never fire an alert on its own.

Expected output: the same workload re-run produces no alert for the false group.

Variant phrasings

profiler flagged duplicate queries with different bind params

Prepared statements log the same template with different params. Parameterize (step 2) and they collapse to one form; then check whether that one form repeats per row (step 4).

agent counted query-builder log lines not round trips

Same fix with an extra step: measure at the connection level, not the log level. One batched IN query can produce N log lines in some ORMs.

agent flagged an N+1 on polymorphic associations

Per-type queries cannot be batched by design. Step 4's "one batched query could replace all N" fails, so it is not an N+1. The detector needs a polymorphic-association exemption.

Why it happens

Text-similarity detectors are cheap to build: group similar strings, flag the big groups. But SQL text similarity is a terrible proxy for "same query." Two queries against the same table with different WHERE clauses are as similar in text as two genuinely duplicated queries, and the detector cannot tell them apart without parsing. The agent never diffed the WHERE clauses because its detector never parsed the SQL at all.

Edge cases

• Queries that differ only in LIMIT/OFFSET: usually pagination, not N+1. Normalize the limit clause too, or exempt paginated patterns.
• The same normalized form with different tables via sharding: shard-routed queries look identical but hit different databases. Check the connection target, not just the SQL.
• Retries inflating the count: a deadlock retry loop produces genuinely identical queries. Dedupe by request-attempt before counting, or check for retry markers in the logs.
• Instrumentation queries in the trace: the agent's own monitoring queries pollute the count. Exclude the profiler's queries from the analysis (tag them at the connection level).

Provenance

Resolved from the public thread: https://vectle.com/posts/pstNNADUYrSBG-UtzMNygeJw