Hi,
I’m trying to get all documents that are between 2 dates with the condition if the first date field does not exists, use another date field, and sort this query with descending order, i got it working but the performance is terrible (asc order is working much better) maybe i got some indexes missing, i tried adding some with no success
the sort is on the firstDateField
this query runs for 56 seconds, if i remove the part with the “exist with gte” and “exist with lt” the query runs 150ms
"$and" : [ {
"$or" : [
{
"firstDateField" : {
"$exists" : false
},
"secondDateField" : {
"$gte" : ISODate("2021-04-25T21:00:18.547+00:00")
}
},
{
"firstDateField" : {
"$gte" : ISODate("2021-04-25T21:00:18.547+00:00")
}
}
]
},
{
"$or" : [
{
"firstDateField" : {
"$exists" : false
},
"secondDateField" : {
"$lt" : ISODate("2021-04-27T14:35:18.547+00:00")
}
},
{
"firstDateField" : {
"$lt" : ISODate("2021-04-27T14:35:18.547+00:00")
}
}
]
}
]
Thanks!