Cannot get a covered query for sharded collection in MongoDB

Question

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
}

Can you show us the full explain()? – Sammaye Jul 25 '14 at 10:06 — Sammaye, Jul 25 '14 at 10:06

Stennie · Accepted Answer · 2014-07-25T15:16:33.073

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.

But the collection is sharded over the (hashed) field that is covered in the index. Thus the returned field value from the index should suffice to check. Is more data than the shard key needed for these checks? — daniel, Jul 31 '14 at 09:39
Note: SERVER-5022 has been resolved during the 2.7.x development cycle, so covered queries on sharded clusters will be possible in the next MongoDB release (i.e. 2.8.0 or higher); the changes are too significant to backport to earlier versions. — Stennie, Jan 22 '15 at 23:18

Cannot get a covered query for sharded collection in MongoDB

1 Answers1

Linked

Related