Fingerprinting and aggregation

Pipeline

raw SQL --tokenize--> tokens --normalize--> fingerprint text --blake2b(6 bytes)--> 12 hex id

querylens.tokenizer is a hand-written scanner (no regex over the whole statement), so it knows what is inside a literal:

Construct Handling
'it''s', E'a\'b', N'..', U&'..' one string token; a backslash-aware match is tried first, then the plain-quote form, so both MySQL and PostgreSQL 'C:\' work
$$ body $$, $tag$ body $tag$ one string token, semicolons inside are not statement ends
"Ident", `ident` quoted identifier token
-- line, /* nested /* */ */ comment token (dropped from fingerprints)
$1, %s, %(name)s, ? parameter token
::int, ->>, <=, || ... operator tokens

Normalisation rules

  1. Strings, numbers and parameters become ?. A sign is folded into the number only when it cannot be a binary operator (b = -5 yes, a - 1 no).
  2. Keywords are upper-cased, other words lower-cased, and quotes are removed from identifiers that are already safe ("shop_order" equals shop_order). Identifiers that need quoting keep their quotes ("Orders").
  3. IN (?, ?, ?), IN (?), VALUES (?, ?), (?, ?) and ARRAY[?, ?] collapse to (...) / [...], so batch size does not create a new shape.
  4. Whitespace and comments are canonicalised; trailing ; is removed.
  5. Transaction noise (BEGIN, COMMIT, SAVEPOINT, SET, ...) is dropped before aggregation.

Worked example:

SELECT * FROM "Orders" o WHERE o.id IN (1,2,3) AND x=-5 -- hi
  AND y::int > $1 LIMIT 10 OFFSET 20
=> SELECT * FROM "Orders" o WHERE o.id IN (...) AND x = ? AND y::int > ? LIMIT ? OFFSET ?

The literals are kept in order (('1','2','3','-5','$1','10','20')); the N+1 detector uses them to require that repeats differ only in their literals.

Aggregation

Per fingerprint, Stats keeps calls, total time, max, a DDSketch, row counts, first/last seen and the slowest example statement (with literals; for PostgreSQL logs DETAIL: parameters: are substituted back in token-aware fashion).

Quantiles: DDSketch

querylens.sketch.QuantileSketch maps a value v to bucket ceil(log_gamma(v)) with gamma = (1+a)/(1-a), a = 0.01. Any quantile is the representative value of the bucket that contains the requested rank, so the relative error is at most 1% regardless of the distribution, memory stays at a few hundred integers per fingerprint, and sketches merge by adding bucket counts. Exact zero durations live in a separate counter. The test suite compares p50/p90/p95/p99 against numpy.quantile on 30k log-normal samples.

Pre-aggregated inputs (pg_stat_statements) enter as weighted events: calls observations at the mean duration, with the exact max. Their p50/p95/p99 therefore equal the mean, which is the best a counter-only source allows.

N+1 detection

RunDetector streams events once. Events are grouped by request id (Django, or any --request-id-regex match), else by connection (conn:<pid> / MySQL Id). Within a group, a run is consecutive occurrences of one fingerprint whose gaps are at most --window-ms (default 500). A run is reported when it has at least --min-repeats (default 5) executions and at least two distinct literal tuples. The parent is picked from the five distinct fingerprints that preceded the run, preferring one that reads the table the foreign key points at (order_id favours FROM shop_order, not a count(*)). Estimated savings are run_time - run_time / n, i.e. everything except one batched query.

Limits: events with neither a group nor a timestamp are ignored; connection grouping can merge consecutive requests of a pooled connection (shorten --window-ms); runs of identical literals are repeated queries (a caching issue), not N+1.