VolunteersController#index: one big datatable query is 105 ms (37% of response time)
维护者通常 1 天内回复
还没有人认领这个 Issue。
评估
- 难度
- 4/5
- 预计耗时
- 3-5 天
- 新手友好度
- 38/100
- Issue 类型
- 缺陷
- 描述清晰度
- 描述清楚
- 活跃度
- 活跃
- 领域
- backend, databases, performance
调研方向
Start with app/datatables/volunteer_datatable.rb and trace raw_records, index_count, and the default display_name sort; use the default request's EXPLAIN (ANALYZE, BUFFERS) plan to identify the expensive node. Keep the organization policy scope, then run spec/system/volunteers/ and spec/datatables/volunteer_datatable_spec.rb; done means the query work is reduced without breaking the scoped results or filters.
由索引模型根据 Issue 内容生成。
描述
Problem
Scout APM, Slow Query Insights (7 days to 2026-11-15): the Volunteer query behind VolunteersController#index is 105 ms per query and 37% of the endpoint's response time. First seen about 3 months ago.
#7279 (closed) fixed the page loading every case contact in the org. This is the next cost: the one big query that is left.
Root cause
VolunteerDatatable#raw_records (app/datatables/volunteer_datatable.rb) builds one SELECT with several LEFT JOINed subqueries (transition-aged-youth cases, most recent attempt using a window function over case_contacts, contacts in the past days, hours in the past 30 days), then filters, orders on a computed alias and applies OFFSET/LIMIT. Postgres has to compute every subquery for every volunteer in the org before it can sort and cut a page of 25. index_count then runs a second COUNT(DISTINCT users.id) over the same joins.
Suggested fix (pick one or combine)
- Run
EXPLAIN (ANALYZE, BUFFERS)for the default request (active, sorted bydisplay_name) and find the slowest node. Paste the plan in a comment. - Make
index_countcheaper: the count does not need themost_recent_attemptsor hours subqueries unless a filter uses them. Build the count frombase_relationplus only the joins the active filters need. - For the default sort (
display_name), page first (25 ids with LIMIT) and then compute the subquery columns only for those 25 volunteers, asdataalready does for the DataTables JSON path (active_case_counts_forand friends). - Check the indexes behind the subqueries:
case_contacts (creator_id, occurred_at)andcase_assignments (volunteer_id, active). Add them concurrently if missing (see #6505). - Keep the org scope: the data must still come from
policy_scope(current_organization.volunteers). Runspec/system/volunteers/andspec/datatables/volunteer_datatable_spec.rb.
Difficulty: intermediate to advanced (SQL). Files: app/datatables/volunteer_datatable.rb, app/controllers/volunteers_controller.rb.
- 主要语言
- Ruby
- 星标
- 384
- 派生
- 558
- 平均合并
- 1 天 7 小时
- 30 天内合并 PR
- 63
环境准备
在浏览器里用你自己的 GitHub 账号启动这个项目的开发容器。
- 提供 Dockerfile 或 Docker Compose 文件
- 有 Pull Request 模板
- 阅读贡献指南
从这里开始
- 先读完整个 Issue,再读项目的贡献指南。
- 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
- Fork 仓库,在一个分支上完成修改。
- 提交 Pull Request,并在描述里引用这个 Issue 编号。
rubyforgood/casa 的其他 Issue
-
⛓️ Security Difficulty: Easy Good First Issue Help Wanted ruby Type: Bug
难度 2/5 1-3 小时 新手友好度 82/100
rubyforgood/casa#7404 ·
维护者通常 1 天内回复
-
Difficulty: Easy Good First Issue Help Wanted ruby Type: Bug
难度 2/5 1-3 小时 新手友好度 76/100
rubyforgood/casa#7402 ·
维护者通常 1 天内回复
-
Remove the bin/bundle binstub (Heroku warns it can break Bundler on deploy)可能已有人在做 @Akash-Dhanwate 于 8 天前认领。 未关闭dependencies Difficulty: Easy Good First Issue
难度 1/5 1 小时以内 新手友好度 82/100
rubyforgood/casa#7325 · 2 条评论 ·
维护者通常 1 天内回复
-
Reports page: move export-button disable/re-enable into a Stimulus controller可能重新可做 @Akash-Dhanwate 于 9 天前认领,目前没有进行中的 PR。 未关闭Difficulty: Easy Good First Issue Help Wanted javascript
难度 2/5 1-3 小时 新手友好度 72/100
rubyforgood/casa#7311 · 2 条评论 ·
维护者通常 1 天内回复
-
New case: use Stimulus for the "I don't know the court date yet" toggle可能重新可做 @Aryankn29 于 9 天前认领,目前没有进行中的 PR。 未关闭Difficulty: Easy Good First Issue Help Wanted javascript
难度 2/5 1-3 小时 新手友好度 78/100
rubyforgood/casa#7310 · 1 条评论 ·
维护者通常 1 天内回复
相似的 Issue
-
Bug Ruby
难度 2/5 1-3 小时 新手友好度 76/100
getsentry/sentry-ruby#3211 · 1 条评论 ·
维护者通常 2 天内回复
-
enhancement
难度 2/5 1-3 小时 新手友好度 66/100
internetee/registry#2984 ·
维护者通常 6 天内回复
-
难度 2/5 1-3 小时 新手友好度 82/100
ecosyste-ms/issues#862 ·
维护者通常 1 天内回复
-
Redis Lua error `@user_script:52: ERR value is not an integer or out of range` when `window_size` is configured可能已有人在做 @mimosa 今天认领。 未关闭bug
难度 2/5 1-3 小时 新手友好度 82/100
维护者通常 1 天内回复
-
难度 2/5 1-3 小时 新手友好度 67/100
riscv/riscv-unified-db#2829 ·
维护者通常 1 天内回复