20

Today I have noticed that the order in which the $lt and $gt operators are given seem to matter in MongoDB 2.0.2.

I have a database of games. "player" is an array of two strings representing both players, "endedAtMS" is a timestamp when the game has ended. I have created this index:

db.games.ensureIndex({player:1,endedAtMS:-1})

To get 30 of my games which were finished in a certain time range, ordered by the time the games where finished, I do:

db.games.find({ "player" : "Stefan" , 
                "endedAtMS" : { "$lt" : 1321284969946 , 
                                "$gt" : 1301284969946}}).
         sort({endedAtMS:-1}).
         limit(30).
         explain()

{
    "cursor" : "BtreeCursor player_1_endedAtMS_-1",
    "nscanned" : 30,
    "nscannedObjects" : 30,
    "n" : 30,
    "millis" : 0,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
        "player" : [
            [
                "Stefan",
                "Stefan"
            ]
        ],
        "endedAtMS" : [
            [
                1321284969946,
                -1.7976931348623157e+308
            ]
        ]
    }
}

All seems to work fine. However when I change the order of $lt and $gt in the query above I get this:

db.games.find({ "player" : "Stefan" , 
                "endedAtMS" : { "$gt":1301284969946, 
                                "$lt" : 1321284969946}}).
         sort({endedAtMS:-1}).
         limit(30).
         explain()

{
    "cursor" : "BtreeCursor player_1_endedAtMS_-1",
    "nscanned" : 126,
    "nscannedObjects" : 126,
    "n" : 30,
    "millis" : 1,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "isMultiKey" : true,
    "indexOnly" : false,
    "indexBounds" : {
        "player" : [
            [
                "Stefan",
                "Stefan"
            ]
        ],
        "endedAtMS" : [
            [
                1.7976931348623157e+308,
                1301284969946
            ]
        ]
    }
}

As you can see 126 docs need to be scanned to get the 30 docs for the result. If you take a look at the indexBounds in the explain output it seems that only the first operator is used to limit the search space in the index.

What do I miss? Why is Mongo only using one operator to limit the search space?

Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
StefanMK
  • 1,313
  • 1
  • 12
  • 22
  • Good find! Let's wait for 10gen guys :) – Sergio Tulentsev Mar 27 '12 at 19:11
  • I've come across the same problem (bug?) with 2.0.3. See [my question](http://stackoverflow.com/questions/9776383/why-are-any-objects-being-scanned-here). 10gen - we love you - please explain this! – Nic Cottrell Mar 27 '12 at 21:06
  • I wonder how soon the 10gen guys can figure this out ... have a feeling this would call for 2.0.4 – Baba Mar 27 '12 at 23:47
  • what are the results when you introduce an $and into the query db.games.find({ "player" : "Stefan" , "endedAtMS" : { $and [ {"$gt" : 1301284969946}, {"$lt" : 1321284969946}]}}). sort({endedAtMS:-1}). limit(30). explain() – JB. Mar 28 '12 at 07:41
  • Using $and is the same. db.games.find({ "player" : "Stefan" , $and : [{"endedAtMS" : {"$gt" : 1301284969946}},   {"endedAtMS" : {"$lt" : 1321284969946}}]}).sort({endedAtMS:-1}).limit(30).explain() scans 126 objects, exchanging $lt and $gt scans 30 objects. – StefanMK Mar 28 '12 at 09:48

2 Answers2

9

This is a known issue. The short answer is that it has to do with the fact that a multikey index is used ("player" is an array), and the index cannot be constrained on both upper and lower bounds.

This is explained in more detail in the Jira case: https://jira.mongodb.org/browse/SERVER-4155 - "Index bound incorrect?"

There is an open Jira ticket to improve this behavior: https://jira.mongodb.org/browse/SERVER-4180 - "Wrong indexbounds picked for a date range query (regression)" which is slated to be released in version 2.1.2 (this version is subject to change). Please vote for it!

Marc
  • 5,488
  • 29
  • 18
  • 1
    Thanks for your answer, however I am not sure if I have understood why the index cannot be contrained because of the array. If you add an entry in the index for each element in the array it shouldn't matter. Anyway, limiting the index from both sides would only partly help here. If Mongo still picks the wrong end of the limit to start searching it has to search 65 documents in my example above. I guess the main issue here is to pick the correct end to start searching, then the other index bound is not needed at all to find the 30 docs for the result with only 30 scans. – StefanMK Apr 01 '12 at 20:25
  • 1
    Take the example from Jira case: https://jira.mongodb.org/browse/SERVER-4155: If the index bounds [2, 9] are used for a, then the document will not be returned. To prevent this, only the first bound is used. The first bound is chosen for convenience, there is no logic performed. As mentioned in my answer above, we are working to improve this behavior. – Marc Apr 03 '12 at 14:36
1

This has been fixed in version '2.1.2'.

As per: https://jira.mongodb.org/browse/SERVER-4180

AWESOME!

Salar Khan
  • 457
  • 3
  • 13