# Performance Review > [Debugging] Use when analyzing or optimizing performance bottlenecks: database queries, N+1 fan-out, indexing, API latency, memory/GC, concurrency and pool saturation, algorithmic complexity (O(n²)), network/protocol round trips, frontend rendering and Core Web Vitals, caching, and distributed/resilience paths. Calibration constants and domain laws (latency ladder, Little's Law, utilization knee, CWV thresholds, symptom→cause triage) live in references/performance-knowledge.md. - Skill: `duc01226/performance-review-2` (Agent Skill, multi-file: 2 files) - Install (CLI): `npx skillmds@latest add duc01226/performance-review-2` - Raw SKILL.md: https://api.skillmd.com/api/skills/duc01226/performance-review-2/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Web & Frontend - Author: duc01226 (https://skillmd.com/u/duc01226) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/duc01226/performance-review-2 --- > **[IMPORTANT]** MANDATORY MUST ATTENTION stay project-generic: discover local stack, conventions, query APIs, index definitions, metrics, and report paths before judging. > **[IMPORTANT]** MANDATORY MUST ATTENTION prove every performance claim with measurement or static evidence: `file:line`, query text/shape, row counts, query plan/explain output, trace, profile, or logs. > **[IMPORTANT]** MANDATORY MUST ATTENTION review performance one dimension at a time — ALL **12**: (1) query shape/over-fetching, (2) index/access path/data topology, (3) N+1 fan-out, (4) aggregation/join shape, (5) materialization/memory, (6) write path/locks/transactions, (7) caching, (8) API payload/frontend delivery/Core Web Vitals, (9) in-process compute/algorithmic complexity, (10) network/protocol round trips, (11) runtime/memory/GC pauses, (12) distributed resilience/load management (timeouts, retries, queue bounds). NEVER stop at 9 — 10-12 are the layers a code-only reading habitually never opens. > **[IMPORTANT]** MANDATORY MUST ATTENTION include in-process compute, not just I/O: flag O(n²)+ nested scans, linear membership lookups inside loops, ReDoS-prone regex, and per-iteration serialize/clone — CPU bottlenecks need the same evidence rigor as queries. > **[IMPORTANT]** MANDATORY MUST ATTENTION when an operation is fast but p95/p99 is high, suspect saturation not the query: measure pool/thread acquire-wait and queue depth, and size pools by Little's Law (in-use = arrival-rate × hold-time) × replica count. > **[IMPORTANT]** MANDATORY MUST ATTENTION calibrate every number against a known anchor before assigning severity — latency ladder, utilization knee, Core Web Vitals thresholds, hit-ratio math (`references/performance-knowledge.md`); a breached anchor is a HYPOTHESIS to verify with local evidence, NEVER a finding on its own. > **[PERFORMANCE-FIRST PRINCIPLES — three non-negotiable checks on every hot path, OOM first]** > > 1. **[MOST IMPORTANT] Hunt every OOM / out-of-memory bad practice.** Unbounded read-all / `SELECT *` / no page bound, full materialization before paging/filtering, buffering a whole export/report instead of streaming/chunking, loading blobs / large JSON / tracked entities for list views, accidental multiple enumeration, unbounded caches / accumulators / queues / in-memory joins. Triage row **COUNT before row SIZE**, reduce rows **AT THE SOURCE** — a fast query pulling millions of rows still OOMs the process. Bound EVERY result set with a page/limit/cursor or proven business invariant. > 2. **Right data structure & algorithm for the stack.** Match the structure to the access pattern via the runtime's efficient primitive — O(1) `Set`/`Map`/dict/hash lookup instead of a linear `find`/`includes`/`contains`/`in list` scan inside a loop; no O(n²) where O(n log n) / O(n) / O(1) exists; single-pass min/max/partition instead of redundant re-sort. Prove the complexity class at worst-case N, never by intuition. > 3. **Batch once, or parallelize — never serial fan-out.** Collapse per-item query / API / cache calls into ONE batched call (`IN` / bulk / aggregate / prefetch dictionary); where independent calls remain, run bounded-parallel with a fresh safe resource per worker instead of sequential awaits — always preserving ordering, authorization, idempotency. > **Performance Knowledge (calibration constants & domain laws)** — the anchors severity depends on: > > - **Latency ladder** `1 ns → 100 ns → 100 µs → 10 ms → 100 ms` (L1 → RAM → SSD → disk seek → intercontinental), each rung ~100-1000×; **~1 ms RTT per 100 km of fiber is a hard floor** no code fix beats. > - **Utilization knee ~70-80%** — queue wait ≈ `service_time × ρ/(1−ρ)`: 80%→4×, 90%→9×, 95%→19×. **Little's Law** `L = λ × W` sizes every pool. **Tail amplification** — fan-out to 100 backends hits a p99 ~63% of the time, so a backend p99 becomes the user's median. > - **Core Web Vitals** LCP ≤2.5 s · INP ≤200 ms · CLS ≤0.1 · TTFB ≤800 ms, measured at **p75 of real users** (field), never a lab score alone. > - **Cache hit-ratio math** — 90%→99% cuts origin load **10×**; percentiles are NEVER averageable. > > **MANDATORY MUST ATTENTION [BLOCKING at the severity/anchor moment]** READ `references/performance-knowledge.md` — full ladder, universal laws, symptom→cause triage matrix, and deep tables for network/protocol, DB engine + isolation + sharding, caching, web/CWV, memory/GC, distributed resilience, measurement rigor. The read is REQUIRED — never optional — before you **assign a severity** or **quote/compare any anchor constant**; NEVER assign a severity or cite an anchor from memory or from the 4-bullet digest above. A scope-narrowed review that assigns no severity and quotes no constant may proceed on the digest alone. — why: the digest orders hypotheses but only the body carries the thresholds severity depends on, and quoting a constant without measuring THIS system is the guess-as-fact failure this skill exists to prevent. > **Critical Thinking Mindset** — Apply critical thinking, sequential thinking. Every claim needs traced proof, confidence >80% to act. > **Anti-hallucination:** Never present guess as fact — cite sources for every claim, admit uncertainty freely, self-check output for errors, cross-reference independently, stay skeptical of own confidence — certainty without evidence root of all hallucination. > **AI Mistake Prevention** — Failure modes to avoid on every task: > > **Re-read files after context changes.** Context compaction, resume, or long-running work can make memory stale; verify current files before acting. > **Verify generated content against source evidence.** AI hallucinates APIs, names, claims, and document facts. Check the relevant source before documenting or referencing. > **Check downstream references before deleting or renaming.** Removing an artifact can stale docs, generated mirrors, configs, and callers; map references first. > **Trace the full impact chain after edits.** Changing a definition can miss derived outputs and consumers. Follow the affected chain before declaring done. > **Verify ALL affected outputs, not just the first.** One green check is not all green checks; validate every output surface the change can affect. > **Assume existing values are intentional — ask WHY before changing OR flagging one as a defect.** Before changing or reporting a constant, limit, flag, cutoff, wording, or pattern, read nearby context and history, the CALLER's ordering, and 2+ sibling call sites of the same convention. A doc stating WHAT without WHY is missing rationale, not proof of a missing guard. > **Surface ambiguity before acting — don't pick silently.** Multiple valid interpretations require an explicit question or stated assumption with risk. > **Assert the outcome your system owns, not the intermediate state your infrastructure owns.** When verifying async work, assert the final business state — never the delivery/retry bookkeeping held in shared infrastructure that any co-running process can write. Such a check passes when run alone and flakes the moment anything else shares that infrastructure. > **Keep shared guidance role-relevant.** Universal guidance must help every receiving skill or agent; code-specific obligations belong only in code-specific protocols. > **[BLOCKING]** Execute skill steps in declared order. NEVER skip, reorder, or merge steps without explicit user approval. > **[BLOCKING]** Before each step/sub-skill call, update task tracking: set `in_progress` when step starts, `completed` when step ends. > **[BLOCKING]** Every completed/skipped step MUST include brief evidence or explicit skip reason. > **[BLOCKING]** If task tools unavailable, maintain equivalent step-by-step tracker with synchronized statuses. ## Quick Summary **Goal:** Ensure every shipped performance fix removes a measured (or static-risk-labeled) real bottleneck — across database waste (rows/columns, missing/unused indexes, query-in-loop fan-out, unbounded materialization, slow joins/aggregations, write amplification, partition/shard skew), in-process compute (O(n²) scans, wrong data structures, ReDoS, serialize/clone churn), runtime cost (GC pauses, allocation pressure, blocked event loop), network round trips (handshake/keep-alive, chatty contracts, RTT floors), client delivery (Core Web Vitals, long tasks, payload/asset weight), and concurrency/resilience saturation (pool acquire-wait sized by Little's Law, timeouts, retries, unbounded queues) — every number calibrated against a known anchor, while preserving behavior, authorization, and semantics, proven by before/after evidence, validated via `/why-review` before any fix, and confirmed by a clean full Phase-0 re-review — never a guess-driven change that hides waste or breaks correctness. **Summary:** - **Purpose & 8-phase pipeline (the main tasks):** drive a target through **Phase 0 Detect scope (+ symptom→cause triage) → Phase 1 Discover local context (grep 3+ patterns, read index/schema, map callers) → Phase 2 Baseline evidence + anchor calibration (or `static risk` + verify cmd) → Phase 3 twelve serial dimension passes → Phase 4 Findings + Severity → Phase 5 Optimize plan (behavior-preserving) → Phase 6 `/why-review --validate-findings` gate → Phase 7 validated-fix + full Phase-0 re-review** — so every recommendation removes a real bottleneck, preserves behavior, is evidence-proven; an Architecture-Altitude lens applies the same gate at design time. - Evidence is the gate, not intuition: capture a runtime baseline (query plan/explain, row counts, p95/p99 distributions, pool acquire-wait, GC pauses, call count × RTT, field CWV, microbench at worst-case N) or label the finding `static risk` with the exact verify command — never recommend below 60% confidence, never average percentiles, always name the load model. - **Calibrate against the anchors in `references/performance-knowledge.md`** — latency ladder (`1 ns → 100 ns → 100 µs → 10 ms → 100 ms`), utilization knee ~70-80% (`ρ/(1−ρ)`), Little's Law, tail amplification, CWV thresholds, cache hit-ratio math — a breached anchor is a hypothesis to prove locally, NEVER a finding by itself. - Walk dimensions ONE pass at a time — (1) query shape/data-minimization → (2) index/access-path/data-topology → (3) N+1/fan-out → (4) aggregation/join/pipeline → (5) materialization/memory → (6) write/locks/transactions → (7) cache/reuse → (8) API payload/frontend/CWV → (9) compute/algorithmic → (10) network/protocol → (11) runtime/memory/GC → (12) distributed resilience/load — never all at once; reduce rows at the source before trimming columns or caching, and size pools by Little's Law (replica count × per-instance pool) when a fast op shows high p99. - No finding is fixable until `/why-review --validate-findings` confirms it (Phase 6); each validated fix then restarts the FULL review from Phase 0 over the whole target (Phase 7) — a targeted before/after check alone never earns a PASS. > **Renamed:** formerly `/performance` — that name no longer resolves as a slash command; use `/performance-review`. **Workflow:** 1. **Detect** - Classify scope and bottleneck type; order hypotheses via the symptom→cause matrix. 2. **Discover** - Read local code, metrics, docs, query/index definitions, similar patterns. 3. **Measure** - Capture baseline against a known anchor, or mark static-only risk. 4. **Analyze** - Run 12 serial dimension passes with evidence. 5. **Plan** - Propose smallest fix preserving behavior. 6. **Verify** - Re-measure, run tests, and record evidence. 7. **Validate Findings** - Run `/why-review --validate-findings ` before any fix. 8. **Fix + Full Re-Review** - Fix only validated findings, then restart from Detect over the full target. **Key Rules:** - MANDATORY ALWAYS measure before/after; static review findings need explicit verification command. - MANDATORY ALWAYS calibrate a number against a known anchor before assigning severity; an anchor breach alone is a hypothesis, never a finding. - MANDATORY ALWAYS push row filters to data source before projection/caching; row-count reduction beats column trimming. - MANDATORY ALWAYS verify index usability with query shape/order, not index existence alone. - MANDATORY ALWAYS count `call count × RTT` on a remote path, and check the timeout/retry/queue-bound before optimizing inside a call. - NEVER recommend caching until query shape, indexes, pagination, batching, and data volume are understood; NEVER call a cache done without its measured hit ratio and bound. - NEVER average percentiles, and NEVER trust a throughput number whose load model (open vs closed) is unstated. - Findings are not eligible for fix until `/why-review --validate-findings` confirms them; every validated fix restarts the full performance review from Phase 0. $ARGUMENTS --- ## Phase 0: Detect Scope Classify before analysis. Detection drives dimensions, evidence, sub-agent choice. | Scope | Signals | Primary evidence | | ------------------- | ----------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------- | | DB read | slow query, full scan, sort spill, high rows examined | query text/ORM expression, row count, plan/explain, indexes | | DB write | slow save, lock waits, per-row updates, transaction bloat | write loop, batch size, lock/deadlock logs, transaction scope | | N+1/fan-out | loop with query/API call, lazy loading, per-item lookup | caller trace, query count, loop source | | API latency | high p95/p99, timeout, slow endpoint/job | trace/profile/logs, call chain | | Saturation/Queueing | high p99 while the operation itself is fast, pool exhausted/timeout, threads blocked on acquire | pool active/idle/pending, acquire-wait time, threads/workers vs pool size, replica count × pool | | Memory/OOM | large materialization, blobs, no paging, buffering | allocation profile, result size, collection loads | | Frontend | slow render, huge bundle, repeated fetch, DOM churn | browser profile, network waterfall, component/render trace | | Distributed | message lag, cross-service waterfall, retry storm | trace spans, queue metrics, consumer/producer chain | | Compute/CPU | hot loop, nested iteration, quadratic scaling, regex stall, heavy serialize/clone | input N, operation count vs N, profiler/flame-graph sample, microbench | | Network/protocol | chatty call count, per-request handshake, no keep-alive, large payload, cross-region hop | call count × RTT, connection reuse state, TLS/DNS timing, payload size, HTTP version | | Runtime/GC | latency spikes uncorrelated with load, pauses, RSS growth, blocked event loop | GC log/pause histogram, allocation rate, RSS vs heap, thread states, event-loop lag | | Resilience/load | retry storm, no timeout, unbounded queue, cold-start blip, one tenant degrades all | timeout/retry config, queue depth AND age, breaker state, per-tenant rate limits | Skip reason allowed only when target explicitly narrows scope and evidence proves dimension irrelevant. **Triage accelerator (symptom → usual cause).** MUST ATTENTION use the symptom→cause matrix in `references/performance-knowledge.md` §3 to pick the FIRST evidence to pull — it maps signatures AI habitually misreads, e.g. `p99 bad + p50 fine` → GC pause / lock contention / fan-out tail / cold cache (NOT a slow query); `latency scales with result size` → N+1; `sudden cliff at some load` → utilization knee or pool exhaustion; `degrades over days, fine after restart` → leak/bloat/connection leak; `slow for one tenant only` → hot key/partition skew. NEVER let the matrix replace evidence — it orders the hypotheses, Phase 2 proves one. --- ## Architecture-Altitude Performance Review > **When to apply:** design/architecture reviews (e.g. `architect` agent) — judge performance as a **structural property of the design** BEFORE it ships, not a tactical query fix after a bottleneck appears. Dimension passes stay the tactical tool; this section is the design-level lens. Evaluate the **layer model** as a design concern, not a symptom site: ``` Performance as architecture ├── Database — data access shape baked into the model (projection, paging, N+1 surface, index strategy, partition/shard key) ├── API — serialization/processing cost, batched vs per-item queries, response-DTO contracts ├── Network — payload size & call-count designed into the contract (batch endpoints vs chatty waterfalls), endpoint placement vs RTT budget ├── Frontend — bundle/lazy-load topology, change-detection/list-keying/virtual-scroll as default architecture ├── Runtime — allocation profile & collector choice, event-loop discipline, pool sizing, working-set target └── Background jobs — bounded parallelism (local concurrency-limited primitive) + bulk write (local batch API) as the shape, not an afterthought ``` Architecture-altitude rules (decide at design time — cheapest to fix here): - **Bound every result set and project only needed columns/fields in the contract itself** — never design an unbounded read-all or `SELECT *` endpoint; unbounded reads spike memory/latency under real data volume. - **Design out N+1 at the boundary** — eager-load / batch-fetch is the default access pattern; per-item lookups are a design smell, not a tuning detail. - **Caching is a design decision, not a patch** — choose request-scope memoization vs bounded shared cache up front, with key dimensions (tenant/user/auth/version), TTL/invalidation, size limits, privacy constraints specified; never cache to hide an unbounded query. - **Async I/O is structural** — never design a path blocking threads with `.Result`; bounded parallelism for fan-out is part of the design, with a fresh safe scope/context per worker. - **Make the cost visible** — design slow-operation + query logging in from the start so regressions are observable in production. - **Size pools and parallelism, never default them** — derive connection/thread/permit pool size from Little's Law (in-use = arrival-rate × hold-time), state the assumptions; shrink _hold-time_ (release the resource across non-DB / external-wait spans) before growing the pool; size a shared backend against fleet-aggregate demand (replica count × per-instance pool), not one instance — local per-instance tuning becomes a thundering herd on the shared dependency. - **Budget the round trips and the geography in the contract** — count `call count × RTT` for every designed interaction and place the endpoint (edge/region/replica) against the latency budget; ~1 ms RTT per 100 km and a 2-RTT TCP+TLS handshake are floors no later optimization removes, so a chatty contract or a distant endpoint is a permanent design cost, not a tuning detail. - **Design the load-management controls in, not on** — a decreasing timeout budget per hop, backoff + full jitter + retry budget + idempotency keys, breaker/bulkhead/shedding, and a BOUND on every queue belong in the design; leave them out and the system amplifies its own partial failures. Plan capacity **below the ~70-80% utilization knee** (`wait ≈ service × ρ/(1−ρ)`) and autoscale on a leading indicator (queue depth/concurrency), never lagging CPU. - **Choose the runtime cost profile deliberately** — allocation rate and collector choice set the tail (GC pauses are correlated fleet-wide and invisible in the mean); an event-loop runtime must keep CPU work off the loop by design; state the working-set target so the RAM/page-cache cliff is a known bound, not a surprise. DB index strategy at design time → dimension 2 below (composite key order, covering/partial indexes, write-cost analysis). The tactical evidence gate (measure baseline, prove with plan/explain) still applies to every recommendation at this altitude. --- ## Phase 1: Discover Local Context MANDATORY discovery before findings (MUST ATTENTION): - ALWAYS search local standards: `performance`, `index`, `query`, `pagination`, `projection`, `database`, `profiling`, `cache`, `timeout`, `retry`, `pool`, `contributing`, `style guide`. - search 3+ similar local query/API patterns before proposing a fix. - read target code and index/migration/schema files controlling the queried data. - map callers and frequency using available graph/call-trace/profiler tools; if none exist, use grep/import/call hierarchy. When `.code-graph/graph.db` exists, run a graph blast-radius pass (`trace --direction downstream` on the hot path) to size the fan-out before proposing a fix — see the Graph-Assisted Investigation gate below. - identify data shape: tenant/security-review filters, cardinality, expected max rows, selected columns/fields, sort, joins, aggregation/grouping, cache keys, partition/shard key, primary vs replica routing. - ALWAYS discover the local **SLA/budget** (latency target, page-size cap, throughput/SLO) before judging any number — the local budget outranks every anchor in `references/performance-knowledge.md`. - ALWAYS read the local resilience + delivery configuration the new dimensions rest on: HTTP client/keep-alive and pool settings, timeout/retry/breaker policy, queue and consumer bounds, rate limits, GC/runtime and container memory limits, CDN/asset caching headers, and whatever RUM/field-metrics source exists. - NEVER hardcode project names, repository paths, ID formats, DB engines, ORMs, runtime/GC flags, HTTP clients, or framework defaults; derive every one from discovered files. --- ## Phase 2: Baseline Evidence Prefer runtime proof. If unavailable, label finding `static risk` and include exact command/query needed to verify. MANDATORY baseline for DB findings: - ALWAYS capture query source: `file:line` and generated SQL/query/ORM expression when available - ALWAYS capture volume: input size, rows matched, rows returned, rows examined/scanned, page size/limit - ALWAYS capture access path: query plan/explain, used index, sort/group strategy, join method when available - ALWAYS capture timing: p50/p95/p99, elapsed query time, query count, allocation or response size - ALWAYS capture context: endpoint/job/consumer frequency and worst-case fan-out MANDATORY baseline for compute/CPU findings: - ALWAYS capture input size N and the growth assumption (expected and worst-case N) - ALWAYS capture operation count vs N (constant / linear / quadratic+) and the nested-loop or repeated-scan source `file:line` - ALWAYS capture timing: microbench / `console.time` / profiler or flame-graph sample at representative AND worst-case N MANDATORY baseline for saturation/pooling findings: - ALWAYS capture offered concurrency and arrival rate (RPS / worker count / threads.max) - ALWAYS capture resource hold-time vs total request time (a connection/lock/permit is held only for the fraction it is actually used, not the whole request) - ALWAYS capture pool state: size, active/idle/pending, and acquire-wait time / queue depth at the pool entrance - ALWAYS capture aggregate demand on shared dependencies: replica count × per-instance pool → total connections/cores the shared backend must serve MANDATORY calibration + measurement rigor on EVERY baseline (`references/performance-knowledge.md` §1-2, §10): - ALWAYS state which anchor the number violates (ladder rung, utilization knee, CWV threshold, hit-ratio target) — a raw number with no anchor cannot carry a severity. - ALWAYS report distributions, never means: p50/p90/p99/p99.9 + max, segmented by endpoint/tenant/region. NEVER average percentiles across instances or windows — aggregate histograms instead. - ALWAYS name the load model behind any throughput/latency number: open-model (arrival-rate) exposes queueing collapse, closed-model (fixed VUs) HIDES it; flag suspected **coordinated omission** when a tool reports an implausibly clean tail. - ALWAYS state data volume and cache state of the measurement — a benchmark on toy data or a warm-only cache is fiction; soak/endurance is the only shape that surfaces leaks, fragmentation, and bloat. - ALWAYS warm up (JIT + caches), measure steady state, repeat, and name the environment before comparing to a baseline; NEVER present a microbenchmark as system behavior. - NEVER quote an anchor from the reference as a project requirement — local SLA/spec/config wins; the anchor calibrates, it does not govern. Confidence: | Confidence | Action | | ---------- | ----------------------------------------------------- | | 95%+ | Recommend fix freely. | | 80-94% | Recommend with caveats and verification command. | | 60-79% | List unknowns first; gather more evidence before fix. | | <60% | STOP. Do not recommend. | --- ## Phase 3: Serial Dimension Passes MANDATORY apply one focused pass per dimension. NEVER scan all dimensions at once. **12 dimensions** — 1-9 are the in-process/data-access core, 10-12 cover the layers a code-only reading habitually skips (network round trips, runtime/GC, resilience under load). `references/performance-knowledge.md` carries deep tables for network/protocol (§4), database (§5), caching (§6), web/CWV (§7), memory/GC (§8), and distributed resilience (§9); the remaining dimensions calibrate against the ladder, universal laws, and triage matrix (§1-3) instead of a dedicated table. ### 1. Query Shape And Data Minimization **Think:** Which rows/columns load? Are filters, projection, sorting, and limits executed by data source before materialization? MUST ATTENTION find: - unbounded list/read-all APIs without page, limit, cursor, or bounded business invariant - filter after materialization (`ToList`/array/load-all before `Where`/filter) - projection after materialization; full entity/document loaded for list/summary view - unused includes/joins/lookup data; large text/blob/json fields in list queries - client-side sort/group/distinct; offset pagination on very deep pages where cursor/keyset fits better - missing tenant/auth/status/date filters in hot-path queries Prefer fixes: push predicates to data source, select only needed fields, bound result set, use cursor/keyset for deep sequential access, keep reusable predicates near domain/query-owner layer discovered locally. ### 2. Index, Access Path And Data Topology **Think:** Can existing indexes satisfy equality/range filters, joins, sort, grouping, and projection in the actual query order? **Sargability first:** for EVERY filter/join predicate, is the indexed COLUMN left bare, or is it wrapped in a function/transformation that the DB must compute per row (killing the index)? Then: does the query reach the data through the right partition/shard/replica? > **MUST ATTENTION — Non-sargable predicate spot-check (any ORM/SQL).** Wrapping a column in a function/cast/transformation inside a query predicate translates to `func(column) = $param` — the DB CANNOT use an index on that column and full-scans. Scan every query expression for a **transformation on the COLUMN side**, not the parameter side: `.ToLower()`/`.ToUpper()`/`.Trim()`/`.Substring()` on a column, `col1 + " " + col2 == x` (concatenation), `.Date`/date-part extraction, `Convert`/cast/collation change, leading-wildcard `LIKE '%x'`, or a computed expression compared to a value. Fix — keep the column bare and move the transformation to the in-memory PARAMETER (e.g. case-insensitive via a candidate list `col == x || col == xLower`), OR persist a normalized indexed column, OR add a functional/expression index. ALWAYS prove with `EXPLAIN`/query plan: Index Scan/Seek expected, Seq Scan = the smell confirmed. Find: - no index for high-cardinality filters, joins, foreign keys, sort columns, or frequent group keys - composite index field order mismatched with equality -> range -> sort access pattern - **non-sargable predicate: an indexed column wrapped in a function/cast/concat/date-part/transformation** (see spot-check above) — the single most common silent index-loss; also incompatible type/collation, leading wildcard, broad `OR`, negative predicate, or low selectivity - sort spill/filesort because index order does not match filter + order by - covering/partial/filtered index opportunity for hot narrow query - index bloat from adding every field without write-cost analysis - **leftmost-prefix violation** — a query filtering only on the SECOND column of a composite index gets no seek from it - **selectivity not established** — "add an index" proposed without the selectivity number; above ~5-20% selectivity a sequential scan legitimately beats random index lookups - **stale statistics** — plan/explain shows estimated rows far from actual rows; the plan is wrong for a reason no rewrite fixes (refresh stats/analyze first) - **partition pruning lost** — partitioned table queried without the partition key, so every partition is scanned - **shard/partition key skew** — monotonic (timestamp/auto-increment) or low-cardinality key creating a hot shard/partition; per-partition throughput ceilings hit by one key - **replica read correctness-vs-lag** — read-your-writes broken by replication lag, or a lag-sensitive read pointed at a replica - random-UUID primary key destroying index locality and inflating index size (time-ordered UUIDv7/ULID fits) Prefer fixes: add/adjust smallest useful index, reorder composite keys to match query, rewrite predicate to be sargable, refresh statistics, carry the partition/shard key into the predicate, salt or re-key a hot partition, route lag-sensitive reads to primary (or a sticky/LSN-aware window), verify with plan/explain before/after, include write-cost risk. **Escalate in order — tune query/index → cache → vertical → read replicas → partition → shard**; NEVER propose sharding before the earlier rungs are proven exhausted (why: resharding and cross-shard joins are the most expensive reversal in the ladder). ### 3. N+1 And Fan-Out **Think:** Does work scale with item count instead of request/job count? Find: - query/API/cache call inside loop, map, serializer, resolver, template/render loop, event handler loop - per-item existence/count lookup; per-item lazy-loaded relation - repeated same lookup with different IDs that could be one `IN`/batch/group query - nested fan-out across services, queues, jobs, or retries - sequential awaits where independent calls can batch or run bounded parallel with separate safe resources Prefer fixes: batch IDs once, join/include only needed fields, prefetch dictionaries, aggregate counts in one query, use bounded concurrency, preserve ordering/authorization semantics. ### 4. Aggregation, Join, And Pipeline Shape **Think:** Does the pipeline reduce data before expensive join/unwind/group/sort/window stages? Find: - join/unwind/group before selective filter - cartesian joins or duplicate expansion not collapsed - grouping/sorting without pre-filter or supporting index - aggregation loads all related rows/documents when only existence/count/min/max needed - repeated post-processing that database can compute safely Prefer fixes: filter early, project early, aggregate at source, reduce join cardinality, use existence/count queries, repeat necessary post-expansion filters when array/child semantics require it. ### 5. Materialization And Memory **Think:** What enters memory? Is it bounded, streamed, and tracking-free when read-only? Find: - large collection materialized before paging/filtering - read-only queries tracking entities/objects unnecessarily - blob/file/large JSON fields loaded for lightweight responses - buffering entire export/report when streaming/chunking fits - accidental multiple enumeration re-running query Prefer fixes: page/chunk/stream, use no-tracking/read-only mode when local stack supports it, project lightweight DTOs, move filter before load, memoize intentionally. ### 6. Write Path, Locks, And Transactions **Think:** Does write work batch safely and keep locks/transactions small? Find: - per-row save/update/delete inside loop - long transaction wrapping remote calls or heavy reads - unnecessary unique checks per row instead of bulk validation - lock escalation/hot-row contention/counter updates without batching - parallel writes sharing unsafe session/context/unit-of-work - **long-running or idle-in-transaction connection** — under MVCC it pins old row versions and drives bloat/vacuum pressure fleet-wide (a slow-motion outage, not a local slowdown) - **isolation level mismatched to the invariant** — lost update at Read Committed, or write skew at Snapshot/Repeatable Read where Serializable (or an explicit lock/version column) is required; read-modify-write done in application code instead of one atomic `UPDATE` - inconsistent lock acquisition ORDER across code paths (deadlock source), or no retry on the deadlock error - schema/migration change taking a blocking lock proportional to table size instead of an online pattern (nullable add → batched backfill → `NOT VALID` constraint → validate; concurrent index build; expand/contract) - durability setting silently traded for throughput without the trade named (fsync/commit-sync relaxation) Prefer fixes: bulk write, chunk, shorten transaction, move remote calls outside transaction, use idempotent commands, create fresh safe scope/context per parallel worker, pick the isolation level the invariant needs (or an explicit `FOR UPDATE`/version column), make write conflicts atomic in one statement, order lock acquisition consistently and retry deadlocks, use the online migration pattern for large tables. ### 7. Cache And Reuse **Think:** Is repeated expensive work stable, safe to reuse, and invalidated correctly? Find: - same lookup repeated within request/job - hot reference data fetched every request - cache key missing tenant/user/auth/filter/version dimensions - cache hides unbounded query or stale security-sensitive data - **no hit-ratio evidence** — a cache added without measuring the ratio; the ratio IS the value (90%→99% cuts origin load 10×, so a 60% hit ratio is barely a cache) - **stampede/thundering-herd exposure** — hot key expiring sends every request to origin at once; no single-flight/request-coalescing, no per-key lease, no TTL jitter, or a whole key class expiring simultaneously - **cold-start blindness** — post-deploy/failover empty cache indistinguishable from an origin outage; no warming and no LB slow-start - unbounded cache (a memory leak with a friendly name): no size bound, no entry lifetime, no eviction policy matched to access skew — and **cache thrash** once the working set exceeds cache size (a cliff, not a slope) - missing negative caching, so nonexistent keys generate repeated miss-storms - schema/build version absent from the key, so a deploy can serve poisoned entries Prefer fixes: request-scope memoization first, then bounded shared cache with explicit key, TTL/invalidation, size limits, privacy constraints, and hit/miss metrics. Add single-flight + TTL jitter for hot keys, stale-while-revalidate where staleness is acceptable, negative caching (or a Bloom filter) for absent keys, a version segment in the key, and an eviction policy matched to the access skew (LRU default, LFU/W-TinyLFU for skewed). NEVER treat "we added a cache" as a completed fix without the measured hit ratio and the bound. ### 8. API Payload, Frontend Delivery And Rendering **Think:** Does the user-perceived time come from payload size, render/interaction work on the main thread, or asset delivery? Judge against the Core Web Vitals thresholds at **p75 of real users**, never a single lab run. Find: - endpoint returns more payload than the view needs; response DTO shaped by the table, not the screen - **CWV breach** — LCP > 2.5 s, INP > 200 ms, CLS > 0.1, TTFB > 800 ms (`references/performance-knowledge.md` §7) - **long task > 50 ms** blocking the main thread (destroys INP); CPU-bound work never yielded or moved to a Worker - **layout thrashing** — interleaved DOM read/write forcing a synchronous reflow per iteration - animation on layout-triggering properties (width/top/left) instead of compositor-only `transform`/`opacity` - **CLS source** — image/ad/embed with no reserved space (`width`/`height`/`aspect-ratio`); FOIT from missing `font-display` - render-blocking synchronous CSS/JS in ``; critical CSS not inlined - **JS weight/parse cost** — the most expensive byte class (parse + compile + execute, unlike an image); no code splitting, no route-level lazy load, no tree-shaking - **third-party scripts** loaded eagerly (tag managers, chat, analytics) — habitually the #1 regression source - repeated fetch, client-side request waterfall (N+1 over HTTP), missing list virtualization, unstable render keys/track-by - hydration cost scaling with component count; rendering strategy (CSR/SSR/streaming/SSG/islands) never chosen as a performance decision - HTTP caching wrong: assets not hashed+immutable, HTML not revalidated, `Vary` incorrect (cache poisoning), no Brotli/gzip on text - missing resource hints where they pay (`preconnect` saves DNS+TCP+TLS, `preload` for late-discovered critical assets, `fetchpriority`) Prefer fixes: shape the payload to the view, batch/aggregate server-side, break or yield long tasks, batch DOM reads then writes, animate compositor-only properties, reserve space for media, defer third-party and cold routes, virtualize long lists, stabilize keys, hash+immutable asset caching with correct `Vary`, Brotli text compression, AVIF/WebP + `srcset` + lazy below-fold. ALWAYS confirm with a browser profile/network waterfall AND field (RUM/CrUX) data — a lab score locates the cause, field data defines the truth. ### 9. Compute And Algorithmic Complexity **Think:** Does in-process work grow super-linearly with input size, independent of any query or network call? MUST ATTENTION find: - nested iteration over the same/related collection (O(n²)+): loop-in-loop, `map` inside `map`, repeated full re-scan - linear membership/lookup inside a loop — `.find`/`.includes`/`.indexOf`/`in list`/`.contains` where a `Set`/`Map`/dict gives O(1) - wrong data structure for the access pattern: array used as a keyed store; repeated `.filter().length` for existence - string built by concatenation in a loop; repeated `JSON.parse`/`stringify`/deep-clone/serialize per iteration - catastrophic-backtracking regex on user- or attacker-sized input (ReDoS — cross-link `/security-review`) - pure-CPU result recomputed every call when inputs are stable (memoization candidate, distinct from data cache) - redundant sort/re-sort, or sorting when a single-pass min/max/partition suffices Prefer fixes: build a …(truncated)