MongoDB, can query fields slow down a query even if they form a partition? MongoDB, can query fields slow down a query even if they form a partition? mongoose mongoose

MongoDB, can query fields slow down a query even if they form a partition?


it is a good question as it touches basic query planning capabilites.Comparing explain results we can see that using IN invokes collection scan by specified query parameter - which is more expensive than basic document dump, when querying without parameters.

db.User.find({ gender: { $in: ['male','female'] }}).explain("executionStats")

{    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "test.User",        "indexFilterSet" : false,        "parsedQuery" : {            "gender" : {                "$in" : [                     "female",                     "male"                ]            }        },        "winningPlan" : {            "stage" : "COLLSCAN",            "filter" : {                "gender" : {                    "$in" : [                         "female",                         "male"                    ]                }            },            "direction" : "forward"        },        "rejectedPlans" : []    },    "executionStats" : {        "executionSuccess" : true,        "nReturned" : 24,        "executionTimeMillis" : 0,        "totalKeysExamined" : 0,        "totalDocsExamined" : 24,        "executionStages" : {            "stage" : "COLLSCAN",            "filter" : {                "gender" : {                    "$in" : [                         "female",                         "male"                    ]                }            },            "nReturned" : 24,            "executionTimeMillisEstimate" : 0,            "works" : 26,            "advanced" : 24,            "needTime" : 1,            "needYield" : 0,            "saveState" : 0,            "restoreState" : 0,            "isEOF" : 1,            "invalidates" : 0,            "direction" : "forward",            "docsExamined" : 24        }    },    "serverInfo" : {        "host" : "greg",        "port" : 27017,        "version" : "3.2.3",        "gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"    },    "ok" : 1}

db.User.find().explain("executionStats")

{    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "test.User",        "indexFilterSet" : false,        "parsedQuery" : {            "$and" : []        },        "winningPlan" : {            "stage" : "COLLSCAN",            "filter" : {                "$and" : []            },            "direction" : "forward"        },        "rejectedPlans" : []    },    "executionStats" : {        "executionSuccess" : true,        "nReturned" : 24,        "executionTimeMillis" : 0,        "totalKeysExamined" : 0,        "totalDocsExamined" : 24,        "executionStages" : {            "stage" : "COLLSCAN",            "filter" : {                "$and" : []            },            "nReturned" : 24,            "executionTimeMillisEstimate" : 0,            "works" : 26,            "advanced" : 24,            "needTime" : 1,            "needYield" : 0,            "saveState" : 0,            "restoreState" : 0,            "isEOF" : 1,            "invalidates" : 0,            "direction" : "forward",            "docsExamined" : 24        }    },    "serverInfo" : {        "host" : "greg",        "port" : 27017,        "version" : "3.2.3",        "gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"    },    "ok" : 1}


When querying without a condition, it return all the documents without checking. But if you and a condition. Simply it compile the condition into BSON and match with the data in the database, Which is slower. But if you create an index on gender. You can not see any difference in time (in both cases)