Large scale date sorting / filtering

[maciej-zarzeczny]

Try to improve performance of sorting filtering big data queries

[abhidg]

Likely an indexing issue since at the moment confirmed date is located within the events array. Should be fixed by adding a confirmedDate field and index with just the date

[iamleeg]

I don't know whether there are specific expectations of what to speed up here, but I see a small number of queries in prod that are still slow:

• a couple times someone searched for verification status is EXCLUDED and sourceId is {{Taiwan source}} and confirmation date between 1 dec 2019 and now, seems a bit weirdly specific but may be an important case
• someone was searching for cases by one curator (maybe themselves?) and their query came out as a text search for curatoremail:{{the email address}}, so this field wasn't parsed by the back end. Maybe it should be, and should be indexed? They were also searching for country:Deutschland which is not going to match but will be fixed by having the country drop-down in the search form.
• the aggregations for generating the map data do a whole collection scan. They search for list:true, then e.g. one of them projects country fields, then buckets by country. Maybe the bucketing could be done before the projection to speed it up.
• all of the mongoexport commands that rely on list:true are all slow. Probably we need to look for another way to deal with that parser limitation.

[iamleeg]

a couple times someone searched for verification status is EXCLUDED and sourceId is {{Taiwan source}} and confirmation date between 1 dec 2019 and now, seems a bit weirdly specific but may be an important case

This just came up again as the slowest query on the cluster in the graph window, so someone is doing it reasonably frequently.

[iamleeg]

A couple of indexes recommended by the Atlas automatic tool:

caseReference.sourceId: 1
caseReference.verificationStatus: 1

and

list: 1
revisionMetadata.creationMetadata.date: -1