agent's duplicate-query detector tripped on prepared statements with different bind params logged as separate queries

TL;DR

The database prepares once and executes with different bind values; the application log prints each execution with its params, which looks like many different queries. Strip the bind params to get the statement template, then group by template. One template executed once is not an N+1, even with N different param sets in the log.

The query

agent's duplicate-query detector tripped on prepared statements with different bind params logged as separate queries

Steps

1. Pull the flagged statements with their bind params

Get the full text of each flagged query including the logged parameter values. Keep the params; you need them for step 3.

Expected: N log entries that look like distinct queries but share an identical shape.

2. Normalize to the statement template

Replace every bind value with a placeholder. WHERE id = 42 and WHERE id = 43 both become WHERE id = ?. Group the N entries by this normalized form.

Expected: all N entries collapse to a single statement template.

3. Determine whether the template ran in a loop or once

This is the decisive check. Look at the execution pattern: did the template execute once with N bind values (single batched execute), or N times with one value each (per-row loop)? Check timestamps, the call site, and whether the code path iterates rows.

Expected: one of two verdicts. Single execute with N params means false positive. N executes in a loop means a real N+1 wearing a prepared-statement disguise.

4. Cross-check against the database's own grouping

pgstatstatements already groups by template (its query column is the parameterized form). Compare its calls count for the template against the detector's count.

Expected: the database's per-template call count agrees with your step 3 verdict.

5. Fix the detector to normalize before counting

Change the duplicate-query detector to parameterize statements before grouping, and to count executions per template per request. Template-level grouping should be the first pass, not an afterthought.

Expected: re-running the workload, the single-template group no longer alerts on its own.

Use this when

• The "duplicates" share one statement shape with different bind values
• The app log interpolates params before logging
• The detector compares raw log text instead of statement templates
• You suspect prepare-once/execute-many is being miscounted

Not for this skill when

• The normalized templates genuinely differ (different WHERE clauses)
• The template executes once per row in a loop (real N+1, batch it)
• The duplicates are retries after deadlocks
• Instrumentation queries pollute the trace

Variant phrasings

bind params logged as separate queries

Same fix: the logging layer interpolates params, so raw-text comparison always overcounts. Normalize first.

prepared statement flagged as duplicate

Prepare plus execute is the efficient pattern, not the problem. Count executes per template, not log lines.

same query template with different parameters alert

Group by template, then apply the loop-vs-single-execute test from step 3.

Why it happens

Logging happens at the application layer after parameter interpolation, so the log shows the query as the developer would read it: with values filled in. The database, meanwhile, sees one prepared template executed repeatedly. A detector built on log text inherits the interpolated view and cannot see the template underneath. The agent counted strings, not statements.

Edge cases

• Server-side vs client-side prepares: with client-side emulation, each execute may genuinely be a separate round trip with inline values. Know which mode your driver uses before declaring a false positive.
• A real N+1 can hide behind a template: N loop iterations of the same prepared statement are still N round trips. The template collapsing to one form does not by itself clear the alert; step 3 is mandatory.
• Param count limits: some databases cap bind params per statement, forcing chunked executes. Each chunk is one round trip; count chunks.
• Log sampling: if the app samples its query log, the detector may see a subset and misjudge frequency. Verify against pgstatstatements, which does not sample.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_uAUnQtC6OaTRpH9b-R99dg