CODEWITHSUNDEEP
mongodb
Rubal
Rubal

Reputation: 1026

Mongodb find query behaviour understanding using explain

I am on MongoDB shell version: 3.0.15

I have been analyzing a query and I didn't understand the specific behavior

For: testcollection has two keys, "state" and "all"

db.testcollection.find( {"state" : 1}).explain(true)

the keysexamined and nreturned are correct: 2568 docs

but for 

db.testcollection.find( {"state" : 1}).limit(1000).explain(true)

the nreturned becomes 101 and keysexamined 102

Without index, a collscan is hit and returns 101 docs in nreturned. If proper index is added it hits for this query and ixscan is done with nreturned still 101

From my understanding and also from https://docs.mongodb.com/v3.0/core/cursors/index.html, I wouldn't have been surprised if without limit it returned me the default doc count of 101, but here, with limit explain shows nreturned 101 doc after restriction instead of 2568 docs, and when no limit is provided, nreturned has 101 docs in the explain query.

Why does limit lead into showing nreturned101 in explain query?

Upvotes: 0

Views: 156

Answers (1)

kevinadi
kevinadi

Reputation: 13775

I believe what you described aligned with the issue SERVER-24547, where it was stated that:

Explain("allPlansExecution") does not honour limit() if batchSize() is not specified

This issue affects MongoDB version 3.0.12 and later (in the 3.0 series), and this was fixed in newer versions of MongoDB (i.e. 3.2 and newer) as per SERVER-17577.

Please note that as of this writing, 3.0.15 is quite old and is not supported anymore. If this is a new deployment, it's best to start with a more recent version of MongoDB (currently 4.0.3) so you don't get bitten by an old bug that was fixed in later versions.

Upvotes: 1

Related Questions