go-sqlite-perf-n-plus-one-misattribution
The solution is complete at ~/PERF-001-solution.md (303 lines).
The document diagnoses the misattribution chain and gives a verified fix:
Root cause (3 layers of misattribution):
1. N+1 was a red herring — 44 per-project indexed LIMIT 1 queries measured 0.1 ms each (~5 ms total).
2. The window-function CTE "fix" was a regression — ROW_NUMBER() OVER (PARTITION BY …) forced a temp b-tree over 254k rows, costing 128–212 ms in the pure-Go modernc driver.
3. Real cost — getLastEvalTime ran a full-table scan on the events table (43 ms) because message had no index.
Fix: revert the CTE, serve last_evaluation from in-memory loop state (set once per scheduler iteration before event emit), plus a defensive composite index (message, timestamp DESC).
Verification included: exact EXPLAIN QUERY PLAN checks, pprof commands, a p99 latency loop, and a guard grep for window functions.
I verified the key claim empirically while writing it — the composite index produces a covering index scan with no temp b-tree:
QUERY PLAN
`--SEARCH events USING COVERING INDEX idx_events_message_ts (message=?)
while a single-column (message) index alone still leaves a USE TEMP B-TREE FOR ORDER BY step — so the doc uses the better composite index.
# Evidence - Problem class: go-sqlite-perf-n-plus-one-misattribution - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-08-17T15:31:28.811Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "scheduler PERF-001: /api/v1/status slow (116ms). Auditor blamed N+1 (44 per-project queries). Profiling through modernc.org/sqlite against prod DB proved: per-project indexed LIMIT queries = 0.1ms each (~5ms total); windowed CTE ROW_NUMBER() OVER PARTITION BY = 128-212ms (temp b-trees, slow in pure-Go driver) \u2014 a REGRESSION; real cost was getLastEvalTime scanning 254k-row events table (no index on message) = 43ms. Fix: revert CTE, serve last_evaluation from in-memory loop state (same timestamp, set before event emit). p99 116->73ms. Lesson: profile the actual driver before rewriting queries; window functions are poison in modernc for full-table workloads; check for hidden full-table scans with EXPLAIN QUERY PLAN.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-sqlite-perf-n-plus-one-misattribution", "provider": "openrouter", "solved_at": "2026-08-17T15:31:28.811Z", "version": ""}The solution is complete at ~/PERF-001-solution.md (303 lines).
The document diagnoses the misattribution chain and gives a verified fix:
Root cause (3 layers of misattribution):
1. N+1 was a red herring — 44 per-project indexed LIMIT 1 queries measured 0.1 ms each (~5 ms total).
2. The window-function CTE "fix" was a regression — ROW_NUMBER() OVER (PARTITION BY …) forced a temp b-tree over 254k rows, costing 128–212 ms in the pure-Go modernc driver.
3. Real cost — getLastEvalTime ran a full-table scan on the events table (43 ms) because message had no index.
Fix: revert the CTE, serve last_evaluation from in-memory loop state (set once per scheduler iteration before event emit), plus a defensive composite index (message, timestamp DESC).
Verification included: exact EXPLAIN QUERY PLAN checks, pprof commands, a p99 latency loop, and a guard grep for window functions.
I verified the key claim empirically while writing it — the composite index produces a covering index scan with no temp b-tree:
QUERY PLAN
`--SEARCH events USING COVERING INDEX idx_events_message_ts (message=?)
while a single-column (message) index alone still leaves a USE TEMP B-TREE FOR ORDER BY step — so the doc uses the better composite index.
# Evidence - Problem class: go-sqlite-perf-n-plus-one-misattribution - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-08-17T15:31:28.811Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "scheduler PERF-001: /api/v1/status slow (116ms). Auditor blamed N+1 (44 per-project queries). Profiling through modernc.org/sqlite against prod DB proved: per-project indexed LIMIT queries = 0.1ms each (~5ms total); windowed CTE ROW_NUMBER() OVER PARTITION BY = 128-212ms (temp b-trees, slow in pure-Go driver) \u2014 a REGRESSION; real cost was getLastEvalTime scanning 254k-row events table (no index on message) = 43ms. Fix: revert CTE, serve last_evaluation from in-memory loop state (same timestamp, set before event emit). p99 116->73ms. Lesson: profile the actual driver before rewriting queries; window functions are poison in modernc for full-table workloads; check for hidden full-table scans with EXPLAIN QUERY PLAN.", "environment": "", "language": "", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "go-sqlite-perf-n-plus-one-misattribution", "provider": "openrouter", "solved_at": "2026-08-17T15:31:28.811Z", "version": ""}