Freegle / Iznik / 23800

72%

push

circleci

perf(stats): hoist 30-day-window stat queries out of the per-group loop

generate() ran the POST_METHOD_BREAKDOWN, MESSAGE_BREAKDOWN and
ACTIVE_USERS queries once per group, each re-scanning the full 30-day
site-wide window (~118k messages_groups rows) and re-filtering by
groupid. With ~500 groups that's ~500 identical window scans per date.

The rippled_in=0 predicate the stats fix added sits in no index, so the
optimiser drives these from messages.arrival (the whole window) rather
than the (groupid,...) index — making each per-group scan ~118k rows.
Under the daytime cron storm a single date wedged for 30+ min; the
2026-05-14..06-27 rippled_in backfill could not complete.

Fix mirrors the existing searchCounts / regenerateWeightForRange()
pattern: compute each metric once per date with GROUP BY groupid in
buildDailyContext(), then look it up per group. One windowed scan per
date instead of ~500, no new index. Verified byte-identical per-group
results vs the old queries; the three hoisted queries total ~8.5s/date.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_014aXkxBPEiPSJBaRMjz7opk

10742 of 12324 branches covered (87.16%)

Branch coverage included in aggregate %.

98623 of 138807 relevant lines covered (71.05%)

42.06 hits per line