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

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

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.0.3
    • Component/s: Querying
    • Labels:
      None
    • Query Optimization

      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.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            andrey.hohutkin@gmail.com Andrey Hohutkin
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: