I have a query that seems to never return.
When I run explain
on that query, it shows me executionStats.executionTimeMillis
of 27ms, and that the initial input-stage is IXSCAN
that should return 4 objects only.
I've confirmed that querying for the input-stage query returns only 4 results.
This is my query:
{"$or":[
{"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},"field2.key":"foobar"},
{"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},"field3.key":"foobar"}
]}
This is the explain({ verbose : "executionStats" })
output (sorry for the long paste):
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "...",
"plannerVersion" : 1,
"indexFilterSet" : false,
"parsedQuery" : { ... },
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "OR",
"inputStages" : [
{
"stage" : "FETCH",
"filter" : {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : { "field3.key" : 1.0 },
"indexName" : "field3.key_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"field3.key" : [ "[\"foobar\", \"foobar\"]" ]
}
}
},
{
"stage" : "FETCH",
"filter" : {"field1.key":{"$in":["name1","name2",/^prefix.*suffix$/]},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : { "field2.key" : 1.0 },
"indexName" : "field2.key_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"field2.key" : [ "[\"foobar\", \"foobar\"]" ]
}
}
}
]
}
},
"rejectedPlans" : []
},
...
// same plan for the 3 other shards
...
]
}
},
"executionStats" : {
"nReturned" : 0,
"executionTimeMillis" : 27,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "SHARD_MERGE",
"nReturned" : 0,
"executionTimeMillis" : 27,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"totalChildMillis" : NumberLong(63),
...
// execution times for each shard
...
},
"allPlansExecution" : []
},
"ok" : 1.0
}
UPDATE
It seems that despite explain
mentioning it uses "field2.key"
for the first part of the $or
and "field3.key"
for the second part of the $or
, when looking at db.currentOp().inprog
it shows:
"planSummary": "IXSCAN { field1.key: 1.0 }, IXSCAN { field3.key: 1.0 }"
so it selected the wrong index for one of the $or
parts, and thus making the query scan a huge number of documents.
- Any idea why
explain
gets the indexes right, but the query itself doesn't? - How can we
hint
mongo to use the correct indexes, when using$or
?