Fix: appEvents(actorUserId,createdAt) composite index — correlated MAX(createdAt) subquery regression in teacher-report query
dbperf snapshot triage (2026-07-15, db-mars-cc) flagged a REAL unresolved latency regression: accessible_comisiones CTE variant queryid -6974078298807959143 (JTP/docente-report query, reporting.ts, correlated per-teacher appEvents MAX(createdAt) subquery). Lifetime mean flat ~357-373ms for 3 weeks (6/26-7/13), jumped to 478ms (7/14) then 554ms (7/15). Back-calculated marginal cost of NEW calls: ~2072ms/call on 7/14, ~861ms/call on 7/15 — both far above baseline, not explained by volume/periodo/TCM growth or code changes. Candidates: call-mix shift (all-teachers vs single-teacher report views) or transient contention; appEvents doesn't log query params so can't correlate further without call-site instrumentation. NOT filing a blind fix. Watch 2-3 more daily dbperf snapshots: climbing trend = real regression, file WI + EXPLAIN ANALYZE with real params; settles back toward ~360ms = transient blip, close.
Questions
Activity
-
VERDICT (2026-07-15, off already-captured data per Elazar's call — no further waiting): REAL REGRESSION, confirmed root cause, not the accessible_comisiones CTE itself. Marginal (incremental) mean per NEW call across all 20 present snapshots (6/26-7/15): flat 297-593ms for 19 consecutive points (6/27-7/13), then 2072ms (7/14) and 861ms (7/15) — 2 consecutive days, 1.5-5x the 3wk ceiling. Not volume-driven (marginal mean rose WITH call growth). appEvents daily inserts on 7/14-15 (144-200) are BELOW several prior days (472 on 7/6, 565 on 7/13) — total-row growth doesn't track the spike day-for-day. ROOT CAUSE (EXPLAIN ANALYZE against real pg_stat_statements query text, real teacher_universe=80): correlated per-teacher subquery 'SELECT MAX(ae.createdAt) FROM appEvents ae WHERE ae.actorUserId=tu.userId' (lastActivityAt) dominates cost — 444ms/149224 buffer hits total, ~149200 inside this one SubPlan across 75 loops. accessible_comisiones itself: 3.36ms, trivial — not the driver (title carried over from MARS-132 imprecisely). Planner picks Index Scan on ix_appEvents_createdAt + filter actorUserId + LIMIT1 (walk-newest-first) instead of the existing single-column ix_appEvents_actorUserId. For teachers with sparse/no-recent appEvents, this walks deep removing thousands of non-matching rows per loop before a match — degrades as total appEvents rows grow (16913 now), worse for any teacher whose last activity sits further back, independent of that teacher's own args. FIX: composite covering index CREATE INDEX CONCURRENTLY ix_appEvents_actorUserId_createdAt ON appEvents(actorUserId, createdAt DESC) — direct index seek per teacher instead of global scan-and-filter. Additive-only DDL, low-risk. Class-A — design ping sent to audit-mars-ca before applying. Scope: this WI now covers the fix (index), not just watch.
-
confirmed real regression, root cause isolated, fix (composite index) pending audit design review
-
Fix: appEvents(actorUserId,createdAt) composite index — correlated MAX(createdAt) subquery regression in teacher-report query
-
Post-apply verification (audit-mars-ca's required step): re-ran EXPLAIN ANALYZE, real teacher_universe=80. New composite index ix_appEvents_actorUserId_createdAt selected via Index Only Scan, Index Cond actorUserId=tu.userId, zero rows-removed-by-filter (was 5544/loop). Buffer hits 149224->193 across 75 loops, correlated SubPlan cost 444ms->1.17ms. Index confirmed valid. Sent to audit-mars-ca for sign-off.
-
composite index applied+verified, audit design-GO obtained pre-apply, post-apply EXPLAIN ANALYZE confirms fix (149224->193 buffer hits)