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

オープン
#7,396 コメント 0 件 リアクション 0 件 担当者 0 名 GitHub で見る

メンテナーはふだん 1 日以内に返信

まだ誰も着手していません。

評価

難易度
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時間
マージ済み PR(30日)
63

環境構築

Codespaces で開く

このプロジェクトの開発コンテナを、あなたの GitHub アカウントでブラウザ上に起動します。

はじめの一歩

  1. issue を最後まで読み、次にプロジェクトのコントリビューションガイドを読みます。
  2. 着手することを issue にコメントします — 二人が同じ作業をするのを防げます。
  3. リポジトリをフォークし、ブランチを切って変更します。
  4. issue 番号を参照したプルリクエストを送ります。

rubyforgood/casa のほかの issue

rubyforgood/casa の issue をすべて見る

似ている issue

Ruby の issue をもっと見る

新しい issue をメールで受け取る

初心者向けの GitHub issue を短くまとめたダイジェスト。