CODEWITHSUNDEEP
mongodbquery-optimization
fazalerabbi
fazalerabbi

Reputation: 173

MongoDB Count Query Optimization

I have total 1644662 records in one of my collections, I have created index on one of my column named "document_type_id". When I run a query 

db.getCollection('my_collection').find({"document_type_id": {"$in": ["7"]}}).count()

It returns count "753800" in 0.285 secs. And When I run a query 

db.getCollection('my_collection').find({"document_type_id": {"$in": ["3"]}}).count()

It returns count "819438" in 0.365 secs. Both queries working perfect. But problem comes when I run query by combining both 

db.getCollection('my_collection').find({"document_type_id": {"$in": ["3", "7"]}}).count()

This query takes 54 secs to return the count. Execution Plan for third query is as follows:

{
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "test.my_collection",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "document_type_id" : {
            "$in" : [ 
                "3", 
                "7"
            ]
        }
    },
    "winningPlan" : {
        "stage" : "COUNT",
        "inputStage" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "document_type_id" : 1.0
                },
                "indexName" : "document_type_id_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "document_type_id" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "document_type_id" : [ 
                        "[\"3\", \"3\"]", 
                        "[\"7\", \"7\"]"
                    ]
                }
            }
        }
    },
    "rejectedPlans" : []
},
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 0,
    "executionTimeMillis" : 74166,
    "totalKeysExamined" : 1573238,
    "totalDocsExamined" : 1573238,
    "executionStages" : {
        "stage" : "COUNT",
        "nReturned" : 0,
        "executionTimeMillisEstimate" : 73504,
        "works" : 1573239,
        "advanced" : 0,
        "needTime" : 1573238,
        "needYield" : 0,
        "saveState" : 14007,
        "restoreState" : 14007,
        "isEOF" : 1,
        "invalidates" : 0,
        "nCounted" : 1573238,
        "nSkipped" : 0,
        "inputStage" : {
            "stage" : "FETCH",
            "nReturned" : 1573238,
            "executionTimeMillisEstimate" : 73253,
            "works" : 1573239,
            "advanced" : 1573238,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 14007,
            "restoreState" : 14007,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 1573238,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1573238,
                "executionTimeMillisEstimate" : 2729,
                "works" : 1573239,
                "advanced" : 1573238,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 14007,
                "restoreState" : 14007,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "document_type_id" : 1.0
                },
                "indexName" : "document_type_id_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "document_type_id" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "document_type_id" : [ 
                        "[\"3\", \"3\"]", 
                        "[\"7\", \"7\"]"
                    ]
                },
                "keysExamined" : 1573238,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    }
},
"serverInfo" : {
    "host" : "nb-fara",
    "port" : 27017,
    "version" : "3.4.9",
    "gitVersion" : "876ebee8c7dd0e2d992f36a848ff4dc50ee6603e"
},
"ok" : 1.0

}

Upvotes: 4

Views: 3367

Answers (5)

user9251303
user9251303

Reputation:

Have you tried the aggregate framework?

db.my_collection.aggregate( [
   { $project : { "document_type_id" : 1 }},
   { $match : { "document_type_id": { "$in": ["3", "7"] } } },
   { $group : { _id : '$document_type_id', count: { $sum: 1} } }
])

Upvotes: 0

Kevin Smith
Kevin Smith

Reputation: 14476

Looks like MongoDb is using the Mulikey Index but is only using it to lookup the Min/Max bounds due to it being a $in query, you can read more here - https://docs.mongodb.com/manual/core/multikey-index-bounds/#intersect-bounds-for-multikey-index

Changing it to a or should work

db.my_collection.count({"$or": [{"document_type_id": "3"}, {"document_type_id": "7"}]})

Upvotes: 0

M. Gopal
M. Gopal

Reputation: 454

With db version v3.0.7 getting below 

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.test",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "document_type_id" : {
                "$in" : [
                    "3",
                    "7"
                ]
            }
        },
        "winningPlan" : {
            "stage" : "COUNT",
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "document_type_id" : {
                        "$in" : [
                            "3",
                            "7"
                        ]
                    }
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 681,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 2000100,
        "executionStages" : {
            "stage" : "COUNT",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 630,
            "works" : 2000102,
            "advanced" : 0,
            "needTime" : 2000101,
            "needFetch" : 0,
            "saveState" : 15625,
            "restoreState" : 15625,
            "isEOF" : 1,
            "invalidates" : 0,
            "nCounted" : 400020,
            "nSkipped" : 0,
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "document_type_id" : {
                        "$in" : [
                            "3",
                            "7"
                        ]
                    }
                },
                "nReturned" : 400020,
                "executionTimeMillisEstimate" : 600,
                "works" : 2000101,
                "advanced" : 400020,
                "needTime" : 1600081,
                "needFetch" : 0,
                "saveState" : 15625,
                "restoreState" : 15625,
                "isEOF" : 1,
                "invalidates" : 0,
                "direction" : "forward",
                "docsExamined" : 2000100
            }
        }
    },
    "serverInfo" : {
        "host" : "ptpll354",
        "port" : 5000,
        "version" : "3.0.7",
        "gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
    },
    "ok" : 1
}

Upvotes: 0

felix
felix

Reputation: 9295

You should use 

db.collection.count({...})

instead of 

db.collection.find({...}).count()

because count() can directly take a query as parameter, see db.collection.count() for details

So your query would be: 

db.getCollection('my_collection').count({"document_type_id": {"$in": ["3", "7"]}})

Here is the explain of this query (finished in ~0.202s on my machine with MongoDB 3.6.2): 

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.test",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "document_type_id" : {
                "$in" : [
                    "3",
                    "7"
                ]
            }
        },
        "winningPlan" : {
            "stage" : "COUNT",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "document_type_id" : 1
                },
                "indexName" : "document_type_id_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "document_type_id" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "document_type_id" : [
                        "[\"3\", \"3\"]",
                        "[\"7\", \"7\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 0,
        "executionTimeMillis" : 202,
        "totalKeysExamined" : 274112,
        "totalDocsExamined" : 0,
        "executionStages" : {
            "stage" : "COUNT",
            "nReturned" : 0,
            "executionTimeMillisEstimate" : 170,
            "works" : 274112,
            "advanced" : 0,
            "needTime" : 274111,
            "needYield" : 0,
            "saveState" : 2141,
            "restoreState" : 2141,
            "isEOF" : 1,
            "invalidates" : 0,
            "nCounted" : 274110,
            "nSkipped" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 274110,
                "executionTimeMillisEstimate" : 140,
                "works" : 274112,
                "advanced" : 274110,
                "needTime" : 1,
                "needYield" : 0,
                "saveState" : 2141,
                "restoreState" : 2141,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "document_type_id" : 1
                },
                "indexName" : "document_type_id_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "document_type_id" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "document_type_id" : [
                        "[\"3\", \"3\"]",
                        "[\"7\", \"7\"]"
                    ]
                },
                "keysExamined" : 274112,
                "seeks" : 2,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "xxxxxxx",
        "port" : 27017,
        "version" : "3.6.2",
        "gitVersion" : "489d177dbd0f0420a8ca04d39fd78d0a2c539420"
    },
    "ok" : 1
}

Upvotes: 4

udaykumar vangari
udaykumar vangari

Reputation: 91

db.getCollection('my_collection').find({"document_type_id": {"$or": ["3", "7"]}}).count()

Upvotes: 1

Related Questions