Covered queries on sharded collections

Published reference · snapshot 2026-10-20 · skill mongodb-expert/references/covered-queries-on-sharded-collections.md

Also known as: Index-only plans on sharded collections

↓ Facts as markdown all context files

Source-anchored research for Covered queries on sharded collections. Run status: COMPLETED.

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

Definitions

The SHARDING_FILTER stage reports chunkSkips, the count of documents it dropped as not owned. A value above 0 means the shard holds documents it does not own, such as orphans or migration copies. Huawei Cloud's FAQ uses this check and warns that its command scans all documents on every shard node. The server test asserts chunkSkips of 0 in its covered cases. (medium confidence) [source]

Structure and components

If a query runs through mongos against a sharded collection, an index can cover it only when the index contains the shard key. The manual lists this under the limitations of covered queries. The rule reads the same in the v4.4 manual source, the v7.0 manual and the current manual. (high confidence) [source]
The manual defines a covered query as one where all fields in the query, 'both as specified by the application and as needed internally such as for sharding purposes', are part of an index. On a sharded collection, the shard key fields count as fields the query needs. (high confidence) [source]
The 3.0 manual added one exception: a query on a sharded collection that specifies only _id and returns only _id could be covered by the _id index through mongos, even if _id is not the shard key. The v4.4 manual source, the v7.0 manual and the current manual contain no such exception, so older answers and posts that cite it are outdated. (medium confidence) (verified as of 2026-10-20) [source]
A dotted shard key such as {'a.b':1} is satisfied by an index that contains the same path: index {c:1,'a.b':1} covers find({c:true},{_id:0,'a.b':1,c:1}). An index on a parent field does not satisfy a shard key that is a subfield of it. With shard key {'_id.a':1,'_id.b':1} the _id index holds enough data, but the planner does not use it, and the improvement ticket SERVER-33054 is in Backlog, unresolved. (medium confidence) (verified as of 2026-10-20) [source]
If a query on a sharded collection runs through mongos, an index covers it only when the index contains the shard key. The manual states this in Query Optimization and again in Limits and Thresholds. Query Optimization also counts fields 'needed internally such as for sharding purposes' among the fields an index must hold. The query planner enforces the rule. (high confidence) [source]
Before 3.0, no query on a sharded collection could be covered. The shard loaded each matching document to check chunk ownership (SERVER-5022, opened 2012-02-20). Commit 2f7fce0 'allow covered queries with shard filter' (2014-08-27) fixed it in 2.7.6, a development release; the manual dates the change to 3.0. The change covers only indexes that contain the shard key, because the shard still checks chunk ownership. A MongoDB 2.6 answer on Stack Overflow confirms covered sharded queries were impossible there. (high confidence) [source]
When _id is the shard key, a covering index must contain _id. With shard key {_id:1}, index {a:1} does not cover find({a:true}, {_id:1, a:1}) (1 document examined). Index {a:1, _id:1} covers it (0 examined). If the index lacks _id, the projection must set _id:0 for the query to be covered. (high confidence) [source]
Since 4.3.1 (SERVER-42390), a document with no value for a shard-key field counts as null there. Before, orphan filtering treated such a document as orphaned and always dropped it. A covered query through an index that contains the shard key therefore returns such a document. The master test shows 1 document returned, 0 examined, chunkSkips 0, and calls this 'weird' because indexes have no 'does not exist' value. (medium confidence) [source]
In 2013 (SERVER-8405) a sharded count skipped the chunk-ownership check. That let counts use covered indexes, but the counts could include orphaned or migrating documents. The ticket proposed the design SERVER-5022 later shipped: check chunk membership from the index when it contains the shard key, otherwise load the document. Current count behavior on sharded clusters was not verified. (low confidence) [source]

How it works

