Chores/analyse performance problems

Anzeige Gruppenangebote

Slow query triggered at:

app/controllers/group_offers_controller.rb#L149

uses scope sorted_by_active_volunteer_count @ app/models/group_offer.rb#L86-98

[1] pry(#<GroupOffersController>)> @group_offers.explain
  GroupOffer Load (11.1ms)  SELECT group_offers.*, count(
              CASE WHEN group_assignments.termination_verified_by_id IS NULL AND
              active_storage_attachments.id IS NOT NULL AND
              group_assignments.period_start IS NOT NULL THEN 1 ELSE NULL END
            ) as active_volunteer_count_sort FROM "group_offers" LEFT OUTER JOIN "group_assignments" ON "group_assignments"."deleted_at" IS NULL AND "group_assignments"."group_offer_id" = "group_offers"."id" LEFT OUTER JOIN "active_storage_attachments" ON "active_storage_attachments"."record_type" = $1 AND "active_storage_attachments"."name" = $2 AND "active_storage_attachments"."record_id" = "group_assignments"."id" WHERE "group_offers"."deleted_at" IS NULL AND ("group_offers"."period_end" IS NULL OR "group_offers"."period_end" = NULL) GROUP BY "group_offers"."id" ORDER BY "group_offers"."active" DESC, "group_offers"."created_at" DESC  [["record_type", "GroupAssignment"], ["name", "pdf"]]
  ↳ (pry):7:in `init_sorts'
=> EXPLAIN for: SELECT group_offers.*, count(
              CASE WHEN group_assignments.termination_verified_by_id IS NULL AND
              active_storage_attachments.id IS NOT NULL AND
              group_assignments.period_start IS NOT NULL THEN 1 ELSE NULL END
            ) as active_volunteer_count_sort FROM "group_offers" LEFT OUTER JOIN "group_assignments" ON "group_assignments"."deleted_at" IS NULL AND "group_assignments"."group_offer_id" = "group_offers"."id" LEFT OUTER JOIN "active_storage_attachments" ON "active_storage_attachments"."record_type" = $1 AND "active_storage_attachments"."name" = $2 AND "active_storage_attachments"."record_id" = "group_assignments"."id" WHERE "group_offers"."deleted_at" IS NULL AND ("group_offers"."period_end" IS NULL OR "group_offers"."period_end" = NULL) GROUP BY "group_offers"."id" ORDER BY "group_offers"."active" DESC, "group_offers"."created_at" DESC [["record_type", "GroupAssignment"], ["name", "pdf"]]
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=168.52..168.74 rows=87 width=481)
   Sort Key: group_offers.active DESC, group_offers.created_at DESC
   ->  HashAggregate  (cost=164.84..165.72 rows=87 width=481)
         Group Key: group_offers.id
         ->  Hash Left Join  (cost=50.70..162.14 rows=541 width=493)
               Hash Cond: (group_assignments.id = active_storage_attachments.record_id)
               ->  Hash Right Join  (cost=13.15..120.50 rows=541 width=493)
                     Hash Cond: (group_assignments.group_offer_id = group_offers.id)
                     ->  Seq Scan on group_assignments  (cost=0.00..103.93 rows=1280 width=28)
                           Filter: (deleted_at IS NULL)
                     ->  Hash  (cost=12.06..12.06 rows=87 width=473)
                           ->  Seq Scan on group_offers  (cost=0.00..12.06 rows=87 width=473)
                                 Filter: ((deleted_at IS NULL) AND (period_end IS NULL))
               ->  Hash  (cost=35.20..35.20 rows=188 width=16)
                     ->  Bitmap Heap Scan on active_storage_attachments  (cost=15.38..35.20 rows=188 width=16)
                           Recheck Cond: (((record_type)::text = 'GroupAssignment'::text) AND ((name)::text = 'pdf'::text))
                           ->  Bitmap Index Scan on index_active_storage_attachments_uniqueness  (cost=0.00..15.34 rows=188 width=0)
                                 Index Cond: (((record_type)::text = 'GroupAssignment'::text) AND ((name)::text = 'pdf'::text))
(18 rows)

A version without the attachment in the sort would be quite a bit faster:

    active_assignment_count_select = <<-SQL.squish
      group_offers.*, count(
        CASE WHEN
            group_assignments.termination_verified_by_id IS NULL
          AND
            group_assignments.period_start IS NOT NULL
          THEN 1
          ELSE NULL
        END
      ) as active_volunteer_count_sort
    SQL
    direction = :desc if direction.blank?
    left_joins(:group_assignments)
      .select(active_assignment_count_select)
      .group(:id)
      .order(active_volunteer_count_sort: direction)

[6] pry(#<GroupOffersController>)> @group_offers.explain
  GroupOffer Load (6.1ms)  SELECT group_offers.*, count( CASE WHEN group_assignments.termination_verified_by_id IS NULL AND group_assignments.period_start IS NOT NULL THEN 1 ELSE NULL END ) as active_volunteer_count_sort FROM "group_offers" LEFT OUTER JOIN "group_assignments" ON "group_assignments"."deleted_at" IS NULL AND "group_assignments"."group_offer_id" = "group_offers"."id" WHERE "group_offers"."deleted_at" IS NULL AND ("group_offers"."active" = TRUE AND ("group_offers"."period_end" IS NULL OR "group_offers"."period_end" = NULL)) GROUP BY "group_offers"."id" ORDER BY "group_offers"."active" DESC, "group_offers"."created_at" DESC
  ↳ (pry):14:in `init_sorts'
=> EXPLAIN for: SELECT group_offers.*, count( CASE WHEN group_assignments.termination_verified_by_id IS NULL AND group_assignments.period_start IS NOT NULL THEN 1 ELSE NULL END ) as active_volunteer_count_sort FROM "group_offers" LEFT OUTER JOIN "group_assignments" ON "group_assignments"."deleted_at" IS NULL AND "group_assignments"."group_offer_id" = "group_offers"."id" WHERE "group_offers"."deleted_at" IS NULL AND ("group_offers"."active" = TRUE AND ("group_offers"."period_end" IS NULL OR "group_offers"."period_end" = NULL)) GROUP BY "group_offers"."id" ORDER BY "group_offers"."active" DESC, "group_offers"."created_at" DESC
                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Sort  (cost=122.19..122.27 rows=35 width=481)
   Sort Key: group_offers.active DESC, group_offers.created_at DESC
   ->  HashAggregate  (cost=120.94..121.29 rows=35 width=481)
         Group Key: group_offers.id
         ->  Hash Right Join  (cost=12.50..119.85 rows=217 width=485)
               Hash Cond: (group_assignments.group_offer_id = group_offers.id)
               ->  Seq Scan on group_assignments  (cost=0.00..103.93 rows=1280 width=20)
                     Filter: (deleted_at IS NULL)
               ->  Hash  (cost=12.06..12.06 rows=35 width=473)
                     ->  Seq Scan on group_offers  (cost=0.00..12.06 rows=35 width=473)
                           Filter: ((deleted_at IS NULL) AND active AND (period_end IS NULL))
(11 rows)