First creating 1 million documents:
> for(i=0; i<100; i++) { for(j=0; j<100; j++) {x = []; for(k=0; k<100; k++) { x.push({a:i, b:j, c:k, _id:(100*100*i+100*j+k) } ) }; db.example.insert(x) } }
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 100,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
>
We have added 1 million documents to the collection, colleciton look like:
> db.example.find()
{ "_id" : 0, "a" : 0, "b" : 0, "c" : 0 }
{ "_id" : 1, "a" : 0, "b" : 0, "c" : 1 }
{ "_id" : 2, "a" : 0, "b" : 0, "c" : 2 }
{ "_id" : 3, "a" : 0, "b" : 0, "c" : 3 }
{ "_id" : 4, "a" : 0, "b" : 0, "c" : 4 }
{ "_id" : 5, "a" : 0, "b" : 0, "c" : 5 }
{ "_id" : 6, "a" : 0, "b" : 0, "c" : 6 }
{ "_id" : 7, "a" : 0, "b" : 0, "c" : 7 }
{ "_id" : 8, "a" : 0, "b" : 0, "c" : 8 }
{ "_id" : 9, "a" : 0, "b" : 0, "c" : 9 }
{ "_id" : 10, "a" : 0, "b" : 0, "c" : 10 }
{ "_id" : 11, "a" : 0, "b" : 0, "c" : 11 }
{ "_id" : 12, "a" : 0, "b" : 0, "c" : 12 }
{ "_id" : 13, "a" : 0, "b" : 0, "c" : 13 }
{ "_id" : 14, "a" : 0, "b" : 0, "c" : 14 }
{ "_id" : 15, "a" : 0, "b" : 0, "c" : 15 }
{ "_id" : 16, "a" : 0, "b" : 0, "c" : 16 }
{ "_id" : 17, "a" : 0, "b" : 0, "c" : 17 }
{ "_id" : 18, "a" : 0, "b" : 0, "c" : 18 }
{ "_id" : 19, "a" : 0, "b" : 0, "c" : 19 }
Type "it" for more
> it
{ "_id" : 20, "a" : 0, "b" : 0, "c" : 20 }
{ "_id" : 21, "a" : 0, "b" : 0, "c" : 21 }
{ "_id" : 22, "a" : 0, "b" : 0, "c" : 22 }
{ "_id" : 23, "a" : 0, "b" : 0, "c" : 23 }
{ "_id" : 24, "a" : 0, "b" : 0, "c" : 24 }
{ "_id" : 25, "a" : 0, "b" : 0, "c" : 25 }
{ "_id" : 26, "a" : 0, "b" : 0, "c" : 26 }
{ "_id" : 27, "a" : 0, "b" : 0, "c" : 27 }
{ "_id" : 28, "a" : 0, "b" : 0, "c" : 28 }
{ "_id" : 29, "a" : 0, "b" : 0, "c" : 29 }
{ "_id" : 30, "a" : 0, "b" : 0, "c" : 30 }
{ "_id" : 31, "a" : 0, "b" : 0, "c" : 31 }
{ "_id" : 32, "a" : 0, "b" : 0, "c" : 32 }
{ "_id" : 33, "a" : 0, "b" : 0, "c" : 33 }
{ "_id" : 34, "a" : 0, "b" : 0, "c" : 34 }
{ "_id" : 35, "a" : 0, "b" : 0, "c" : 35 }
{ "_id" : 36, "a" : 0, "b" : 0, "c" : 36 }
{ "_id" : 37, "a" : 0, "b" : 0, "c" : 37 }
{ "_id" : 38, "a" : 0, "b" : 0, "c" : 38 }
{ "_id" : 39, "a" : 0, "b" : 0, "c" : 39 }
Now we are going to add indexes to the collection:
> db.example.createIndex({a:1, b:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
>
Creating another index on field b:
> db.example.createIndex({b:1})
> db.example.createIndex({b:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.example.getIndexes();
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "learn.example"
},
{
"v" : 1,
"key" : {
"a" : 1,
"b" : 1
},
"name" : "a_1_b_1",
"ns" : "learn.example"
},
{
"v" : 1,
"key" : {
"b" : 1
},
"name" : "b_1",
"ns" : "learn.example"
}
]
Getting an explainable object:
> exp = db.example.explain()
Explainable(learn.example)
Now seeing what are the various operations you can do with exp:
> exp.help()
Explainable operations
.aggregate(...) - explain an aggregation operation
.count(...) - explain a count operation
.distinct(...) - explain a distinct operation
.find(...) - get an explainable query
.findAndModify(...) - explain a findAndModify operation
.group(...) - explain a group operation
.remove(...) - explain a remove operation
.update(...) - explain an update operation
Explainable collection methods
.getCollection()
.getVerbosity()
.setVerbosity(verbosity)
It tells you all great thing you can do.
> exp.find({a:17, b:55}).sort({b:-1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : 17
}
},
{
"b" : {
"$eq" : 55
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
}
}
}
]
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
>
WinningPlan is what actually got chooses. We can see that it uses in IXSCAN and used a and b of the index and index used is a_1_b_1 which is an index on a,b. It's not a multi key index, direction is backword because of the sorting order. It shows the bound since we are looking for a single document so the bound is as:
"a" : ["[17.0, 17.0]"],
"b" : ["[55.0, 55.0]"]
Also shows the rejectedPlans, where is shows it considered using an index only on b but turn out to be a worst choice so turn off this option so it didn't do it.
We could have also got the same thing rather then creating an explanable object first by using command:
> db.example.explain().find({a:17, b:55}).sort({b:-1});
In earlier version of mongodb the option by which you run explain is calling explain after the find:
> db.example.find({a:17, b:55}).sort({b:-1}).explain();
example.find() bring back a cursor with explain set on it, but the result is same when you see it in shell.
Now why do mongodb change it up, why make a new approach of explanable object, so we change it because certain things won't return a cursor. For instance if I want to perform this old version, we cannot get an explain output:
> db.example.find({a:17, b:55}).sort({b:-1}).count().explain();
2016-11-12T19:34:34.909+0530 E QUERY [thread1] TypeError: db.example.find(...).sort(...).count(...).explain is not a function :
@(shell):1:1
On the other hand if I use my explain object and I do a count on that, I can actually get an explain output:
> exp.count()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"winningPlan" : {
"stage" : "COUNT"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
>
Now looking explainable object where it doesn't use an index.
> exp.find({c:200})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"c" : {
"$eq" : 200
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"c" : {
"$eq" : 200
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
This can the whole collection as there is no index on c.
> var cursor = db.example.find({a:99});
> cursor.explain();
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"a" : {
"$eq" : 99
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[99.0, 99.0]"
],
"b" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
I can actually call next on the cursor if I want the result.
> cursor.next()
{ "_id" : 990000, "a" : 99, "b" : 0, "c" : 0 }
> cursor.next()
{ "_id" : 990001, "a" : 99, "b" : 0, "c" : 1 }
> cursor.next()
{ "_id" : 990002, "a" : 99, "b" : 0, "c" : 2 }
executionStats verbosity mode of explain:
Creating explanable object with executionStats option:
> exp = db.example.explain("executionStats")
Explainable(learn.example)
Now running our query on a and b:
> exp.find({a:17, b:55})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : 17
}
},
{
"b" : {
"$eq" : 55
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 100,
"executionTimeMillis" : 162,
"totalKeysExamined" : 100,
"totalDocsExamined" : 100,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 100,
"executionTimeMillisEstimate" : 30,
"works" : 104,
"advanced" : 100,
"needTime" : 0,
"needYield" : 2,
"saveState" : 6,
"restoreState" : 6,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 100,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 100,
"executionTimeMillisEstimate" : 20,
"works" : 101,
"advanced" : 100,
"needTime" : 0,
"needYield" : 0,
"saveState" : 6,
"restoreState" : 6,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
},
"keysExamined" : 100,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
>
Once again we got the queryPlanner mode which tells us it is going to use a_1_b_1 index and shows the parse query. It also tells us the rejected plan and the rejected plan is to use the b index on this. But it doesn't tell what happen when it use all these which it is showing.
So the executionStats here will tell us the exectionstats of the winning plan and there are certain key item we must give attention to:
nReturned: 100 : number of document query actually return.
"executionTimeMillis" : 162
Time for the query 162 millisecond
"totalKeysExamined" : 100
"totalDocsExamined" : 100
It tells us index worked preety well, we looked at 100 keys and we got 100 documents.
Further in "inputStage" we can see number of documents returned at each stage. It shows the values index looked into in indexBounds, it shows indexName and keyPattern.
Now checking what happen if we do not have a_1_b_1 index. Dropping index:
{ "nIndexesWas" : 3, "ok" : 1 }
>
> exp.find({a:17, b:55})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : 17
}
},
{
"b" : {
"$eq" : 55
}
}
]
},
"winningPlan" : {
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 100,
"executionTimeMillis" : 661,
"totalKeysExamined" : 10000,
"totalDocsExamined" : 10000,
"executionStages" : {
"stage" : "KEEP_MUTATIONS",
"nReturned" : 100,
"executionTimeMillisEstimate" : 40,
"works" : 10199,
"advanced" : 100,
"needTime" : 9900,
"needYield" : 198,
"saveState" : 199,
"restoreState" : 199,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"nReturned" : 100,
"executionTimeMillisEstimate" : 40,
"works" : 10199,
"advanced" : 100,
"needTime" : 9900,
"needYield" : 198,
"saveState" : 199,
"restoreState" : 199,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 10000,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 10000,
"executionTimeMillisEstimate" : 40,
"works" : 10001,
"advanced" : 10000,
"needTime" : 0,
"needYield" : 0,
"saveState" : 199,
"restoreState" : 199,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
},
"keysExamined" : 10000,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
>
Now looking at the previos stats keys:
"winningPlan" shows it going to use the b index, with bound b:[55.0, 55.0]. It then going to pass that to up to FETCH stage of the query which fetches the final document, it going to look for the document a:17.
"executionTimeMillis" : 661
executionTime has increased. This query is slower than previous one.
"totalKeysExamined" : 10000,
"totalDocsExamined" : 10000,
We examinded 10000 documents and returned 10000 document and that's because the first stage of the document which is index scan of b returned 10000 document that has to be examined during the fetch stage.
This can be verified by inner most inputStage was the first stage run, that run query using index b and you can see it returned 10000 documents or we can see it returned pointer to 10000 document because index actually doesn't return 10000 documents.
Then in above inputStage we can see filter a:1 is applied on 10000 documents and they are whittle down to a final number of 100. "nReturned" : 100
And whenever you see number of documents examined much larger than the number of documents Returned, you know you did a lot of extra work that you look lot of extra data out of database and at some point it has to be inspected and then rejected before it returned to your actual client program.
That's all what happen when you give the option "executionStats".
"allPlansExecution" verbosity mode of explain():
Let's first re-create the index:
> db.example.createIndex({a:1, b:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
Creating new explainable object with allPlanExecution option.
> exp = db.example.explain("allPlansExecution")
Explainable(learn.example)
allPlansExecution: allPlansExecution does what a query optimizer does periodicaly. It runs all possible indexes that can be used and it run it in parallel and then makes the decision about which one is fastest and it periodicaly does it. And when it remember which one is faster for certain shape of query and then always uses that index.
But when you run the explain command in allPlansExecution you are running the query optimizer that periodicaly run to determine what index would be used for any particular shape of the query.
> exp.find({a:17, b:55})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "learn.example",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"a" : {
"$eq" : 17
}
},
{
"b" : {
"$eq" : 55
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "KEEP_MUTATIONS",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 100,
"executionTimeMillis" : 75,
"totalKeysExamined" : 100,
"totalDocsExamined" : 100,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 100,
"executionTimeMillisEstimate" : 0,
"works" : 102,
"advanced" : 100,
"needTime" : 0,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 3,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 100,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 100,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 100,
"needTime" : 0,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 3,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
},
"keysExamined" : 100,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
},
"allPlansExecution" : [
{
"nReturned" : 0,
"executionTimeMillisEstimate" : 30,
"totalKeysExamined" : 101,
"totalDocsExamined" : 101,
"executionStages" : {
"stage" : "KEEP_MUTATIONS",
"nReturned" : 0,
"executionTimeMillisEstimate" : 30,
"works" : 101,
"advanced" : 0,
"needTime" : 101,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 3,
"isEOF" : 0,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"a" : {
"$eq" : 17
}
},
"nReturned" : 0,
"executionTimeMillisEstimate" : 30,
"works" : 101,
"advanced" : 0,
"needTime" : 101,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 3,
"isEOF" : 0,
"invalidates" : 0,
"docsExamined" : 101,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 101,
"executionTimeMillisEstimate" : 3
"works" : 101,
"advanced" : 101,
"needTime" : 0,
"needYield" : 0,
"saveState" : 3,
"restoreState" : 3,
"isEOF" : 0,
"invalidates" : 0,
"keyPattern" : {
"b" : 1
},
"indexName" : "b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"b" : [
"[55.0, 55.0]"
]
},
"keysExamined" : 101,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
},
{
"nReturned" : 100,
"executionTimeMillisEstimate" : 0,
"totalKeysExamined" : 100,
"totalDocsExamined" : 100,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 100,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 100,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2,
"restoreState" : 2,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 100,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 100,
"executionTimeMillisEstimate" : 0,
"works" : 101,
"advanced" : 100,
"needTime" : 0,
"needYield" : 0,
"saveState" : 2,
"restoreState" : 2,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"a" : 1,
"b" : 1
},
"indexName" : "a_1_b_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[17.0, 17.0]"
],
"b" : [
"[55.0, 55.0]"
]
},
"keysExamined" : 100,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
]
},
"serverInfo" : {
"host" : "himanshu-PC",
"port" : 27017,
"version" : "3.2.1",
"gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},
"ok" : 1
}
>
We got lot more information. We have same executionStats as before. But we get a new thing: "allPlansExecution". This allPlansExecution is an array of different plans. The first one is actually rejected as its "nReturned" : 0 and the reason why its rejected is because database run the query using the b index and when it does that they examined 101 keys. We saw previously that when it uses b index and perform the query it examined 10000 documents and now it is examining 101 documents only. Why is that?
The reason is that the 101 is as many as the database need to see to know this approach of using b index, is a looser. You can see it didn't return any document "nReturned" : 100 and documents examined "totalDocsExamined" : 101. And I have a plan which is the second plan in array of allPlansExecution, which is going to return 100 documents and only look at the 100 documents and that's using a_1_b_1 index.
Now you can actually see the logic the database applied to make the decision which is as soon as it saw that using the index just on b going to look at more documents than using the a_1_b_1 index, it immediately abandoned that path and didn't follow it through it's completion.
So although this executionPlanStats gives you some execution information about alternative paths satisfying this query, it won't tell you everything that would happen because it treats just like the query optimizer because when query optimizer know it's bad it stops. And if you remember when we removed a_1_b_1 index it uses b index and examined lot more documents (10000).
It good to have an index for every query, that is every query should hit an index but it also important that every index that is on your collection there will be atleast one query hitting it. Which is to say that when you have an index on a collection and it's never chosen, it never selected, it's just a waste and you are wasting time inserting into that index and keeping that index upto date when infact you don't need to be.
So you want to have a good balances between indexes on your collection and queries so that all the indexes get used and all the query can have atleast one index that can satisfy them and work efficiently.