When a query takes the IDHACK path (an equality on _id), the planner always fetches the full document and adds a ShardFilterStage if the collection is sharded; the code comment says 'we don't support covered projections'. Such queries are not covered on sharded collections. In 2.6.0 to 2.6.2, IDHackRunner allowed covered _id projections on sharded connections, skipped the sharding filter and returned orphan documents. SERVER-14304 fixed this in 2.6.3, and adding hint({_id:1}) avoided it. (high confidence) (verified as of 2026-10-20) [source]
The position of FETCH relative to SHARDING_FILTER shows why the fetch exists. FETCH below SHARDING_FILTER means the plan needed documents before it could check ownership: a shard key field is missing from the index, the index holds a hash for a ranged shard key field, or the query filters on a hashed field. FETCH above SHARDING_FILTER means the index supplied the shard key and the plan fetches only to build output, for example when the projection includes a hashed field. (medium confidence) (verified as of 2026-10-20) [source]
Orphan filtering, the reason for the shard-key-in-index rule, also applies to reads from secondaries, because every member of a shard replica set keeps chunk metadata. Earlier versions could return orphaned documents from secondaries whatever the read concern. Read concern 'available' skips the check that the correct shard received the query, and it can return orphaned documents during chunk migration. (high confidence) (verified as of 2026-10-20) [source]
The SHARDING_FILTER stage in sharded explain output reports chunkSkips, the number of documents skipped because they belong to another shard. A high value points to orphaned documents on that shard. (low confidence) [source]
The server decides per operation, on each shard, whether to add the shard filter. getExecutorFind and the count path set INCLUDE_SHARD_FILTER when the collection acquisition's sharding description reports the collection as sharded. (low confidence) (verified as of 2026-10-20) [source]
Indexing only the filter and projection fields, without the shard key, leaves a FETCH on every shard. With shard key {_id:'hashed'} and index {a:1}, find({a:true},{_id:0,a:1}) still examines one document. The test suite's own comment says documents must be fetched to apply SHARDING_FILTER when the query is not on the shard key. (medium confidence) [source]
The shard filter reads the shard-key value of each document it checks. If the index scan does not supply every shard-key field, the planner puts a FETCH stage below SHARDING_FILTER, and the query is no longer covered. The planner also adds the FETCH when the index supplies only the hash of a shard-key field that is not itself hashed. SERVER-8405 (2013) had proposed checking chunk membership from the index when the index includes the shard key, and fetching the document otherwise. (high confidence) [source]
A nested shard key such as {'a.b': 1} can be covered on current master. Index {c:1, 'a.b':1} with projection {_id:0, 'a.b':1, c:1} examines 0 documents. The original 2014 test asserted the opposite ('nested query not covered even when projecting'). Behavior on releases between those two points was not checked. (medium confidence) (verified as of 2026-10-20) [source]
Before 8.2, DISTINCT_SCAN plans for $group on sharded collections had no SHARDING_FILTER, so orphaned documents could supply stale values (SERVER-42160, fixed in 8.2.0-rc0). Flag featureFlagShardFilteringDistinctScan (version 8.2, default true, FCV-gated) now lets distinct scans apply the shard filter. The planner adds a FETCH first when the scanned index lacks a shard-key field. (medium confidence) (verified as of 2026-10-20) [source]
If a covered query on a sharded collection still shows FETCH, check in order: (1) the index lacks a shard-key field; (2) the projection returns a field the index lacks, including _id unless the index holds it or the projection sets _id:0; (3) the query matches null, since {field: null} and {field: {$eq: null}} never cover; (4) the index is hashed, or is multikey on an array field. Causes 2 to 4 also apply to unsharded collections. (medium confidence) [source]

How-to and procedures

Run explain from mongos to check coverage on a sharded collection. The plan of each accessed shard appears in the shards field. A covered plan has an IXSCAN stage that is not a descendant of FETCH. MongoDB's sharding tests expect IXSCAN and SHARDING_FILTER, no FETCH, SINGLE_SHARD or SHARD_MERGE at the router, and executionStats.totalDocsExamined equal to 0. (high confidence) [source]
To cover a query on a sharded collection, add every shard key field to the covering index after the query fields. MongoDB's tests use {c:1,b:1,a:1} for shard key {a:1,b:1}, and {c:1,d:1,a:1,b:1,_id:1} for a filter on c and d. The manual says the ESR rule orders the fields of a covered-query index. (medium confidence) [source]
To decide whether a query on a sharded collection can be covered, check four conditions. (1) One index supplies every shard key field: the raw value, or for a hashed shard key field the raw value or its hash. (2) The same index supplies every field the filter and projection use. (3) The filter and projection do not touch a hashed field. (4) The usual limits hold: no equality-to-null predicate, no array fields from a multikey index, and _id excluded from the projection unless it is indexed. Then confirm with explain from mongos. (medium confidence) [source]
Check coverage by running explain('executionStats') through mongos. A covered plan shows an IXSCAN that is not a descendant of a FETCH, and totalDocsExamined is 0. The server tests assert totalDocsExamined of 0 for covered cases and 1 for uncovered ones. Per-shard plans sit under queryPlanner.winningPlan.shards and executionStats.executionStages.shards. (high confidence) [source]

Problems, failure modes and limitations

