MongoDB ixscan seems to be scanning more objects than expected

indexmongodboptimization

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?

Best Answer

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
Related Question