Optimize notifications_summary

[moshloop]

Aggregate  (cost=25590.13..25590.15 rows=1 width=112)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=12742.64..12742.65 rows=1 width=8)
          ->  GroupAggregate  (cost=12484.07..12742.56 rows=6 width=452)
                Group Key: notifications_1.id, (round(avg(CASE WHEN (notification_send_history_1.error IS NOT NULL) THEN notification_send_history_1.duration_millis ELSE NULL::integer END), 2)), (count(CASE WHEN (notification_send_history_1.error IS NOT NULL) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (notification_send_history_1.error IS NULL) THEN 1 ELSE NULL::integer END)), (mode() WITHIN GROUP (ORDER BY notification_send_history_1.error))
                ->  Incremental Sort  (cost=12484.07..12742.43 rows=6 width=96)
                      Sort Key: notifications_1.id, (round(avg(CASE WHEN (notification_send_history_1.error IS NOT NULL) THEN notification_send_history_1.duration_millis ELSE NULL::integer END), 2)), (count(CASE WHEN (notification_send_history_1.error IS NOT NULL) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (notification_send_history_1.error IS NULL) THEN 1 ELSE NULL::integer END)), (mode() WITHIN GROUP (ORDER BY notification_send_history_1.error))
                      Presorted Key: notifications_1.id
                      ->  Merge Left Join  (cost=12432.43..12742.16 rows=6 width=96)
                            Merge Cond: (notifications_1.id = notification_send_history_1.notification_id)
                            Inner Unique: true
                            ->  Sort  (cost=44.48..44.49 rows=6 width=16)
                                  Sort Key: notifications_1.id
                                  ->  Hash Right Join  (cost=2.29..44.40 rows=6 width=16)
                                        Hash Cond: ((event_queue_1.properties ->> 'notification_id'::text) = (notifications_1.id)::text)
                                        Inner Unique: false
                                        ->  Seq Scan on public.event_queue event_queue_1  (cost=0.00..41.95 rows=21 width=329)
                                              Filter: ((event_queue_1.attempts < 4) AND (event_queue_1.name = 'notification.send'::text))
                                        ->  Hash  (cost=2.21..2.21 rows=6 width=16)
                                              ->  Seq Scan on public.notifications notifications_1  (cost=0.00..2.21 rows=6 width=16)
                                                    Filter: (notifications_1.deleted_at IS NULL)
                            ->  GroupAggregate  (cost=12387.96..12697.56 rows=6 width=96)
                                  Group Key: notification_send_history_1.notification_id
                                  ->  Sort  (cost=12387.96..12439.54 rows=20633 width=1099)
                                        Sort Key: notification_send_history_1.notification_id
                                        ->  Seq Scan on public.notification_send_history notification_send_history_1  (cost=0.00..962.33 rows=20633 width=1099)
  ->  Limit  (cost=12847.37..12847.39 rows=6 width=639)
        ->  Sort  (cost=12847.37..12847.39 rows=6 width=639)
              Sort Key: notifications.created_at DESC
              ->  Nested Loop Left Join  (cost=12484.36..12847.29 rows=6 width=639)
                    Inner Unique: false
                    ->  Nested Loop Left Join  (cost=12484.21..12798.15 rows=6 width=733)
                          Inner Unique: false
                          ->  Nested Loop Left Join  (cost=12484.21..12791.73 rows=6 width=629)
                                Inner Unique: false
                                ->  GroupAggregate  (cost=12484.07..12742.58 rows=6 width=559)
                                      Group Key: notifications.id, (round(avg(CASE WHEN (notification_send_history.error IS NOT NULL) THEN notification_send_history.duration_millis ELSE NULL::integer END), 2)), (count(CASE WHEN (notification_send_history.error IS NOT NULL) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (notification_send_history.error IS NULL) THEN 1 ELSE NULL::integer END)), (mode() WITHIN GROUP (ORDER BY notification_send_history.error))
                                      ->  Incremental Sort  (cost=12484.07..12742.43 rows=6 width=527)
                                            Sort Key: notifications.id, (round(avg(CASE WHEN (notification_send_history.error IS NOT NULL) THEN notification_send_history.duration_millis ELSE NULL::integer END), 2)), (count(CASE WHEN (notification_send_history.error IS NOT NULL) THEN 1 ELSE NULL::integer END)), (count(CASE WHEN (notification_send_history.error IS NULL) THEN 1 ELSE NULL::integer END)), (mode() WITHIN GROUP (ORDER BY notification_send_history.error))
                                            Presorted Key: notifications.id
                                            ->  Merge Left Join  (cost=12432.43..12742.16 rows=6 width=527)
                                                  Merge Cond: (notifications.id = notification_send_history.notification_id)
                                                  Inner Unique: true
                                                  ->  Sort  (cost=44.48..44.49 rows=6 width=447)
                                                        Sort Key: notifications.id
                                                        ->  Hash Right Join  (cost=2.29..44.40 rows=6 width=447)
                                                              Hash Cond: ((event_queue.properties ->> 'notification_id'::text) = (notifications.id)::text)
                                                              Inner Unique: false
                                                              ->  Seq Scan on public.event_queue  (cost=0.00..41.95 rows=21 width=345)
                                                                    Filter: ((event_queue.attempts < 4) AND (event_queue.name = 'notification.send'::text))
                                                              ->  Hash  (cost=2.21..2.21 rows=6 width=431)
                                                                    ->  Seq Scan on public.notifications  (cost=0.00..2.21 rows=6 width=431)
                                                                          Filter: (notifications.deleted_at IS NULL)
                                                  ->  GroupAggregate  (cost=12387.96..12697.56 rows=6 width=96)
                                                        Group Key: notification_send_history.notification_id
                                                        ->  Sort  (cost=12387.96..12439.54 rows=20633 width=1099)
                                                              Sort Key: notification_send_history.notification_id
                                                              ->  Seq Scan on public.notification_send_history  (cost=0.00..962.33 rows=20633 width=1099)
                                ->  Subquery Scan on notifications_summary_person_1  (cost=0.14..8.17 rows=1 width=86)
                                      ->  Limit  (cost=0.14..8.16 rows=1 width=62)
                                            ->  Index Scan using people_pkey on public.people people_1  (cost=0.14..8.16 rows=1 width=62)
                                                  Index Cond: (people_1.id = notifications.person_id)
                          ->  Subquery Scan on notifications_summary_team_1  (cost=0.00..1.06 rows=1 width=104)
                                ->  Limit  (cost=0.00..1.05 rows=1 width=80)
                                      ->  Seq Scan on public.teams teams_1  (cost=0.00..1.05 rows=1 width=80)
                                            Filter: (teams_1.id = notifications.team_id)
                    ->  Subquery Scan on notifications_summary_created_by_1  (cost=0.14..8.17 rows=1 width=86)
                          ->  Limit  (cost=0.14..8.16 rows=1 width=62)
                                ->  Index Scan using people_pkey on public.people people_1_1  (cost=0.14..8.16 rows=1 width=62)
                                      Index Cond: (people_1_1.id = notifications.created_by)
Query Identifier: 6316707696111731852