db-query-patterns

db-query-patterns (M13)

How queries are written decides whether the indexes from M11 can even be used. This module inspects query shape in ORM source and raw SQL. Feeds the Performance & Scale score (axis performance, relational Query w18, shared with M3/M19).

What it checks

1. SELECT * — fetching all columns defeats covering indexes, bloats network/cache, and couples code to column order. Flag in hot paths.
2. Structural N+1 — a query inside a loop / per-row lazy relation load that should be a single join or batched IN. Static detection is directional (the loop's runtime cardinality is unknown) — it points at the structure, never claims a row count.
3. OFFSET pagination — LIMIT n OFFSET m degrades linearly with depth; deep pagination should use keyset/seek (WHERE id > $last ORDER BY id LIMIT n).
4. Non-SARGable predicates — wrapping the indexed column in a function (WHERE lower(email)=…, WHERE date(created_at)=…, leading-wildcard LIKE '%x', implicit type cast) so the index can't be used. Recommend an expression index or rewriting the predicate. For the leading-wildcard / LIKE '%x%' case a B-tree can never help — name the remedy: a pg_trgm GIN/GiST index on the column (or a dedicated search engine for heavy full-text search).

Score / axis

Feeds performance only (relational Query w18; Query category in document/time-series/graph profiles).

Tier-0 (static)

Grep ORM call sites and raw SQL for SELECT *, function-wrapped indexed columns, leading-wildcard LIKE, and OFFSET; detect query calls inside loops/.map/per-item relation access for N+1. All query-pattern findings are at most directional from source — confirming the actual plan/cost needs runtime (needs_api / Tier-2).

Tier-1/2 (verification query)

EXPLAIN (ANALYZE, BUFFERS) <the suspect query>;

Method explain_plan. A Seq Scan where an index exists confirms a non-SARGable predicate; the actual rows × loops confirms an N+1 amplification. Tier-2 pg_stat_statements (ordered by total_exec_time) surfaces the real hot queries — without it, hotness is directional.