aggregate query performance sorting by ISODate

Question

I have this kind of simple aggregate query

db.SomeCollection.aggregate([{
  "$match": {
    "Id": "someId"
  }
}, {
  "$sort": {
    "someISODatePropertyName": 1
  }
}, {
  "$unwind": {
    "path": "$somePropertyName"
  }
}], {
  allowDiskUse: true
})

this query returns 50 items at most and takes 10 seconds to complete.

If I simply change the sort property with a numeric one:

db.SomeCollection.aggregate([{
  "$match": {
    "Id": "someId"
  }
}, {
  "$sort": {
    "someNumericPropertyName": 1
  }
}, {
  "$unwind": {
    "path": "$somePropertyName"
  }
}], {
  allowDiskUse: true
})

the query takes a few milliseconds to complete.

Is there any sorting issue with ISODate properties?

I really can't understand why it takes so long in the first version.

Thank you.

UPDATE

this is the query result with "explain" flag set to true (note: on the ISODate field there is an index):

{
    "waitedMS" : NumberLong(0),
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "StreamId" : "5b8cc895-c626-5994-95d4-b9ac89fb66ed"
                },
                "sort" : {
                    "CommitStamp" : 1
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "vrp-events-prod.Commits",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "StreamId" : {
                            "$eq" : "5b8cc895-c626-5994-95d4-b9ac89fb66ed"
                        }
                    },
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "StreamId" : {
                                "$eq" : "5b8cc895-c626-5994-95d4-b9ac89fb66ed"
                            }
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "CommitStamp" : 1
                            },
                            "indexName" : "CommitStamp_Index",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "CommitStamp" : [ 
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$unwind" : {
                "path" : "$Events"
            }
        }
    ],
    "ok" : 1
}

Answer 1

In my experience date sorting without an index on the date field is slow. Assuming that you read from this collection often and usually sort by date, adding an index will be the ideal solution.

db.collection.createIndex("someISODatePropertyName": 1 or -1)

Try it, it always made a huge difference for me.

Update:

Assuming that this doesn't slow down your write queries too much, add an index that covers both the match and the sort:

db.xxx.createIndex({"StreamId": 1, "CommitStamp": 1});

I tested this on my collection and it sped up the query from 15 seconds (using the date index) to less than a second (using the newly created index). FYI, my updated explain shows:

"queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "db.xxx",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "id" : {
                                                        "$eq" : 122
                                                }
                                        },
                                        "winningPlan" : {
                                                "stage" : "FETCH",
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "id" : 1,
                                                                "date" : 1
                                                        },
                                                        "indexName" : "id_1_date_1",
                                                        "isMultiKey" : false,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "id" : [
                                                                        "[122.0, 122.0]"
                                                                ],
                                                                "date" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [ ]
                                }