Question on Lab 2.2 explain()

I’m failing to understand the “explain()” output for the following query and index and would very much appreciate an explanation:

Query:

db.people.find({
“address.state”: “Connecticut”,
“birthday”: {
“$gte”: ISODate(“2010-01-01T00:00:00.000Z”),
“$lt”: ISODate(“2011-01-01T00:00:00.000Z”)
}
})

Index:

{ “job”: 1, “address.state”: 1, “last_name”: 1 }

The explain() output (below) indicates that the query uses the index (IXSCAN) but both my current understanding of indexing and the detailed answer indicate otherwise. Why would explain() show IXSCAN? Shouldn’t it show COLSCAN? Given that the query contains the fields “address.state” and “birthday”, I would expect the query not to match an index whose first key is “job”. I also note that the explain() output indicates that 50474 keys and 50474 documents are examined, which would indicate to me that a COLSCAN was actually performed. Again, why is COLSCAN not indicated in the explain() output? Thanks for any input.

{
“queryPlanner” : {
“plannerVersion” : 1,
“namespace” : “m201.people”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“address.state” : {
“$eq” : “Connecticut”
}
},
{
“birthday” : {
“$lt” : ISODate(“2011-01-01T00:00:00Z”)
}
},
{
“birthday” : {
“$gte” : ISODate(“2010-01-01T00:00:00Z”)
}
}
]
},
“queryHash” : “8CAD30B3”,
“planCacheKey” : “4FC5D866”,
“winningPlan” : {
“stage” : “FETCH”,
“filter” : {
“$and” : [
{
“address.state” : {
“$eq” : “Connecticut”
}
},
{
“birthday” : {
“$lt” : ISODate(“2011-01-01T00:00:00Z”)
}
},
{
“birthday” : {
“$gte” : ISODate(“2010-01-01T00:00:00Z”)
}
}
]
},
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“job” : 1,
“address.state” : 1,
“last_name” : 1
},
“indexName” : “job_1_address.state_1_last_name_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“job” : ,
“address.state” : ,
“last_name” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“job” : [
“[MinKey, MaxKey]”
],
“address.state” : [
“[MinKey, MaxKey]”
],
“last_name” : [
“[MinKey, MaxKey]”
]
}
}
},
“rejectedPlans” :
},
“executionStats” : {
“executionSuccess” : true,
“nReturned” : 96,
“executionTimeMillis” : 131,
“totalKeysExamined” : 50474,
“totalDocsExamined” : 50474,
“executionStages” : {
“stage” : “FETCH”,
“filter” : {
“$and” : [
{
“address.state” : {
“$eq” : “Connecticut”
}
},
{
“birthday” : {
“$lt” : ISODate(“2011-01-01T00:00:00Z”)
}
},
{
“birthday” : {
“$gte” : ISODate(“2010-01-01T00:00:00Z”)
}
}
]
},
“nReturned” : 96,
“executionTimeMillisEstimate” : 3,
“works” : 50475,
“advanced” : 96,
“needTime” : 50378,
“needYield” : 0,
“saveState” : 394,
“restoreState” : 394,
“isEOF” : 1,
“docsExamined” : 50474,
“alreadyHasObj” : 0,
“inputStage” : {
“stage” : “IXSCAN”,
“nReturned” : 50474,
“executionTimeMillisEstimate” : 3,
“works” : 50475,
“advanced” : 50474,
“needTime” : 0,
“needYield” : 0,
“saveState” : 394,
“restoreState” : 394,
“isEOF” : 1,
“keyPattern” : {
“job” : 1,
“address.state” : 1,
“last_name” : 1
},
“indexName” : “job_1_address.state_1_last_name_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“job” : ,
“address.state” : ,
“last_name” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“job” : [
“[MinKey, MaxKey]”
],
“address.state” : [
“[MinKey, MaxKey]”
],
“last_name” : [
“[MinKey, MaxKey]”
]
},
“keysExamined” : 50474,
“seeks” : 1,
“dupsTested” : 0,
“dupsDropped” : 0
}
}
},
“serverInfo” : {
“host” : “m042.university.mongodb”,
“port” : 27017,
“version” : “4.1.13”,
“gitVersion” : “441714bc4c70699950f3ac51a5cac41dcd413eaa”
},
“ok” : 1
}

Weird behaviour. You’re right about expecting a COLLSCAN and I’ve just re-tested this:

I completed this course last week and I remember testing all of them out during the lab and I got the expected results. Try to remove all indexes, close the connection, reconnect, add index, run your test again.

I did what you suggested and have discovered a couple of things. First off, I should say that I am using MongoDB 4.2. But here are my results:

In my prior test, I had used a hint with the suggested index, which seems to have forced the use of of that index { “job”: 1, “address.state”: 1, “last_name”: 1 } as evidenced by the number of keys scanned (50474).

When I run the test without a hint, I get the expected result. COLLSCAN is included, and zero keys are examined, i.e. no index is used.

It seems to me that Mongo should probably ignore the hint in this case since it cannot use the index, but I guess this is how it works.

Thanks for your reply, 007.

Ah you used a hint… now I can see why it behaved like that. It actually makes sense!

When you use a hint you are forcing the query engine to examine the index keys regardless of whether it can satisfy the query or not. In this case, it cannot satisfy the query so all the keys are examined. This is why we’re seeing that in addition to scanning all index keys it also scanned all the documents which in turn represents a COLLSCAN. The misleading part of the explain in this regard is "stage" : "IXSCAN". It really should read something else if a hint cannot fulfil the query.

NB: I’m also using 4.2 on my local machine.

1 Like