Opening the app takes 5 to 6 seconds because the dashboard's year-range analytics queries read the whole archive on every page load

[dqtz5vpvj9-create]

agentsview version

main at fea56e5 (also v0.41.1 and v0.42.0)

Install method

Built from source

OS / platform

Linux x86_64

Which agent and version

Not agent-specific; archive of about 1,650 sessions, 693k messages, 545k tool calls

Which model(s)

Not applicable

What happened, and what did you expect

What happened

Opening the app cold takes 5 to 6 seconds before the session list appears, and almost all of it is spent on the analytics dashboard that fills the content pane when no session is selected. Measured with a headless Chromium driving the real frontend against a local daemon:

cold open until the first .session-item is visible	ms
as shipped	6330, 5608, 6078
with /api/v1/analytics/* stubbed to return {}	954, 999, 953

The sidebar index request itself completes in about 0.8 s. What holds the page is the nine analytics requests the dashboard fires on mount with the default one-year range; with a warm page cache on a quiet daemon they take (analytics/<endpoint>, seconds): tools 8.0, velocity 1.6, summary 0.4 to 1.6, activity 0.5 to 1.4, skills 0.2 to 1.5. With the archive evicted from the page cache the same requests took 7.8, 1.6, 2.2, 1.4, 1.6 s on this archive after #1589, and 8.8, 2.2, 11.5, 2.0, 18.0 s on the same sources with the previous storage layout. Nine concurrent heavy queries also occupy the reader pool, so everything else the page needs queues behind them.

analytics/tools is the clearest case. GetAnalyticsTools selects every session id matching the non-date filters, then for every chunk runs

SELECT tc.session_id, tc.category, TRIM(COALESCE(tc.tool_name, '')), COUNT(*), COALESCE(m.timestamp, '')
FROM tool_calls tc LEFT JOIN messages m ON m.session_id = tc.session_id AND m.id = tc.message_id
WHERE tc.session_id IN (...)
GROUP BY tc.session_id, tc.category, TRIM(COALESCE(tc.tool_name, '')), COALESCE(m.timestamp, '')

The date range is applied afterwards in Go (ResolveSkillRowTime), so every page load reads all 545k tool_calls rows, joins each to its message, and groups by per-message timestamp before discarding what falls outside the range. analytics/skills has the same shape. These are O(corpus) per page view, exactly the pattern #1584 removed from the embedding refresh.

Expected behavior

Two independent fixes, either of which helps on its own:

1. The session list should not wait for the dashboard. Render the sidebar as soon as its own request returns and let the analytics panels fill in; or defer the dashboard requests until the pane is actually visible and idle.
2. The analytics queries should cost what the requested range costs, not the whole archive: push the date predicate into SQL through the existing idx_messages_activity_timestamp / idx_messages_velocity indexes (or a (session_id, category, tool_name, message_id) covering index on tool_calls for the tools and skills queries), and aggregate by day in SQL rather than by raw message timestamp in Go. The usage cache already shows the pattern of per-session facts plus cached rollups; the dashboard could sit on the same facts.

Sample session file or snippet

request trace of one cold open (ms since navigation):
  473 ->   800  /api/v1/sessions/sidebar-index
  473 ->  4884  /api/v1/analytics/summary?from=2025-09-03&to=2026-09-02
  482 ->  3367  /api/v1/analytics/activity
  483 ->  2415  /api/v1/analytics/hour-of-week
  483 ->  3796  /api/v1/analytics/velocity
  483 ->  5031  /api/v1/analytics/skills
  483 ->     ?  /api/v1/analytics/tools        (still running when the list appeared)
  session list visible at 5759

Steps to reproduce

1. Open the app at / with no session selected on an archive with a few hundred thousand messages.
2. Time until the session list renders; compare with the analytics endpoints blocked in devtools.
3. time curl '/api/v1/analytics/tools?from=<a year ago>&to=<today>'.

Checklist

• I searched existing issues
• I removed secrets and private data from any attached session files