The collection is a sharded collection over the hashed field. The following query should definitly be indexOnly but explain shows otherwise.
db.collection.ensureIndex({field : "hashed"})
db.collection.ensureIndex({field : 1, "field2" : 1, "field3" : 1})
db.collection.find(
{
field : 100
}
,{field : 1, _id : 0}
)
//.hint({ "field" : 1, "field2" : 1, "field3" : 1})
//.hint({ "field" : "hashed"})
.explain()
"cursor" : "BtreeCursor field_hashed",
"nscannedObjects" : 1,
"nscanned" : 1,
"indexOnly" : false,
I tested to hint both indexes but none of them generate a covered query. I would appreciate any help or suggestions.
explain():
{
"clusteredType" : "ParallelSort",
"shards" : {
"repset12" : [
{
"cursor" : "BtreeCursor field_hashed",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"field" : [
[
NumberLong(5346856657151215906),
NumberLong(5346856657151215906)
]
]
},
"server" : "server",
"filterSet" : false,
"stats" : {
"type" : "PROJECTION",
"works" : 3,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"children" : [
{
"type" : "KEEP_MUTATIONS",
"works" : 3,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"children" : [
{
"type" : "SHARDING_FILTER",
"works" : 2,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"chunkSkips" : 0,
"children" : [
{
"type" : "FETCH",
"works" : 1,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 1,
"children" : [
{
"type" : "IXSCAN",
"works" : 1,
"yields" : 0,
"unyields" : 0,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ field: \"hashed\" }",
"boundsVerbose" : "field #0['field']: [5346856657151215906, 5346856657151215906]",
"isMultiKey" : 0,
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 1,
"children" : []
}
]
}
]
}
]
}
]
}
}
]
},
"cursor" : "BtreeCursor field_hashed",
"n" : 1,
"nChunkSkips" : 0,
"nYields" : 0,
"nscanned" : 1,
"nscannedAllPlans" : 2,
"nscannedObjects" : 1,
"nscannedObjectsAllPlans" : 2,
"millisShardTotal" : 0,
"millisShardAvg" : 0,
"numQueries" : 1,
"numShards" : 1,
"indexBounds" : {
"field" : [
[
NumberLong(5346856657151215906),
NumberLong(5346856657151215906)
]
]
},
"millis" : 1
}
As at MongoDB 2.6, you won't get a fully covered sharded query because there is an extra query to check if the shard in question owns that document (see SERVER-5022 in the MongoDB issue tracker).
The mongos router filters documents that are found on a shard but that should not live there according to the sharded cluster metadata.
Documents can exist on more than one shard if:
There is a chunk migration in progress: documents are copied from a donor shard to a destination shard and are not removed from the donor shard until the chunk migration successfully completes.
Documents have been "orphaned" on a shard as a result of a failed migration or incomplete clean up. There is a cleanupOrphaned admin command in MongoDB 2.6 which can be run against a sharded mongod to delete orphaned documents.
This covered query limitation is noted in the Limits: Covered Queries in Sharded Clusters section of the MongoDB documentation but should also be highlighted in the tutorial on Creating Covered Queries. I've raised DOCS-3820 to make this more obvious.