$exists works slowly

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Duplicate
    • Priority: Major - P3
    • None
    • Affects Version/s: 4.2.10
    • Component/s: Index Maintenance, Querying
    • None
    • ALL
    • Hide

      db.test.insertMany([

      {"name" : "0_0", "status" : 2.0 }

      ,

      {"name" : "0_1", "status" : 2.0 }

      ,

      {"name" : "0_2"}

      ,

      {"name" : "0_3", "status" : 2.0}

      ,

      {"name" : "0_4"}

      ,

      {"name" : "0_5"}

      ,

      {"name" : "0_6", "status" : 2.0}

      ,

      {"name" : "0_7", "status" : 3.0}

      ]);

      db.test.createIndex({ status: 1});

      db.test.find({status: 2}, {}).explain();

      db.test.find({status: {$exists: false}}, {}).explain();

      Show
      db.test.insertMany([ {"name" : "0_0", "status" : 2.0 } , {"name" : "0_1", "status" : 2.0 } , {"name" : "0_2"} , {"name" : "0_3", "status" : 2.0} , {"name" : "0_4"} , {"name" : "0_5"} , {"name" : "0_6", "status" : 2.0} , {"name" : "0_7", "status" : 3.0} ]); db.test.createIndex({ status: 1}); db.test.find({status: 2}, {}).explain(); db.test.find({status: {$exists: false}}, {}).explain();
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      If you're doing a query with $exists, it takes much more time than doing a query by value.

      Example query: db.getCollection('test').find({status: {$exists: false}}, {}).count()
      Explain looks like this:
      "winningPlan" : {
      "stage" : "FETCH",
      "filter" : {
      "status" : {
      "$not" :

      { "$exists" : true }

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

      { "status" : 1.0 }

      ,
      "indexName" : "status_1",
      "isMultiKey" : false,
      "multiKeyPaths" :

      { "status" : [] }

      ,
      "isUnique" : false,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "forward",
      "indexBounds" :

      { "status" : [ "[null, null]" ]}

      }},

      So, there is IXSCAN and next FETCH with filter. Why filter is here?

      This is explain for db.getCollection('test').find({status: 1}, {}).count():
      "queryHash" : "E6304EB6",
      "planCacheKey" : "7A94191B",
      "winningPlan" : {
      "stage" : "FETCH",
      "inputStage" : {
      "stage" : "IXSCAN",
      "keyPattern" :

      { "status" : 1.0 }

      ,
      "indexName" : "status_1",
      "isMultiKey" : false,
      "multiKeyPaths" :

      { "status" : [] }

      ,
      "isUnique" : false,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "forward",
      "indexBounds" :

      { "status" : [ "[1.0, 1.0]" ]}

      }},

      As you can see, there are no filter in FETCH, and this query is faster.
      I tried it in cloud.mongodb.com on 4.2.10.

            Assignee:
            Unassigned
            Reporter:
            Дмитрий Аганов
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: