exception_stack_traces per-hash reads sort the whole group: idx_exceptions_project_hash stops short of recorded_at

[FrameAutomata]

Split out of #323 / #334, which fixed the same defect class on endpoints, tasks and ai_traces. Not included there because the query shape is genuinely different and I did not measure it — #323's own rule was to measure rather than assume, and adding an unmeasured index to a high-write table is exactly what that rule exists to prevent.

SQLite telemetry backend only.

The gap

exception_stack_traces has (0001_telemetry_tables.up.sql:46-47, 0007:20):

• idx_exceptions_project_recorded (project_id, recorded_at)
• idx_exceptions_project_hash (project_id, exception_hash)
• idx_exceptions_session (project_id, session_id)

No index covers (project_id, exception_hash, recorded_at).

Why it is NOT the same as #323

Worth being precise, because the obvious framing is wrong. The two per-hash queries in FindByHash (exception_stack_trace.repository.go:232, :254) carry no time bound at all:

SELECT ... FROM exception_stack_traces
WHERE project_id = :project_id AND exception_hash = :exception_hash
ORDER BY recorded_at DESC LIMIT :limit OFFSET :offset

So idx_exceptions_project_hash does restrict to the hash — this is not a full-project scan, and any claim that it is should be checked before being repeated. The cost is that the index gives no recorded_at ordering, so serving ORDER BY recorded_at DESC LIMIT 20 requires sorting every occurrence the hash has ever recorded. On a noisy issue with a long tail that is a large sort to fill one page. Widening the index would let SQLite walk it in order within the hash and stop after LIMIT rows.

GetHourlyTrendForHashes (:294) is the shape closer to #323 — exception_hash IN (...) plus a recorded_at range, one query per issues-list render — and would become a seek per hash rather than a window scan with a temp B-tree.

Proposed change

DROP INDEX IF EXISTS idx_exceptions_project_hash;
CREATE INDEX IF NOT EXISTS idx_exceptions_project_hash_recorded ON exception_stack_traces(project_id, exception_hash, recorded_at);

A widening, not an addition — (project_id, exception_hash) stays a leading prefix, so every query that used the old index stays covered.

Measure before merging

exception_stack_traces is one of the highest-write tables here, and in #334 the one genuinely new index roughly doubled insert cost on tasks (~120k → ~64k rows/s) while the widenings cost only ~4-9%. This is a widening, so it should land in the cheap band — but that is the prediction to verify, not to assume:

1. Seed with many hashes, each with a long occurrence history.
2. EXPLAIN QUERY PLAN on both FindByHash queries and GetHourlyTrendForHashes before/after — confirm the USE TEMP B-TREE FOR ORDER BY disappears on the paginated read.
3. Time a realistic issue-detail page and an issues-list render.
4. Measure exception ingest throughput with the old vs widened index.

If the widening costs materially more than the endpoints/ai_traces ones did, it is worth knowing why before shipping.

Note

0020's guard test (app/migrations/telemetry_group_index_test.go) covers only the three tables in #334. If this lands, add exception_stack_traces to that table-driven list.

[dusanstanojeviccs]

Minor, the index can be safely added, SQLite as a telemtry backend is not going to be supported in the future (DuckDB for self hosted envs).