Why do we need an additional LIMIT stage with compound index in Mongo

Question

I am using Mongo 4.2 (stuck with this) and have a collection say "product_data" with documents with the following schema:

_id:"2lgy_itmep53vy"
uIdHash:"2lgys2yxouhug5xj3ms45mluxw5hsweu"
userTS:1494055844000

Case 1: With this, I have the following indexes for the collection:

1. _id:Regular - Unique
2. uIdHash: Hashed

I tried to execute

db.product_data.find( {"uIdHash":"2lgys2yxouhug5xj3ms45mluxw5hsweu"}).sort({"userTS":-1}).explain()

and these are the stages in result:

Ofcourse, I could realize that it would make sense to have an additional compound index to avoid the mongo in-memory 'Sort' stage.

Case 2: Now I have attempted to add another index with those which were existing 3. {uIdHash:1 , userTS:-1}: Regular and Compound

Up to my expectation, the result of execution here was able to optimize on the sorting stage:

---

---

All good so far, now that I am looking to build for pagination on top of this query. I would need to limit the data queried. Hence the query further translates to

db.product_data.find( {"uIdHash":"2lgys2yxouhug5xj3ms45mluxw5hsweu"}).sort({"userTS":-1}).limit(10).explain()

The result for each Case now are as follows:

Case 1 Limit Result:

The in-memory sorting does less work (36 instead of 50) and returns the expected number of documents. Fair enough, a good underlying optimization in the stage.

Case 2 Limit Result: Surprisingly, with the index in use and the data queried, there is an additional Limit stage added to processing!

The doubts now I have are as follows:

1. Why do we need an additional stage for LIMIT, when we already have 10 documents retured from FETCH stage?
2. What would be the impact of this additional stage? Given that I need pagination, shall I stick with Case 1 indexes and not use the last compound index?

Answer 1

The query executor is able to perform some optimizations. One of these is that when there is a limit and no blocking stage (like a sort), when the limit is reached, all of the upstream stages can stop early.

This means that if there were no limit stage, the ixscan and fetch stages would have continued through all 24 matching documents.

There is no discreet limit stage with the non-index sort because it is combined with the sort stage.

Answer 2

Limit stage tells you that the database is limiting the result set. This means subsequent stages will work with less data.

Your question of "why do we need an additional stage for limit" doesn't make sense. You send the query to the database, and you do not use (or need) any stages. The database decides how to fulfill the query, if you asked it to limit the result set it does that and it communicates to you that it has done that, by telling you there is a limit stage in query processing.