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

未关闭
#7,396 0 条评论 0 个 reaction 已指派 0 人 在 GitHub 查看

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
62/100
Issue 类型
缺陷
描述清晰度
描述清楚
活跃度
活跃
技术栈
postgresql, rails, ruby

调研方向

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.

由索引模型根据 Issue 内容生成。

描述

📈 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/.

主要语言
Ruby
星标
384
派生
558
平均合并
1 天 7 小时
30 天内合并 PR
63

环境准备

在 Codespaces 中打开

在浏览器里用你自己的 GitHub 账号启动这个项目的开发容器。

从这里开始

  1. 先读完整个 Issue,再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库,在一个分支上完成修改。
  4. 提交 Pull Request,并在描述里引用这个 Issue 编号。

rubyforgood/casa 的其他 Issue

查看 rubyforgood/casa 的全部 Issue

相似的 Issue

更多 Ruby Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。