0

I'm using MongoDB 2.6.12 and a collection has the following index {a: 1, d: -1}. In case the types matter a is a Number and d is a Timestamp.

From what I can see in the code we are limiting this query to 21 documents. However, the query log below suggests that the index was used but it still scanned 903483 documents.

2016-05-27T22:13:05.722-0700 [conn890] query my.collection query: { $query: { a: 12345 }, $orderby: { d: -1 } } planSummary: IXSCAN { a: 1, d: -1 } cursorid:312034207299 ntoreturn:21 ntoskip:903462 nscanned:903483 nscannedObjects:903483 keyUpdates:0 numYields:89 locks(micros) r:3550704 nreturned:21 reslen:1028 1820ms

We are using pymongo 2.8.1 and mongengine 0.7.9

Can anyone explain why and if we are actually scanning that large number of objects in this query and what I should look for to solve the problem?

2
  • Thinking about this some more, it could be because we're paging through this collection and when we provide a large offset the query needs to scan through until it gets to the page it wants.
    – kerryjj
    May 28 '16 at 10:37
  • Large skip values are definitely a performance issue; what is your use case for having such a large skip value (903,462 in the example query)?
    – Stennie
    May 31 '16 at 15:00
1

The large number of nscanned key comparisons is explainable by the skip value: the query is skipping 903,462 documents (ntoskip) in order to return 21 (ntoreturn). The nscanned value in your output is the sum of ntoskip and ntoreturn.

The number of nscannedObjects (identical to nscanned) is because the skip stage in MongoDB 2.6.x query processing happens after the document fetch and projection stages.

There are a few suggested optimizations for skip performance you can watch and upvote in the MongoDB issue tracker:

In general, large values of skip are discouraged (see: cursor.skip() documentation).

Depending on your use case for skip(), alternative approaches to consider include:

  • use range-based queries for pagination, if possible
  • limit pagination for your API/UI (a skip value of 903,462 documents with 21 per page implies this query might be for page number 43,023 of results)
  • use $sample (MongoDB 3.2+) or an alternative approach for fetching/sampling random documents

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.