perf(stats): GetStats, GetModelUsage and the LLM-metrics scanner still full-scan message blobs #719

New issue

Open

opened 2026-09-25 08:50:06 +02:00 by dries · 0 comments

dries commented

2026-09-25 08:50:06 +02:00

Owner

Follow-up from the perf report (second tier). Same root cause as the session-list fix: `message.data` is ~1.9 GB, 94% `summary.diffs`; `session` now carries denormalised `cost/tokens_input/tokens_output` (detected once as `DB.sessionTotals`).

`internal/db/stats.go:73-106` `GetStats`: `count(*) WHERE json_extract(role)='user'` (1.03 s) + token/cost `SUM` over assistant messages (1.10 s). Served by `/api/stats` and `metrics_stats.go:74` per OTel collection interval. Fix: `SELECT SUM(tokens_input), SUM(tokens_output), SUM(cost) FROM session` (2 ms, values match) when `sessionTotals`; count messages index-only.
`internal/db/stats.go:191-198` `GetNewAssistantMessages`, loop at `internal/server/metrics_llm.go:148-181`: `WHERE json_extract(role)='assistant' AND time_created > ?` = 1.0 s every 30 s for ~7 rows (no index on `message.time_created`). Fix: `SELECT id FROM session WHERE time_updated > ?` then `message` index range `(session_id, time_created > ?)`.
`internal/db/stats_models.go:15-30` `GetModelUsage`: `SELECT m.data FROM message` for every assistant message into Go then `json.Unmarshal` each — GBs of I/O for the metrics page with no `since`. Fix: aggregate from `session.model` + `session.tokens_*` when available, else extract the four fields in SQL.

Follow-up from the perf report (second tier). Same root cause as the session-list fix: \`message.data\` is ~1.9 GB, 94% \`summary.diffs\`; \`session\` now carries denormalised \`cost/tokens_input/tokens_output\` (detected once as \`DB.sessionTotals\`). 1. \`internal/db/stats.go:73-106\` \`GetStats\`: \`count(*) WHERE json_extract(role)='user'\` (1.03 s) + token/cost \`SUM\` over assistant messages (1.10 s). Served by \`/api/stats\` and \`metrics_stats.go:74\` per OTel collection interval. Fix: \`SELECT SUM(tokens_input), SUM(tokens_output), SUM(cost) FROM session\` (2 ms, values match) when \`sessionTotals\`; count messages index-only. 2. \`internal/db/stats.go:191-198\` \`GetNewAssistantMessages\`, loop at \`internal/server/metrics_llm.go:148-181\`: \`WHERE json_extract(role)='assistant' AND time_created > ?\` = 1.0 s every 30 s for ~7 rows (no index on \`message.time_created\`). Fix: \`SELECT id FROM session WHERE time_updated > ?\` then \`message\` index range \`(session_id, time_created > ?)\`. 3. \`internal/db/stats_models.go:15-30\` \`GetModelUsage\`: \`SELECT m.data FROM message\` for every assistant message into Go then \`json.Unmarshal\` each — GBs of I/O for the metrics page with no \`since\`. Fix: aggregate from \`session.model\` + \`session.tokens_*\` when available, else extract the four fields in SQL.