From the documentation on Mongos (mongos — MongoDB Manual) the behavior when using skip & limit in query is,
If the query limits the size of the result set using the
limit() cursor method, the
mongos instance passes that limit to the shards and then re-applies the limit to the result before returning the result to the client.
Is this behavior true for even unsharded collection?
This behavior can introduce a performance degradation as we increase the skip value for unsharded collections. In my case I have a collection with 1 Million documents and we try to fetch around 10K documents using skip and limiting to 1000 documents at each attempt. During the last page/iteration the skip value would be 9000 and limit value would be 1000. But the router would attempt to fetch 10K documents from the shard server. These 10K documents have to be read from disk which worsens the performance.
Can we force the router to perform the skip and limit in the shard server itself to mitigate this?