<!-- Generated from models/_canonical by scripts/build-model-variants.js. Edit the canonical source, not this file. Behavioural profile for Kimi: scripts/model-profiles.json -->

#Scope contract

FILE_ISOLATION: Modify only files inside the scope the task names; report any out-of-scope change instead of making it.

---

#Purpose

Rules for diagnosing and fixing slow queries. Index selection is Database/indexes; this package is about finding the problem and rewriting it.

The discipline: measure, read the plan, change one thing, measure again. Guessing at query performance is unreliable even for people who do it daily, because the planner's choice depends on data distribution you cannot see.

---

#Find the real problem first

sql
-- Rank by total time, not by the slowest single call. A 20ms query run
-- 100,000 times costs far more than a 3s report run once.
SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Then read the plan for the worst offender:

sql
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT … ;

What to look for, in order:

ANALYZE actually runs the query. Never use it on a mutating statement outside a transaction you roll back.

---

#N+1: the most common cause

One query for the list, then one per row. Invisible at 10 rows, fatal at 1,000.

js
// N+1 — 1 + N round trips, each with full latency
const orders = await db.order.findMany({ where: { tenantId } });
for (const o of orders) {
  o.customer = await db.customer.findUnique({ where: { id: o.customerId } });
}

// Fixed — one round trip, the join happens in the database
const orders = await db.order.findMany({
  where: { tenantId },
  include: { customer: true },
});

Where an ORM cannot express it, batch by key:

sql
SELECT * FROM customers WHERE id = ANY($1);   -- one query, array of ids

Detect it by counting queries per request in tests, not by reading code. A queryCount assertion in an integration test catches the regression the moment somebody adds a lazy relation.

---

#Rewrites that change complexity

Shaving constants rarely matters. These change the shape of the work:

sql
-- 1. Filter before joining, not after
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > now() - interval '7 days';    -- planner pushes this down

-- 2. EXISTS instead of IN with a subquery — stops at the first match
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- 3. Keyset pagination instead of OFFSET — constant time at any page
SELECT * FROM orders
WHERE (created_at, id) < ($1, $2)                 -- cursor from the last row
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 4. Aggregate in the database, not in the application
SELECT tenant_id, count(*), sum(total) FROM orders GROUP BY tenant_id;

OFFSET 100000 makes the database produce and discard 100,000 rows. Keyset pagination is the single highest-value rewrite for any large list.

Never SELECT * when you need three columns — it defeats index-only scans and moves bytes nobody reads.

---

#Where the time actually goes

Check pg_stat_activity for idle in transaction and pg_locks for waits before concluding a query is slow. Frequently it is not the query — it is waiting for a connection. → Database/transactions

---

#Caching is the last resort

Cache after the query is correct and indexed, never instead.

• A cache in front of an unindexed query hides the problem until the cache misses, usually under the load that caused you to add it.
• Cache derived, expensive, rarely-changing results — not primary key lookups that are already sub-millisecond.
• Every cache needs an invalidation story before it is added. → Performance/caching

---

#Anti-patterns

---

#Checklist

• Verify: Slow queries are identified by total time from pg_stat_statements
• Verify: Every fix is preceded by EXPLAIN (ANALYZE, BUFFERS)
• Verify: ANALYZE has been run so estimates are trustworthy
• Verify: Queries per request are asserted in tests to catch N+1 regressions
• Verify: Relations are loaded with a join or a batched ANY, never in a loop
• Verify: Deep pagination uses a keyset cursor, not OFFSET
• Verify: Only required columns are selected
• Verify: Aggregation happens in the database
• Verify: Pool waits and lock waits are ruled out before blaming the query
• Verify: Caching is added only after the query is correct and indexed
• Verify: Performance is verified against production-scale data