I have a large collection with 4 shards.
When I run a query over an indexed array field "array.number" like so:
var query = { "array" : { $elemMatch: { "number" : { $gte : "10", $lt : "20" } } } };
and check explain, I'll get these winning plans (abbreviated for clarity):
Shards 0/2/3:
"inputStage": {
"stage": "IXSCAN",
...
"isMultiKey": true,
"indexBounds": {
"array.number": [
"[\"10\", {})"
]
}
}
Shard1:
"inputStage": {
"stage": "IXSCAN",
...
"isMultiKey": true,
"multiKeyPaths": {
"array.number": [
"array"
]
},
"indexBounds": {
"array.number": [
"[\"10\", \"20\")"
]
}
}
So shard1 gives the expected optimal use of the index, limiting the inputStage to go over 10-20 only, while the other shards only use the lower bound on the index. The only difference between the shards objects is the multiKeyPaths part, which is missing in shards 0/2/3.
Any idea why is that, and how we can cause our other shards to properly use our index?
UPDATE
Here's the full explain response for the following query:
var query = { "array" : { $elemMatch: { "number" : { $gte : "10", $lt : "20" } } } };
db.collection.find(query).explain()
Response:
{
"queryPlanner" : {
"mongosPlannerVersion" : 1,
"winningPlan" : {
"stage" : "SHARD_MERGE",
"shards" : [
{
"shardName" : "company_rs0",
"connectionString" : "company_rs0/shard0-db0:27017,shard0-db1:27017",
"serverInfo" : {"host":"shard0-db0","port":27017,"version":"3.4.7","gitVersion":"cf38c1b8a0a8dca4a11737581beafef4fe120bcd"},
"plannerVersion" : 1,
"namespace" : "company_database.collection",
"indexFilterSet" : false,
"parsedQuery" : {"array":{"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"array" : {
"$elemMatch" : {"$and":[{"number":{"$gte":"10"}},{"number":{"$lt":"20"}}]}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"10\", {})"]}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"\", \"20\")"]}
}
}
]
},
{
"shardName" : "company_rs1",
"connectionString" : "company_rs1/shard1-db0:27017,shard1-db1:27017",
"serverInfo" : {"host":"shard1-db0","port":27017,"version":"3.4.7","gitVersion":"cf38c1b8a0a8dca4a11737581beafef4fe120bcd"},
"plannerVersion" : 1,
"namespace" : "company_database.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"multiKeyPaths" : {"array.number":["array"]},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"10\", \"20\")"]}
}
},
"rejectedPlans" : []
},
{
"shardName" : "company_rs2",
"connectionString" : "company_rs2/shard2-db0:27017,shard2-db1:27017",
"serverInfo" : {"host":"shard2-db0","port":27017,"version":"3.4.7","gitVersion":"cf38c1b8a0a8dca4a11737581beafef4fe120bcd"},
"plannerVersion" : 1,
"namespace" : "company_database.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$gte":"10"}},{"number":{"$lt":"20"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"10\", {})"]}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"\", \"20\")"]}
}
}
]
},
{
"shardName" : "company_rs3",
"connectionString" : "company_rs3/shard3-db0:27017,shard3-db1:27017",
"serverInfo" : {"host":"shard3-db0","port":27017,"version":"3.4.7","gitVersion":"cf38c1b8a0a8dca4a11737581beafef4fe120bcd"},
"plannerVersion" : 1,
"namespace" : "company_database.collection",
"indexFilterSet" : false,
"parsedQuery" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$gte":"10"}},{"number":{"$lt":"20"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"10\", {})"]}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"array" : {"$elemMatch":{"$and":[{"number":{"$lt":"20"}},{"number":{"$gte":"10"}}]}}
},
"inputStage" : {
"stage" : "IXSCAN",
"numberPattern" : {"array.number":1.0},
"indexName" : "array.number_1",
"isMultiKey" : true,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {"array.number":["[\"\", \"20\")"]}
}
}
]
}
]
}
},
"ok" : 1.0
}
