/coaches is slow: two unindexed group/count aggregations per request

[mroderick]

Summary

DashboardController#wall_of_fame (/coaches) has a median duration of 769ms (max 7.5s) in production. The controller runs the aggregation twice per request:

@coaches_count = top_coach_query.length          # full group+count, no year filter
coaches = Member.where(id: top_coach_query
                           .year(year_param))
                .includes(:skills)

where top_coach_query is WorkshopInvitation.to_coaches.attended.group(:member_id).order('COUNT(member_id) DESC').select(:member_id) — a group/count over workshop_invitations (~2.0M rows) with no covering index for it.

Measured

3h window on 2026-09-16/17: 89 requests, median 769ms, max 7.5s, median db_runtime 307ms across only 5 queries, median view_runtime 212ms. These numbers are stale by the time you read this — re-measure from current logs or the Codebar canonical logs dashboard before starting (the raw drain archive is queryable with DuckDB; ask Morgan or see the homelab services/observability setup).

Suggested directions

1. Run the aggregation once and reuse it for both @coaches_count and the member list
2. EXPLAIN (ANALYZE, BUFFERS) the group/count query for a recent year — check whether a covering index on workshop_invitations (role, attending, workshop_id, member_id) helps
3. Past years are effectively immutable — a per-year cache (fragment or low-level) removes repeat aggregation entirely

Verify

Median db_runtime and total duration drop on /coaches; no regression on the #2885 dashboard.

Related: #2885