The index must contain every shard key field, including fields that the filter and the projection do not use. In MongoDB's sharding tests, hinting index {a:1,c:1} on shard key {a:1,b:'hashed',c:1} adds a FETCH to find({a:{$gt:25,$lt:29}},{a:1,c:1,_id:0}). On shard key {a:1,b:1}, index {c:1} cannot cover a query that projects a, b and c, while index {c:1,b:1,a:1} can. (high confidence) [source]
A query is not covered if it filters on or projects a hashed field of the index, because different values can produce the same hash. The planner tests give this reason, and the hashed-index page says 'Hashed indexes can't cover a query.' A collection sharded on one hashed field therefore gets no covered query on that field: with shard key {_id:'hashed'}, find({_id:true},{_id:0}) examines one document. (high confidence) [source]
Before MongoDB 3.0, no index could cover a query on a sharded collection through mongos. The 3.0 manual marks the rule with 'versionchanged 3.0: In previous versions, an index cannot cover a query on a sharded collection when run against a mongos.' SERVER-5022 reports that covered indexes were ineffective whenever a chunk manager existed. It closed as Done with fix version 2.7.6 on 2014-09-05. (medium confidence) [source]
Hashed indexes cannot cover a query. The Hashed Indexes page says so, and the server test for a hashed _id shard key asserts that find({_id:true}, {_id:0}) examines 1 document, with the comment 'can't be covered since hashed index'. The hashed shard-key index therefore cannot be the covering index. (medium confidence) [source]
When the query pins the full shard key by equality, mongos can target one shard. Since 5.0 (present in tag r5.0.0, absent in r4.4.0) the planner then drops the shard filter, because a shard cannot own orphans inside its own key ranges. The source comment says this can allow a more efficient plan, for example COUNT_SCAN. Tags r4.0.0 to r4.4.0 drop the filter only when the shard has no routing metadata. The predicate still names the shard-key fields, so the index must still contain them to cover the query. (medium confidence) (verified as of 2026-10-20) [source]
A 3.4-era copy of the manual listed one exception: a query on a sharded collection that filters only on _id and returns only _id could be covered by the _id index through mongos, even if _id is not the shard key. The current manual lists no such exception; its Sharded Collections limit says only that the index must contain the shard key. Do not rely on the _id exception. The release that removed it was not determined. (medium confidence) (verified as of 2026-10-18) [source]
The coverage limit applies to queries that arrive through mongos. The planner adds the shard filter only when the command requests it; the source comment reads 'Shard filter was not requested; cmd may not be from a router'. A 2014 user report on MongoDB 2.6 saw indexOnly true with 0 objects scanned on the shard's mongod, and indexOnly false through mongos. A direct connection skips the shard filter, so it does not drop orphaned documents. (medium confidence) [source]

Facts and statements

