[SERVER-56101] Unnecessary FETCH stage when querying an array Created: 14/Apr/21 Updated: 27/Oct/23 Resolved: 20/Apr/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | None |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Kay Agahd | Assignee: | Edwin Zhou |
| Resolution: | Works as Designed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | When querying an array as documented here, MonogDB needs to fetch the whole document to read the queried field although both the projected field and the queried field are in the index:
The same applies when the $elemMatch operator is used:
However, using the $all operator does not require the FETCH stage:
The same is true for the standard way to query a single element in an array:
|
||||||||||||||||||||||||||||||||||||||||
| Participants: |
| Description |
|
When querying an array as documented here, MonogDB may need to fetch the whole document to read the queried field although both the projected field and the queried field are in the index. This is an undesired behaviour because it fetches uselessly documents from the disk which makes the query slow (especially, if documents are bigger). |
| Comments |
| Comment by Kay Agahd [ 25/Apr/21 ] |
|
Hi edwin.zhou,
However, as my last two examples show, there are obviously exceptions to the rule, so it would be great if the documentation could be clearer about how multikey indexes may cover a query over array fields. Just saying:
is not enough because it does not explain how the query and index have to be written in order to be covered by the multikey index over the array field. Thanks for your consideration. |
| Comment by Edwin Zhou [ 20/Apr/21 ] |
|
I greatly appreciate your detailed reproduction and description of this issue. Multikey indexes cannot cover queries over array fields as documented here. A more detailed explanation by Asya can be found on Best, |