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

Negations over multikey fields do not use index

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Fixed
    • Affects Version/s: 2.6.0-rc0
    • Fix Version/s: 2.6.1, 2.7.0
    • Component/s: Querying
    • Labels:
      None
    • Backwards Compatibility:
      Major Change
    • Backport Completed:

      Description

      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

        Attachments

          Activity

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: