Repository navigation
Statistics count routes time out #900
Description
Summary
Two statistics routes count variants per score set by scanning every published variant on every request. They average 21 and 37 seconds, so under the API's 30-second statement limit the slower one now returns 503. This issue precomputes the counts when the published-variants view is refreshed, so the routes read a small table instead.
Where
GET /api/v1/statistics/record/score-set/variant/countGET /api/v1/statistics/record/score-set/mapped-variant/count
Both are in routers/statistics.py and run GROUP BY score_set_urn with count(...) over published_variants_materialized_view. The mavedb.org UI doesn't call them; the statistics page uses /published/count. Callers are external scripts.
On the allele data model branch (RT) the same queries count mapping_record_id instead of mapped_variant_id.
Fix
- Add a materialized view of per-score-set variant and mapped-variant counts, derived from
published_variants_materialized_view. Write its definition as literal SQL in the migration, following Squash the migration history into one baseline so it replays from an empty database #877's rules. - Refresh it in
refresh_all_mat_views, straight after the view it's derived from. - Point both routes at it.
Acceptance criteria
- Both routes return in under a second in staging.
- Their responses match the current output for every score set.
- The counts update after the scheduled view refresh.
Background
Performance Insights, prod, 7 days to 2026-09-29: the mapped-variant count averaged 37.1s per call and the variant count 21.5s, each called once or twice a week.
A request-time cache was considered and rejected: the first request per process would still take 37 seconds and fail at the 30-second limit.
Activity