/ingests/status Slow With a Large Database

[emimaesmith]

Description The REST API for /ingests/status takes too long to return when the database is very large.

Reproduction Steps Steps to reproduce the problem:

1. Have a large database
2. Navigate to /v6/ingets/status
3. Watch it return a 504

Expected behavior The /ingest/status should return results - not a 504.

Related to #1831

[emimaesmith]

Adding timings to the different parts of the get_status method reveals the majority of the time is spent grouping the results by time.

[mheppner]

A few optimizations can be made on the db level for ingests.

For /ingests, some massive improvements:

• page size 100: 205 queries at 12070ms (15.2s request) -> 6 queries at 1501ms (2.4s request)
• page size 500: 1005 queries at 55380ms (70.2s request) -> 6 queries at 2429ms (3.7s request)

For /ingests/status:

• 17 queries at 6335ms (8.1s request) -> 5 queries at 5527ms (7.6s request)

/ingests/status isn't too much better but there isn't much else to improve. There is a bit more computation going on at the Python level than with other requests, but you can see the overhead time (difference in db queries vs full HTTP request) isn't that much more. The single query that is holding most of this up:

SELECT ••• FROM "ingest" LEFT OUTER JOIN "strike" ON ("ingest"."strike_id" = "strike"."id") WHERE "ingest"."status" = 'INGESTED' ORDER BY "ingest"."data_started" ASC

I'm guessing it's just due to the size of the database and that inner join.