# Pathrule Pattern: SQL Query Performance and Indexing (1.0.0) # ::pathrule:package:sql-query-performance ### [RULE] Read the execution plan before changing the query (path: /db/queries) A slow query is evidence about the optimizer's chosen plan under a particular dataset and parameter set. Rewriting syntax or adding an index without that evidence can improve one case while making the dominant workload worse. - Capture the plan with representative bind values and, where safe, actual execution metrics; do not infer cost from the SQL text alone. - Compare estimated rows with observed rows at each major node. Large divergence points to statistics, correlation, or parameter-distribution problems before it points to missing hardware. - Identify the first expensive expansion, repeated inner scan, sort, or spill rather than blaming the final node that merely accumulates upstream cost. - Save the before plan, runtime, returned row count, and dataset assumptions with the change so a later regression can compare the same evidence. See /db/migrations for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Create indexes for proven access paths (path: /db/migrations) Every index is a second data structure maintained on writes. An index that only resembles a WHERE clause may be unused because its leading columns, selectivity, expression, collation, or ordering do not match the plan. - Design from the complete access path: equality filters first where appropriate, then range or ordering needs, and include projected columns only when measured lookup avoidance justifies it. - Use partial or filtered indexes when a stable predicate selects the operational subset, and make the query express the same predicate the optimizer can prove. - Remove duplicate and prefix-redundant indexes only after checking constraints, foreign-key behavior, and less frequent workload paths that may rely on them. - Estimate added write amplification, build duration, lock behavior, storage, and rollback before applying the index to a large production table. See /db/queries for the adjacent decision or procedure that completes this constraint. --- ### [RULE] Bound every interactive query (path: /src/repositories) Application query shape is part of database capacity. Returning entire rows or using deep offsets transfers avoidable data, consumes memory, and forces the database to revisit or discard work the interface never displays. - Project the fields the caller uses instead of selecting whole records that include blobs, wide JSON, or sensitive columns. - Require a deterministic ORDER BY with a unique tie-breaker and a maximum page size at the repository boundary. - Use a cursor built from the ordered key tuple for growing lists; do not use unbounded OFFSET as the primary pagination strategy. - Avoid query-per-row loading by batching known relations or using a measured join, but do not create one giant fan-out query whose repeated rows dwarf the useful payload. See /db/queries for the adjacent decision or procedure that completes this constraint. --- ### [MEMORY] Representative cardinality matters more than fixture convenience (path: /db/queries) A query plan chosen for ten uniform fixture rows says little about a production table with millions of rows, one dominant tenant, sparse values, or highly correlated columns. The optimizer responds to distribution, not just schema. - Build anonymized or synthetic datasets that preserve row counts, value frequency, null fraction, relationship fan-out, and the heaviest authorized tenant. - Test common, worst credible, and boundary parameter values because a plan that is excellent for one selectivity can be poor for another. - Refresh or analyze statistics through the engine's supported workflow after representative loads and major distribution changes. - Keep performance fixtures separate from functional fixtures so developers can run fast correctness tests without erasing the dataset needed for plan work. See /src/repositories for the rule or workflow that puts this decision into practice. --- ### [MEMORY] Query budgets belong to user-visible operations (path: /src/repositories) Ten individually fast statements can still create a slow endpoint, and one low-latency query can return enough data to exhaust application memory. The useful budget follows the product operation across repository calls. - Record normalized query identity and duration under the request or job trace without logging secrets or raw personal values. - Set expectations for total query count, result rows, and database time on hot flows, then alert on trend or regression rather than one copied threshold. - Separate time waiting for a pool connection from time executing SQL so capacity exhaustion is not misdiagnosed as a bad plan. - Review background and export workloads too; they often evade interactive latency alerts while consuming the same connection and I/O capacity. See /db/queries for the rule or workflow that puts this decision into practice. --- ### [SKILL] investigate-sql-regression (path: /) --- name: investigate-sql-regression description: Investigate a production SQL performance regression without guessing from query text. --- # Investigate Sql Regression Run this procedure when the affected surface changes, before the result is promoted to production. Record evidence for every step instead of accepting a plausible-looking result. 1. Identify the normalized query, affected operation, parameter distribution, database wait, and the first deployment or data change correlated with the slowdown. 2. Reproduce against representative statistics and data volume, then capture plans for a common value and the worst credible value. 3. Compare estimated and observed rows, access paths, join order, sorts, spills, locks, and time waiting for a connection. 4. Apply one query, index, statistics, or data-shape change and rerun the same evidence set, including write cost and concurrent load. 5. Define rollback for the selected change and store before-and-after plans, timings, row counts, and assumptions beside the migration or query. ## Exit criteria The change is complete only when the expected behavior, failure behavior, and rollback path have all been exercised with representative data. Preserve the evidence with the change so the next operator can repeat the same checks.