[SERVER-52845] $exists works slowly Created: 13/Nov/20  Updated: 13/Nov/20  Resolved: 13/Nov/20

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 4.2.10
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Дмитрий Аганов Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-12869 Index null values and missing values ... Backlog
Operating System: ALL
Steps To Reproduce:

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();

Participants:

 Description   

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.



 Comments   
Comment by Eric Sedor [ 13/Nov/20 ]

Hi dmitriy.aganov@gmail.com,

SERVER-12869 describes an improvement that will help with the performance difference you are observing. Please watch that ticket for updates and comment there if you see anything you'd like to add.

Thank you,
Eric

Generated at Thu Feb 08 05:29:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.