$skip and $limit in aggregation framework

I found that it seems the sequence of limit and skip is immaterial. If I specify skip before limit, the mongoDB will make limit before skip under the hood.

> db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty()
{
    "op" : "command",
    "ns" : "archiprod.userinfos",
    "command" : {
        "aggregate" : "userinfos",
        "pipeline" : [
            {
                "$sort" : {
                    "updatedAt" : -1
                }
            },
            {
                "$limit" : 625
            },
            {
                "$skip" : 600
            }
        ],
    },
    "keysExamined" : 625,
    "docsExamined" : 625,
    "cursorExhausted" : true,
    "numYield" : 4,
    "nreturned" : 25,
    "millis" : 25,
    "planSummary" : "IXSCAN { updatedAt: -1 }",
    /* Some fields are omitted */
}

What happens if I swtich $skip and $limit? I got the same result in terms of keysExamined and docsExamined.

> db.system.profile.find().limit(1).sort( { ts : -1 } ).pretty()
{
    "op" : "command",
    "ns" : "archiprod.userinfos",
    "command" : {
        "aggregate" : "userinfos",
        "pipeline" : [
            {
                "$sort" : {
                    "updatedAt" : -1
                }
            },
            {
                "$skip" : 600
            },
            {
                "$limit" : 25
            }
        ],
    },
    "keysExamined" : 625,
    "docsExamined" : 625,
    "cursorExhausted" : true,
    "numYield" : 5,
    "nreturned" : 25,
    "millis" : 71,
    "planSummary" : "IXSCAN { updatedAt: -1 }",
}

I then checked the explain result of the query. I found that totalDocsExamined is already 625 in the limit stage.

> db.userinfos.explain('executionStats').aggregate([ { "$sort" : { "updatedAt" : -1 } }, { "$limit" : 625 }, { "$skip" : 600 } ])
{
    "stages" : [
        {
            "$cursor" : {
                "sort" : {
                    "updatedAt" : -1
                },
                "limit" : NumberLong(625),
                "queryPlanner" : {
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "updatedAt" : -1
                            },
                            "indexName" : "updatedAt_-1",
                        }
                    },
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 625,
                    "executionTimeMillis" : 22,
                    "totalKeysExamined" : 625,
                    "totalDocsExamined" : 625,
                    "executionStages" : {
                        "stage" : "FETCH",
                        "nReturned" : 625,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 625,
                        "advanced" : 625,
                        "docsExamined" : 625,
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "nReturned" : 625,
                            "works" : 625,
                            "advanced" : 625,
                            "keyPattern" : {
                                "updatedAt" : -1
                            },
                            "indexName" : "updatedAt_-1",
                            "keysExamined" : 625,
                        }
                    }
                }
            }
        },
        {
            "$skip" : NumberLong(600)
        }
    ]
}

And surprisingly, I found switching the $skip and $limit results in the same explain result.

> db.userinfos.explain('executionStats').aggregate([ { "$sort" : { "updatedAt" : -1 } }, { "$skip" : 600 }, { "$limit" : 25 } ])
{
    "stages" : [
        {
            "$cursor" : {
                "sort" : {
                    "updatedAt" : -1
                },
                "limit" : NumberLong(625),
                "queryPlanner" : {
                    /* Omitted */
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 625,
                    "executionTimeMillis" : 31,
                    "totalKeysExamined" : 625,
                    "totalDocsExamined" : 625,
                    /* Omitted */
                }
            }
        },
        {
            "$skip" : NumberLong(600)
        }
    ]
}

As you can see, even though I specified $skip before $limit, in the explain result, it's still $limit before $skip.

Answer: $skip before $limit

The order of $skip and $limit definitely matters, for aggregations at least. I just tried this, I don't know how it was missed, maybe it has changed since the op but I thought I would share.

I agree with @vkarpov15's comment in this conversation

In aggregate, $limit limits the number of documents sent to the next aggregation state, and $skip skips the first N documents, so if $skip is after $limit and $skip >= $limit, you won't get any results. In short, this is expected behavior in MongoDB.

Since this is a text search query we are talking about then the most optimal form is this:

db.collection.aggregate([
    { 
       "$match": {
               "$text": { "$search": "cake tea" }
    }
    },
    { "$sort": { "score": { "$meta": "textScore" } } },
    { "$limit": skip + limit },
    { "$skip": skip }
])

The rationale on the memory reserve from the top "sort" results will only work within it's own "limits" as it were and this will not be optimal for anything beyond a few reasonable "pages" of data.

Beyond what is reasonable for memory consumption, the additional stage will likely have a negative effect rather than positive.

These really are the practical limitations of the text search capabilities available to MongoDB in the current form. But for anything more detailed and requiring more performance, then just as is the case with many SQL "full text" solutions, you are better off using an external "purpose built" text search solution.