Investigate report download query performance

[andrewleith]

Describe the bug

When a service has more than 470k notifications in one week, the report download fails with a 504 - Endpoint request timed out thrown from API to ADMIN.

Notes:

• There is a 6MB limit on our lambda API that could affect this
• There is a 30s timeout on our api gateway that could affect this
• BUT, I was able to download a report from another service with 360k notifications. This download was 35MB and took several minutes to download 🤷

Error as seen in New Relic: https://one.newrelic.com/nr1-core/errors-inbox/entity-inbox/MjY5MTk3NHxBUE18QVBQTElDQVRJT058MjgzNDY5MDYx?account=2691974&begin=1713363600000&end=1713367560000&state=b0664a13-e152-bfe2-1cb2-e2589bbe21f2

Related fresh desk ticket: https://cds-snc.freshdesk.com/a/tickets/17194

Bug Severity

SEV-3 (SEV-1 Critical, SEV-2 Major, SEV-3 Minor, SEV-4 Low)

Acceptance criteria

• [ ] Investigate performance of report download query
• [ ] Determine where failure occurs (i.e. running query, or transferring large data set between api+admin, something else?)

To Reproduce

Steps to reproduce the behavior:

1. Go to the dashboard of a service with more than 500k notifications for the week
2. Click on Emails sent tile
3. Click Download this report
4. See error

Expected behavior

Report should download successfully

Impact

Describe overall user/system impact to consider when prioritizing this issue.

If applicable

Impact on Notify users:

• services with large sends are unable to review their sending reports offline

Impact on Recipients:

• None

Impact on Notify team:

• Manual creation of reports in this case takes time

Screenshots

If applicable, add screenshots to help explain your problem.

Additional context

Add any other context about the problem here.