[SERVER-41142] Compound index on an array field is not being used correctly Created: 14/May/19 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Index Maintenance, Querying |
| Affects Version/s: | 4.0.6 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Tom Grossman | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||
| Issue Links: |
|
||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||
| Participants: | |||||||||||||
| Description |
|
In the following schema:
with the following index:
If I use this query:
The inputStages are separated to 3 stages - arr: [a, a], arr: [[], []], arr: [undefined, undefined]. BUT, if I use the following query:
There are only 2 input stages - arr: [a, a], arr: [[undefined, undefined], [[] , []]] . In large collection, this causes the following error: I would expect the second scenario to perform like the first one - separating the input stages to 3 stages and to use the SORT_MERGE function. |
| Comments |
| Comment by Asya Kamsky [ 11/May/20 ] | ||
|
It looks to me like MERGE_SORT is used if the query:
is rewritten as
which can be used as a workaround till this is fixed. | ||
| Comment by Eric Sedor [ 24/May/19 ] | ||
|
This may be considered a duplicate of SERVER-24518 because of the combined "arr": ["[undefined, undefined]", "[[] , []]"] index bounds that are generated for the compound case. But there are implications related to queries on the empty array. I am passing this on to the appropriate team to investigate further. Thanks for your report, tomgrossman | ||
| Comment by Eric Sedor [ 14/May/19 ] | ||
|
Thanks for the detailed report tomgrossman; we will investigate. |