Update the dao_fetch_todays_stats_for_service query.
Update the dao_fetch_todays_stats_for_service query.

We have an index on Notifications(service_id, created_at), by updating the query to use between created_at rather than date(created_at) this query will use the index. Changing the query plan to use an index scan rather than a sequence scan, see query plans below.
This query is still rather slow but is improved by this update.

https://www.pivotaltracker.com/story/show/178263480

explain analyze
SELECT notification_type, notification_status, count(id)
FROM notifications
WHERE service_id = 'e791dbd4-09ea-413a-b773-ead8728ddb09'
AND date(created_at) = '2021-05-23'
AND key_type != 'test'
GROUP BY notification_type, notification_status;
                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=6326816.31..6326926.48 rows=24 width=22) (actual time=91666.805..91712.976 rows=10 loops=1)
   Group Key: notification_type, notification_status
   ->  Gather Merge  (cost=6326816.31..6326925.88 rows=48 width=22) (actual time=91666.712..91712.962 rows=30 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial GroupAggregate  (cost=6325816.28..6325920.31 rows=24 width=22) (actual time=91662.907..91707.027 rows=10 loops=3)
               Group Key: notification_type, notification_status
               ->  Sort  (cost=6325816.28..6325842.23 rows=10379 width=30) (actual time=91635.890..91676.225 rows=270884 loops=3)
                     Sort Key: notification_type, notification_status
                     Sort Method: external merge  Disk: 10584kB
                     Worker 0:  Sort Method: external merge  Disk: 10648kB
                     Worker 1:  Sort Method: external merge  Disk: 10696kB
                     ->  Parallel Seq Scan on notifications  (cost=0.00..6325123.93 rows=10379 width=30) (actual time=0.036..91513.985 rows=270884 loops=3)
                           Filter: (((key_type)::text <> 'test'::text) AND (service_id = 'e791dbd4-09ea-413a-b773-ead8728ddb09'::uuid) AND (date(created_at) = '2021-05-23'::date))
                           Rows Removed by Filter: 16191366
 Planning Time: 0.760 ms
 Execution Time: 91714.500 ms
(17 rows)

explain analyze
SELECT notification_type, notification_status, count(id)
FROM notifications
WHERE service_id = 'e791dbd4-09ea-413a-b773-ead8728ddb09'
AND created_at  >= '2021-05-22 23:00'
and created_at < '2021-05-23 23:00'
AND key_type != 'test'
GROUP BY notification_type, notification_status;
                                                                                                                       QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Finalize GroupAggregate  (cost=2114273.37..2114279.57 rows=24 width=22) (actual time=21032.076..21035.725 rows=10 loops=1)
   Group Key: notification_type, notification_status
   ->  Gather Merge  (cost=2114273.37..2114278.97 rows=48 width=22) (actual time=21032.056..21035.703 rows=30 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=2113273.35..2113273.41 rows=24 width=22) (actual time=21029.261..21029.265 rows=10 loops=3)
               Sort Key: notification_type, notification_status
               Sort Method: quicksort  Memory: 25kB
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Partial HashAggregate  (cost=2113272.56..2113272.80 rows=24 width=22) (actual time=21029.228..21029.230 rows=10 loops=3)
                     Group Key: notification_type, notification_status
                     ->  Parallel Bitmap Heap Scan on notifications  (cost=114455.71..2111695.14 rows=210322 width=30) (actual time=4983.790..20960.581 rows=271217 loops=3)
                           Recheck Cond: ((service_id = 'e791dbd4-09ea-413a-b773-ead8728ddb09'::uuid) AND (created_at >= '2021-05-22 23:00:00'::timestamp without time zone) AND (created_at < '2021-05-23 23:00:00'::timestamp without time zone))
                           Rows Removed by Index Recheck: 1456269
                           Filter: ((key_type)::text <> 'test'::text)
                           Heap Blocks: exact=12330 lossy=123418
                           ->  Bitmap Index Scan on ix_notifications_service_created_at  (cost=0.00..114329.51 rows=543116 width=0) (actual time=4973.139..4973.140 rows=813671 loops=1)
                                 Index Cond: ((service_id = 'e791dbd4-09ea-413a-b773-ead8728ddb09'::uuid) AND (created_at >= '2021-05-22 23:00:00'::timestamp without time zone) AND (created_at < '2021-05-23 23:00:00'::timestamp without time zone))
 Planning Time: 0.191 ms
 Execution Time: 21035.770 ms
(21 rows)

alphagov/notifications-api,alphagov/notifications-api