[SERVER-36818] $expr does not use multi-key index Created: 23/Aug/18 Updated: 27/Oct/23 Resolved: 24/Aug/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 3.6.3, 4.0.1 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Trevor Wilson | Assignee: | Nick Brewer |
| Resolution: | Works as Designed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | Start with empty collection and the index:
Initially, the query strategy indicates it will use the correct index
But when an element is inserted, it "discovers" that its a multi-key index
and the query no longer uses the correct index
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
An $expr expression in a query will not use a multi-key index. Ticket A normal query will use the correct index, but, as with the motivation for The problem exists in 3.6.3 as well as 4.0.1 |
| Comments |
| Comment by Asya Kamsky [ 03/Nov/20 ] | ||
|
craig@bipsync.com I'm afraid no update on this. | ||
| Comment by Craig Marvelley [ 03/Nov/20 ] | ||
|
Hi Asya. I don't suppose there's any update on when this alternate syntax for expressions might be available? I'm having issues with writing performant queries due to this limitation so it's relevant to my interests | ||
| Comment by Asya Kamsky [ 20/May/19 ] | ||
|
twilson sorry I missed your comment before. You can see some related discussion in SERVER-32549 but basically using a multikey index for $in aggregation expression would return both matches for array members and matches for top level value, because in the index we don't differentiate if the value happened to be an array element or not. That means that in the example we were discussing using an index would match both documents where links.id is an array with a match contained and when it's not an array but subdocument, which would be incorrect We are working on supporting an alternate syntax for expressions that would allow using indexes "normally" but unfortunately it's not yet scheduled for any particular version. | ||
| Comment by Trevor Wilson [ 24/Aug/18 ] | ||
|
Ok, it is clear I had misunderstood $expr, thanks for clearing that up. However, is it truly the case that $expr won't use a multi-key index no matter the expression? Reworking to use $in yields the correct results but still doesn't use the index.
| ||
| Comment by Asya Kamsky [ 24/Aug/18 ] | ||
|
The aggregation expression
is not equivalent to find expression
When we tried always using indexes for $expr expressions, we discovered that incorrect results would be returned when index was multikey. When value being compared equal is not an array the semantics of both expressions is the same in find and aggregation. In fact, if links.id is an array, the $expr you want to use would be $in and not $eq.
| ||
| Comment by Trevor Wilson [ 24/Aug/18 ] | ||
|
Really? Sounds strange to me that a normal query would use the index while the $expr equivalent does not... In my use case it was used to $lookup related documents that were multi-key indexed, but, since it induces a collection scan to do the lookup, it was not performant and we had to abandon it in favor of doing the queries manually outside the aggregation pipeline. Is there any plans to make this optimization? Is there a better way? | ||
| Comment by Nick Brewer [ 24/Aug/18 ] | ||
|
twilson This behavior is actually expected - the $expr operator will not work with a multikey index. I've opened a separate ticket to get some clarification on this in the documentation. -Nick |