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

Abierto
#7,398 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 1 día

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
38/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
postgresql, rails, ruby

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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

Lenguaje dominante
Ruby
Estrellas
384
Forks
558
Merge medio
1 d 7 h
PR fusionados (30 d)
63

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de rubyforgood/casa

Todos los issues de rubyforgood/casa

Issues similares

Más issues de Ruby

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.