mongo sort before match in aggregation

Question

Given a collection of a few million documents that look like:

{
    organization: ObjectId("6a55b2f1aae2fe0ddd525828"),
    updated_on: 2019-04-18 14:08:48.781Z
}

and 2 indices, on both keys {organization: 1} and {updated_on: 1}

The following query takes ages to return:

db.getCollection('sessions').aggregate([
        {
                "$match" : {
                        "organization" : ObjectId("5a55b2f1aae2fe0ddd525827"),
                }
        },
        {
                "$sort" : {
                        "updated_on" : 1
                }
        }
])

One thing to note is, the result is 0 matches. Upon further investigation, the planner in explain() actually returns the following:

{
    "stage" : "FETCH",
    "filter" : {
        "organization" : {
            "$eq" : ObjectId("5a55b2f1aae2fe0ddd525827")
        }
    },
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "updated_on" : 1.0
        },
        "indexName" : "updated_on_1",
        "isMultiKey" : false,
        "multiKeyPaths" : {
            "updated_on" : []
        },
        "isUnique" : false,
        "isSparse" : false,
        "isPartial" : false,
        "indexVersion" : 2,
        "direction" : "forward",
        "indexBounds" : {
            "updated_on" : [ 
                "[MinKey, MaxKey]"
            ]
        }
    }
}

Why would Mongo combine these into one stage and decide to to sort ALL documents BEFORE filtering?
How can I prevent that?

You can tell MongoDB use specific index [https://stackoverflow.com/a/57092149/3710490](https://stackoverflow.com/a/57092149/3710490) — Valijon, Dec 14 '19 at 09:45

score 0 · Answer 1 · answered Dec 14 '19 at 08:38

MongoDB will use the index on to $sort because it's a heavy operation even if matching before will limit the result to be sorted,

You can either force using the index for $match:

db.collection.aggregate(pipeline, {hint: "index_name"})

Or create a better index to solve both problems, see more informations here

db.collection.createIndex({organization: 1, updated_on:1}, {background: true})

prasad_ · Accepted Answer · 2019-12-14T10:28:04.673

Why would Mongo combine these into one stage and decide to to sort ALL documents BEFORE filtering? How can I prevent that?

The sort does happen after the match stage. The query plan doesn't show the SORT stage - that is because there is an index on the sort key updated_on. If you remove the index on the updated_on field you will see a SORT stage in the query plan (and it will be an in-memory sort).

See Explain Results - sort stage.

Some Ideas:

(i) You can use a compound index, instead of a two single field indexes:

{ organization: 1, updated_on: 1 }

It will work fine. See this topic on Sort and Non-prefix Subset of an Index.

(ii) Also, instead of an aggregation, a find() query can do the same job:

db.test.find( { organization : ObjectId("5a55b2f1aae2fe0ddd525827") } ).sort( { updated_on: 1 } )

NOTE: Do verify with explain() and see how they perform. Also, try using the executionStats mode.

mongo sort before match in aggregation

2 Answers2