Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-15658

Query with $in and $nin doesn't use index correctly

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 2.6.4
    • Component/s: Performance, Querying
    • None
    • ALL

      When matching an attribute against both $in and $nin, Mongo doesn't use the index correctly.

      If only $in is used, then index takes advantage of that:

      db.assets.find({
        tags: {
          $in: ['blah']
        }
      }).explain()
      
      {
        "cursor" : "BtreeCursor tags_1",
        "isMultiKey" : true,
        "n" : 6,
        "nscannedObjects" : 6,
        "nscanned" : 6,
        "nscannedObjectsAllPlans" : 6,
        "nscannedAllPlans" : 6,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
          "tags" : [
            [ "blah", "blah" ]
          ]
        }
      }
      

      However, if $nin is involved, instead of finding documents that match $in and then filtering out those that don't pass the $nin condition, it scans all documents.

      db.assets.find({
        tags: {
          $in: ['blah'],
          $nin: ['cat']
        }
      }).explain()
      
      {
        "cursor" : "BtreeCursor tags_1",
        "isMultiKey" : true,
        "n" : 75760,
        "nscannedObjects" : 79974,
        "nscanned" : 1197016,
        "nscannedObjectsAllPlans" : 79974,
        "nscannedAllPlans" : 1197130,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 9351,
        "nChunkSkips" : 0,
        "millis" : 2331,
        "indexBounds" : {
          "tags" : [
            [ {"$minElement" : 1}, "cat" ],
            [ "cat", {"$maxElement" : 1} ]
          ]
        }
      }
      

      Related SO issue: http://stackoverflow.com/questions/26351714/query-with-in-and-nin-doesnt-use-index

            Assignee:
            Unassigned Unassigned
            Reporter:
            petrbela Petr Bela
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: