Learning hours roster aggregate is slow (161 ms, 74% of LearningHoursController#index)

Offen
#7,396 0 Kommentare 0 Reaktionen 0 zugewiesene Personen Auf GitHub ansehen

Maintainer antworten meist innerhalb von 1 Tag

Dieses Issue hat noch niemand übernommen.

Bewertung

Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Anfängerfreundlichkeit
62/100
Issue-Typ
Bug
Klarheit
Klar beschrieben
Aktivitätsstatus
Aktiv
Tech-Stack
postgresql, rails, ruby

Rechercherichtung

Start with app/models/learning_hour.rb and inspect the two roster aggregate methods, then review app/services/learning_hours_dashboard_rows_service.rb and the existing indexes before checking the query plan. The issue suggests a composite index, grouping change, or caching; preserve the casa_org_id scope and use the listed request and system specs to check behavior. Done means the aggregate is faster and cross organization scoping still works.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Beschreibung

📈 Performance Improvement 🗄️ Data db Difficulty: Intermediate Help Wanted Priority: High ruby

Problem

Scout APM, Slow Query Insights (7 days to 2026-11-15): the LearningHour roster aggregate behind LearningHoursController#index is 161 ms per query, 74% of that endpoint's response time. It is new this week.

Root cause

LearningHour.all_volunteers_learning_hours (admins) and supervisor_volunteers_learning_hours (supervisors) in app/models/learning_hour.rb join users, filter on users.casa_org_id and learning_hours.occurred_at, then GROUP BY users.display_name, users.id and SUM(...). learning_hours only has an index on user_id, so the occurred_at range filter scans every learning hour for the matching users. Cost grows with the number of entries ever logged.

The controller then does rows.to_a, searches and paginates in Ruby (app/controllers/learning_hours_controller.rb). That is fine at roster size, but the aggregate still runs in full on every page view, including every pagination click.

Suggested fix (pick one or combine)

  • Run EXPLAIN (ANALYZE, BUFFERS) on the query in a production console or a copy of the data and confirm a seq scan on learning_hours.
  • Add a composite index add_index :learning_hours, [:user_id, :occurred_at], algorithm: :concurrently (Strong Migrations requires disable_ddl_transaction!). Check the plan again afterwards.
  • Group by users.id only and select MAX(users.display_name) (or use users.id in the GROUP BY with users.display_name functionally dependent, which Postgres allows when id is the primary key). This avoids sorting and grouping on a text column.
  • Optional: cache the per-volunteer totals for the selected period for a few minutes (Rails.cache.fetch, keyed by org, role and period), so pagination clicks do not re-run the aggregate.
  • Keep the cross-org check: the query must still be scoped to casa_org_id. Run spec/requests/learning_hours_spec.rb and spec/system/learning_hours/.

Related: #6505 (ensure indexes present).

Difficulty: intermediate. Files: app/models/learning_hour.rb, app/services/learning_hours_dashboard_rows_service.rb, a new migration under db/migrate/.

Vorherrschende Sprache
Ruby
Sterne
384
Forks
558
Ø Merge
1 T. 7 Std.
Gemergte PRs (30 T.)
63

Entwicklungsumgebung

In Codespaces öffnen

Startet den Dev-Container des Projekts im Browser, mit Ihrem eigenen GitHub-Konto.

Erste Schritte

  1. Lesen Sie das ganze Issue und danach den Beitragsleitfaden des Projekts.
  2. Schreiben Sie ins Issue, dass Sie es übernehmen — das erspart doppelte Arbeit.
  3. Forken Sie das Repository und arbeiten Sie in einem Branch.
  4. Öffnen Sie einen Pull Request, der die Issue-Nummer nennt.

Mehr aus rubyforgood/casa

Alle Issues in rubyforgood/casa

Ähnliche Issues

Weitere Issues zu Ruby

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.