Freegle / Iznik / 24502

72%

Build Type

push

circleci

Committed by edwh

Commit Message

perf(rippling): bound the metrics first-ripple subquery to the window start, not 100 days

/rippling/metrics was still timing out. The reply-rate (1), replies-per-post (1b) and
taken-rate (4) KPI queries each materialise the same `rip` derived table - the first
rippled_in=1 arrival per msgid - and each bounded it to `arrival >= NOW() - INTERVAL 100
DAY`. Over 100 days that range is not selective, so the optimiser scanned messages_groups
via the low-cardinality `deleted` index: EXPLAIN showed ~8.8M rows examined to find the
~129k (1.6%) rippled_in=1 rows. Three such queries run concurrently, so this dominated the
endpoint.

Bound the rip subquery to the requested window `start` instead. It's provably safe: a post
only enters the outer set when its ORIGIN (rippled_in=0) row's arrival is in [start, end),
and a ripple can only happen after the post is posted, so first_ripple >= origin arrival >=
start - bounding to start never drops a first_ripple for an in-window post. With a selective
range the optimiser uses the `arrival` index instead.

Measured on live data (30-day window): reply-rate query 20.3s -> 3.7s (5.5x), identical
results (row-for-row diff clean). Same win for replies-per-post and taken-rate, which share
the subquery. No schema change.

Remaining tall pole after this is the reply-distance median query (~6s); left for a follow-up.

Test: full Go suite green (3369), including TestRipplingMetricsAnchorsRippledCohortOnFirstRippleDate
which pins the first-ripple anchoring the bound relies on, plus the reply/taken/distance cohort tests.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01AsgvoSgfeUwBMBbxKJHSNy

Coverage Stats

12231 of 16161 branches covered (75.68%)

Branch coverage included in aggregate %.

5 of 5 new or added lines in 1 file covered. (100.0%)

84 existing lines in 2 files now uncovered.

130409 of 182995 relevant lines covered (71.26%)

37.85 hits per line