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

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor - P4
    • Resolution: Duplicate
    • Affects Version/s: 2.6.4
    • Fix Version/s: None
    • Component/s: Performance, Querying
    • Labels:
      None
    • Operating System:
      ALL

      Description

      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

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: