Why is MongoDB using scanAndOrder here?

Question

I am saving games documents in MongoDB. Among other things the documents contain the name of the player (name), the time when the game has ended (endMS) and the type of the game (type). Type can have one out of five different values.

I need to search for all finished games by a player sorted by the time when the game has ended and for all finished games by a player with a certain game type also sorted by the time of the game end.

Examples for both queries are

db.games.find({name:"Stefan",endMS:{$gt:0}}).sort({endMS:-1})

and

db.games.find({name:"Stefan",type:"bli",endMS:{$gt:0}}).sort({endMS:-1})

You can use the indexes

db.games.ensureIndex({name:1,endMS:-1})

and

db.games.ensureIndex({name:1,type:1,endMS:-1})

for fast access.

Now I am trying to get along with just one index:

db.games.ensureIndex({name:1,endMS:-1,type:1})

The first query or course still runs fine. The idea for the second query is that Mongo might need to skip some entries when scanning the index but only need to access the documents that are finally returned by the query because the "type" can already be checked in the index. That should be fast enough for my needs.

However using explain() MongoDB tells me that "scanAndOrder" is needed when querying the database like this.

db.games.find({name:"Stefan",type:"bli",endMS:{$gt:0}}).sort({endMS:-1}).explain()

{
    "cursor" : "BtreeCursor name_1_endMS_-1_type_1",
    "isMultiKey" : false,
    "n" : 1,
    "nscannedObjects" : 1,
    "nscanned" : 22,
    "nscannedObjectsAllPlans" : 4,
    "nscannedAllPlans" : 25,
    "scanAndOrder" : true,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "name" : [
            [
                "Stefan",
                "Stefan"
            ]
        ],
        "endMS" : [
            [
                Infinity,
                0
            ]
        ],
        "type" : [
            [
                "bli",
                "bli"
            ]
        ]
    },
    "server" : "localhost:27017",
    "filterSet" : false
}

nscannedObjects and nscanned are as expected like described above, but I am wondering why Mongo says scanAndOrder:true.

According to the docs: "scanAndOrder is a boolean that is true when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor."

As far as I have understood it the documents should be ordered in the index, only some need to be skipped which doesn't affect the order.

So why is MongoDB using scanAndOrder here?

Answer 1

This seems to be a bug in MongoDB 2.6.0-rc0. All works as expected in MongoDB 2.4.9.