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

Answering "equality to null" predicates with an index requires a FETCH stage

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major - P3
    • Resolution: Works as Designed
    • 3.0.3
    • None
    • Querying
    • None

    Description

      I have a primary collection users:

      { _id: ObjectId, follow: [ObjectId], primary: ObjectId }

      primary field is optional in document
      I have a non-sparse index:

      { follow: 1, primary: 1 }

      When I try to query all followers without primary:
      db.users.count(

      { follow: ObjectId, primary: null }

      )
      it uses only follow field, fetches all these documents and then filters out documents where primary is null.

      Here is a result from explain:
      "winningPlan" : {
      "stage" : "COUNT",
      "inputStage" : {
      "stage" : "FETCH",
      "filter" : {
      "primary" :

      { "$eq" : null }

      },
      "inputStage" : {
      "stage" : "IXSCAN",
      "keyPattern" :

      { "follow" : NumberInt(1), "primary" : NumberInt(1) }

      ,
      "indexName" : "follow_1_primary_1",
      "isMultiKey" : true,
      "direction" : "forward",
      "indexBounds" :

      { "follow" : [ "[ObjectId('5382f987832983fb3a5ae9d4'), ObjectId('5382f987832983fb3a5ae9d4')]" ], "primary" : [ "[null, null]" ] }

      }
      }
      }

      It shows 'filter' stage that absolutely not needed.
      It drastically drops down a performance of an application.

      I checked that if I put primary as 0 - it works fine.
      The problem is query with null value.

      Attachments

        Issue Links

          Activity

            People

              backlog-query-optimization Backlog - Query Optimization
              andrey.hohutkin@gmail.com Andrey Hohutkin
              Votes:
              0 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: