Need some suggestions here. Having indexes on the match condition fields still end up as slow query. The collection has index on start field which is datetime field (i.e. start :2024-04-28T23:24:35.088+00:00)
However as we can see from below looks like index scan is not working as expected and these queries take a long time like 10-13mins to run. What improvements can be made?
Will changing the $dateToString to something else but achieving same results help with this query performance? Based on the data volume we have seen this query running for hours and had to be killed.
“type”: “command”,
“command”: {
“aggregate”: “collectionName”,
“pipeline”: [
{
“$match”: {
“start”: {
“$gte”: {
“$date”: “2024-05-22T16:25:52.029Z”
}
}
}
},
{
“$group”: {
“_id”: {
“date”: {
“$dateToString”: {
“date”: “$start”,
“format”: “%Y-%m-%dT%H:00:00.000Z”
}
},
“Id”: “$Id”
},
“count”: {
“$sum”: 1
}
}
}
]
“planSummary”: “IXSCAN { start: -1 }”,
“cursorid”: 3388478945949195000,
“keysExamined”: 1175129,
“docsExamined”: 1175129,
“numYields”: 69312,
“nreturned”: 101,
“reslen”: 10976
This isn’t a one off case but thats the generic pattern we are seeing for all such queries. We have M200 cluster.