Best index on aggregation with multiple match conditions

Working with Data
,
4

You can run explain and see how the pipeline gets transformed. Two things you can see from that:

  1. all the $match stages get coalesced into one
  2. project gets factored out and set to the query subsystem (so it doesn’t matter where it is in this pipeline though I recommend putting it last always, regardless).

What we see is that you are using $expr for some reason and it’s NOT as efficient as regular match expressions. If the format of createdAtFrom date string is “normal” then it can just be passed to ISODate() constructor as right hand side of the comparison.

Now, best index will always start with {__STATE__:1, countryCode:1, ... } since it seems like those are always filtered on, but the order of the other three fields depend on which filtering is more likely (and equality being ahead of range comparisons).

Asya
P.S. you don’t need "$exists": true, since that’s a strict subset of "$ne": null for countryCode in your query.