VolunteersController#index: one big datatable query is 105 ms (37% of response time)

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

维护者通常 1 天内回复

还没有人认领这个 Issue。

评估

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

调研方向

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 内容生成。

描述

📈 Performance Improvement 🗄️ Data Difficulty: Advanced Help Wanted ruby

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 by display_name) and find the slowest node. Paste the plan in a comment.
  • Make index_count cheaper: the count does not need the most_recent_attempts or hours subqueries unless a filter uses them. Build the count from base_relation plus 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, as data already does for the DataTables JSON path (active_case_counts_for and friends).
  • Check the indexes behind the subqueries: case_contacts (creator_id, occurred_at) and case_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). Run spec/system/volunteers/ and spec/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

环境准备

在 Codespaces 中打开

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

从这里开始

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

rubyforgood/casa 的其他 Issue

查看 rubyforgood/casa 的全部 Issue

相似的 Issue

更多 Ruby Issue

把新 issue 发到你的邮箱

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