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

Negations over multikey fields do not use index

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 2.6.1, 2.7.0
    • Affects Version/s: 2.6.0-rc0
    • Component/s: Querying
    • None
    • Major Change

      Issue Status as of April 15, 2014

      ISSUE SUMMARY
      Negation predicates like $ne or $not did not make use of an index if the index was a "multikey" index (where the indexed fields contained arrays).

      USER IMPACT
      Queries with negation predicates were scanning the entire collection, which is inefficient and can cause disruption in the working set.

      WORKAROUNDS
      In some cases, the query can be rewritten (for example with $gt and $lt predicates) to eliminate the negation predicate.

      RESOLUTION
      Multikey index usage has been enabled for negation predicates.

      AFFECTED VERSIONS
      All recent production releases up to and including 2.6.0 are affected.

      PATCHES
      The patch is included in the 2.6.1 production release.

      Original description

      This query does not seem to use an index at all even when it exists:

      db.Book.find({ "Ratings" : { "$ne" : null, "$not" : { "$size" : 0 } } } )
      

      Notice the DB (referenced below), has an index:

             {
                     "v" : 1,
                     "key" : {
                             "Ratings" : 1
                     },
                     "name" : "Ratings_1",
                     "ns" : "BookStore.Book"
             }
      

      Yet, when you explain the query, you see basic cursor:

      > db.Book.find({ "Ratings" : { "$ne" : null, "$not" : { "$size" : 0 } } } ).expl
      ain()
      {
              "cursor" : "BasicCursor",
              "isMultiKey" : false,
              "n" : 270171,
              "nscannedObjects" : 271380,
              "nscanned" : 271380,
              "nscannedObjectsAllPlans" : 271380,
              "nscannedAllPlans" : 271380,
              "scanAndOrder" : false,
              "indexOnly" : false,
              "nYields" : 2120,
              "nChunkSkips" : 0,
              "millis" : 559,
              "server" : "WIN-7S2IMPQ2TOE:27017",
              "filterSet" : false
      }
      

      To see this in action, open the BookStore.zip file from this issue:
      https://jira.mongodb.org/browse/SERVER-13065

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            mkennedy66996693 Michael Kennedy
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: