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

Question

Assuming I have only male and females in my user collection. Is the following :

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

slower than this one :

User.find()

I feel like it would be, but I don't really know how MongoDB works internally. Both requests return the entire collection. I'm building a filter feature and I'd like to simplify my api code by considering that every call is filtered somehow.

Answer 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)

Answer 2

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
}