Each shard must drop documents it does not own, such as orphaned documents, before it returns results. The SHARDING_FILTER stage does this and needs the shard key value of every candidate document. If the index does not supply every shard key field, the planner adds a FETCH below SHARDING_FILTER so the filter can read the shard key from the document. (high confidence) [source]
The order of the shard key fields inside the index does not matter for covering. Index {a:1,c:1,b:'hashed'} covers queries on shard key {a:1,b:'hashed',c:1}, and index {c:1,b:1,a:1} covers queries on shard key {a:1,b:1}. (high confidence) [source]
A hashed shard key field needs only its hash in the index for shard filtering. If the shard key field is hashed and the index holds its hash, the plan needs no FETCH. If the shard key field is ranged and the index holds only a hash of that field, the planner adds a FETCH. If the index holds the raw value of a field whose shard key is hashed, the plan stays covered. (high confidence) (verified as of 2026-10-20) [source]
A find or count that fixes the whole shard key with equality predicates can omit SHARDING_FILTER: {a:true,b:true} on shard key {a:1,b:1}, or {a:true} on shard key {a:'hashed'}. A prefix of a compound shard key, an $in on a shard key field, or a filter on a non-shard-key field keeps the stage. A plan without SHARDING_FILTER can therefore be by design. (low confidence) (verified as of 2026-10-20) [source]
A sharded collection must have an index on the shard key, or a compound index with the shard key as a prefix, and sh.shardCollection() builds the shard key index on an empty collection. That index can itself serve covered queries. In MongoDB's compound-hashed test, the shard key index {a:1,b:'hashed',c:1} covers find({a:0},{a:1,c:1,_id:0}), rejects the orphan documents and uses no FETCH. (medium confidence) [source]
Covering is decided on each shard. Whether mongos targets one shard or broadcasts to all shards does not change the rule. A shard uses the most efficient index available, either the shard key index or another eligible index on that shard. MongoDB's test shows covered plans under both SINGLE_SHARD and SHARD_MERGE routing. (high confidence) [source]
For distinct and $group on a sharded collection, the hand-built distinct scan chooses an index from the projection alone. With indexes {notShardKey:1} and {notShardKey:1,shardKey:1}, it can pick the first and add a redundant FETCH. SERVER-97235 (Needs Scheduling, opened 2024-11-15) tracks this. (low confidence) (verified as of 2026-10-20) [source]
A document stored on a shard without the shard key field, for example inserted directly on the shard, is returned by both the fetch plan and the covered plan, and chunkSkips stays 0 in both. The test comment calls the covered result 'weird' and attributes it to indexes having no dedicated 'does not exist' value. (low confidence) (verified as of 2026-10-20) [source]
Each shard runs a SHARDING_FILTER stage that drops documents the shard does not own. A shard holds unowned documents while a chunk migration is in flight and after a migration aborts (orphaned documents). The explain reference describes SHARDING_FILTER as the stage that filters out orphan documents from shards. (high confidence) [source]
A compound shard key needs every one of its fields in the covering index, in any order. With shard key {a:1, b:1}, the server test shows index {c:1, b:1, a:1} covers find({c:true}, {_id:0, a:1, b:1, c:1}) with 0 documents examined. Index {c:1} alone examines 1 document. (high confidence) [source]
Coverage and targeting depend on different things. Coverage needs the shard key inside the index. Targeting needs the shard key, or a prefix of a compound shard key, inside the query predicate; without one, mongos broadcasts to all shards. A covered query with no shard-key predicate is therefore still a scatter-gather query (this follows from the two documented rules). (medium confidence) [source]
Adding a shard-key predicate only to steer routing can break a covering index that lacks the shard key. The shard-local query then names a field the index does not hold (SERVER-11991, opened 2013). The reporter asked for a routing-only hint and said putting the shard key in every index 'seems like a hack'. MongoDB closed the ticket as Won't Fix on 2019-11-07. (low confidence) [source]
The _id point-lookup path has returned orphans on sharded collections. In 2.6.0 to 2.6.2 the IDHACK path allowed covered projections on sharded connections, skipped the sharding filter, and returned orphaned documents (SERVER-14304). Release 2.6.3 stopped allowing covered projections there. In current source the express _id lookup is handed the collection's shard filter whenever the filter was requested. (medium confidence) (verified as of 2026-10-20) [source]
Orphan filtering depends on the read concern. Reads with 'available' skip the check that the correct shard received the query and can return orphaned documents. All members of a shard replica set keep chunk metadata, so reads from secondaries filter orphans unless the read concern is 'available'; earlier versions could return orphans from secondaries with any read concern. The manual does not say whether 'available' also removes SHARDING_FILTER from the plan. (medium confidence) [source]

Children

COUNT_SCAN and sharded count accuracy (frontier, viability 3.80 / 5)
Covered distinct and $group on sharded collections (frontier, viability 3.85 / 5)
DISTINCT_SCAN with shard filtering in 8.2 (frontier, viability 3.40 / 5)
Express path for _id lookups on sharded collections (frontier, viability 3.23 / 5)
Hashed and compound hashed shard keys and index coverage
Hashed shard keys and index support (frontier, viability 3.92 / 5)
Orphaned documents and range deletion
Read concern available, secondary reads and orphaned documents (frontier, viability 4.28 / 5)
Reading sharded explain output (shards, SINGLE_SHARD, SHARD_MERGE)
SHARDING_FILTER stage and chunkSkips
SHARDING_FILTER stage internals and orphaned-document filtering (frontier, viability 3.75 / 5)
Shard key index design (frontier, viability 3.55 / 5)
readConcern available on sharded clusters (frontier, viability 4.08 / 5)

Frontier under this node: COUNT_SCAN and sharded count accuracy (3.80), Covered distinct and $group on sharded collections (3.85), DISTINCT_SCAN with shard filtering in 8.2 (3.40), Express path for _id lookups on sharded collections (3.23), Hashed shard keys and index support (3.92), Read concern available, secondary reads and orphaned documents (4.28), SHARDING_FILTER stage internals and orphaned-document filtering (3.75), Shard key index design (3.55), readConcern available on sharded clusters (4.08)

← the whole tree · 3D view· how to read this page