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

Open
#7,398 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
38/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
postgresql, rails, ruby

Research direction

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.

Written by the indexing model from the issue text.

Description

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

Dominant language
Ruby
Stars
384
Forks
558
Avg merge
1d 7h
Merged PRs (30d)
63

Getting set up

Open in Codespaces

Starts the project's dev container in your browser, under your own GitHub account.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from rubyforgood/casa

All issues in rubyforgood/casa

Similar issues

More Ruby issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.