Using $min and $max on shard key doesn’t target queries ?

Using $gte and $lt on the shard key fields inside a query will cause it the query to be targeted to only the appropriate shards.

However, using the $min and $max query operators (while hinting on the shard key) doesn’t target the query to the relevant shard(s) in the same way. The explain() is missing indexBounds in this case, which may be the cause or at least related?

$ mlaunch init –smallfiles –single –sharded 5 –port 22221 –binarypath /usr/local/m/versions/2.6.3/bin/
launching: /usr/local/m/versions/2.6.3/bin/mongod on port 22222
launching: /usr/local/m/versions/2.6.3/bin/mongod on port 22223
launching: /usr/local/m/versions/2.6.3/bin/mongod on port 22224
launching: /usr/local/m/versions/2.6.3/bin/mongod on port 22225
launching: /usr/local/m/versions/2.6.3/bin/mongod on port 22226
launching: config server on port 22227
launching: /usr/local/m/versions/2.6.3/bin/mongos on port 22221
adding shards.
$ /usr/local/m/versions/2.6.3/bin/mongo –port 22221 db
MongoDB shell version: 2.6.3
connecting to: 127.0.0.1:22221/test
db: db
> db.version()
2.6.3
> sh.enableSharding(“db”)
{ “ok” : 1 }
> sh.shardCollection(“db.coll”, {i: 1})
{ “collectionsharded” : “db.coll”, “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 0})
{ “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 10})
{ “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 20})
{ “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 30})
{ “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 40})
{ “ok” : 1 }
> sh.splitAt(“db.coll”, {i: 50})
{ “ok” : 1 }
> sh.status()
— Sharding Status —
sharding version: {
“_id” : 1,
“version” : 4,
“minCompatibleVersion” : 4,
“currentVersion” : 5,
“clusterId” : ObjectId(“53b20b86efe03f7a2ee9192e”)
}
shards:
{ “_id” : “shard0000”, “host” : “dbversity:22222” }
{ “_id” : “shard0001”, “host” : “dbversity:22223” }
{ “_id” : “shard0002”, “host” : “dbversity:22224” }
{ “_id” : “shard0003”, “host” : “dbversity:22225” }
{ “_id” : “shard0004”, “host” : “dbversity:22226” }
databases:
{ “_id” : “admin”, “partitioned” : false, “primary” : “config” }
{ “_id” : “test”, “partitioned” : false, “primary” : “shard0000” }
{ “_id” : “db”, “partitioned” : true, “primary” : “shard0000” }
db.coll
shard key: { “i” : 1 }
chunks:
shard0001 2
shard0002 1
shard0003 1
shard0004 1
shard0000 2
{ “i” : { “$minKey” : 1 } } –>> { “i” : 0 } on : shard0001 Timestamp(2, 0)
{ “i” : 0 } –>> { “i” : 10 } on : shard0002 Timestamp(3, 0)
{ “i” : 10 } –>> { “i” : 20 } on : shard0003 Timestamp(4, 0)
{ “i” : 20 } –>> { “i” : 30 } on : shard0004 Timestamp(5, 0)
{ “i” : 30 } –>> { “i” : 40 } on : shard0001 Timestamp(6, 0)
{ “i” : 40 } –>> { “i” : 50 } on : shard0000 Timestamp(6, 1)
{ “i” : 50 } –>> { “i” : { “$maxKey” : 1 } } on : shard0000 Timestamp(5, 5)

> for (i in db.coll.find({ i: { $gte: 1, $lt: 2 } }).explain().shards) print(i)
dbversity:22224

> for (i in db.coll.find().hint({i:1}).min({i:1}).max({i:2}).explain().shards) print(i)
dbversity:22222
dbversity:22223
dbversity:22224
dbversity:22225
dbversity:22226

> db.coll.find({ i: { $gte: 1, $lt: 2 } }).explain(true)
{
“clusteredType” : “ParallelSort”,
“shards” : {
“dbversity:22224” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
“i” : [
[
1,
2
]
]
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
“i” : [
[
1,
2
]
]
}
}
],
“server” : “dbversity:22224”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “field #0[‘i’]: [1.0, 2.0)”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
]
},
“cursor” : “BtreeCursor i_1”,
“n” : 0,
“nChunkSkips” : 0,
“nYields” : 0,
“nscanned” : 0,
“nscannedAllPlans” : 0,
“nscannedObjects” : 0,
“nscannedObjectsAllPlans” : 0,
“millisShardTotal” : 0,
“millisShardAvg” : 0,
“numQueries” : 1,
“numShards” : 1,
“indexBounds” : {
“i” : [
[
1,
2
]
]
},
“millis” : 0
}

> db.coll.find().hint({i:1}).min({i:1}).max({i:2}).explain(true)
{
“clusteredType” : “ParallelSort”,
“shards” : {
“dbversity:22222” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
}
}
],
“server” : “dbversity:22222”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “[{ : 1.0 }, { : 2.0 })”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
],
“dbversity:22223” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
}
}
],
“server” : “dbversity:22223”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “[{ : 1.0 }, { : 2.0 })”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
],
“dbversity:22224” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
}
}
],
“server” : “dbversity:22224”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “[{ : 1.0 }, { : 2.0 })”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
],
“dbversity:22225” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
}
}
],
“server” : “dbversity:22225”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “[{ : 1.0 }, { : 2.0 })”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
],
“dbversity:22226” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“nscannedObjectsAllPlans” : 0,
“nscannedAllPlans” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nYields” : 0,
“nChunkSkips” : 0,
“millis” : 0,
“indexBounds” : {
},
“allPlans” : [
{
“cursor” : “BtreeCursor i_1”,
“isMultiKey” : false,
“n” : 0,
“nscannedObjects” : 0,
“nscanned” : 0,
“scanAndOrder” : false,
“indexOnly” : false,
“nChunkSkips” : 0,
“indexBounds” : {
}
}
],
“server” : “dbversity:22226”,
“filterSet” : false,
“stats” : {
“type” : “KEEP_MUTATIONS”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“children” : [
{
“type” : “SHARDING_FILTER”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“chunkSkips” : 0,
“children” : [
{
“type” : “FETCH”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“alreadyHasObj” : 0,
“forcedFetches” : 0,
“matchTested” : 0,
“children” : [
{
“type” : “IXSCAN”,
“works” : 1,
“yields” : 0,
“unyields” : 0,
“invalidates” : 0,
“advanced” : 0,
“needTime” : 0,
“needFetch” : 0,
“isEOF” : 1,
“keyPattern” : “{ i: 1.0 }”,
“boundsVerbose” : “[{ : 1.0 }, { : 2.0 })”,
“isMultiKey” : 0,
“yieldMovedCursor” : 0,
“dupsTested” : 0,
“dupsDropped” : 0,
“seenInvalidated” : 0,
“matchTested” : 0,
“keysExamined” : 0,
“children” : [ ]
}
]
}
]
}
]
}
}
]
},
“cursor” : “BtreeCursor i_1”,
“n” : 0,
“nChunkSkips” : 0,
“nYields” : 0,
“nscanned” : 0,
“nscannedAllPlans” : 0,
“nscannedObjects” : 0,
“nscannedObjectsAllPlans” : 0,
“millisShardTotal” : 0,
“millisShardAvg” : 0,
“numQueries” : 5,
“numShards” : 5,
“millis” : 1
}
>