[SERVER-34926] allow $expr with comparison bounded on both sides with same type to use a non-multikey index Created: 10/May/18 Updated: 14/Sep/22 Resolved: 14/Sep/22 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Aggregation Framework |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Asya Kamsky | Assignee: | James Wahlin |
| Resolution: | Duplicate | Votes: | 4 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||
| Sprint: | QO 2022-07-11, QO 2022-07-25, QO 2022-08-08, QO 2022-08-22, QO 2022-09-05, QO 2022-09-19 | ||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||||||
| Description |
|
Similarly to I believe the two clear cases of this are:
I think the first case would be more common in "normal use" the second can be used to force the type bracketing semantics in aggregation comparisons. |
| Comments |
| Comment by James Wahlin [ 14/Sep/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
The first/primary case described by this ticket, which asks to create tight index bounds for an explicit range was addressed by The second request for a one sided comparison plus $type operator is still outstanding. A new ticket (SERVER-69680) has been created to track this feature request. The current ticket will be closed as a duplicate of | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by David Storch [ 28/Jun/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
asya.kamsky@mongodb.com yeah, it does look like the basic request tracked by this ticket of allowing inequalities expressed with $expr to use a non-multikey index has been completed. That said, your initial description also discusses the case of an inequality expressed with $expr that is AND'ed with a $type expression. Here's an example:
Notably, the index bounds are [MinKey, 20) rather than [Infinity, 20). So I think there is still an opportunity for improvement here. You also brought up the point that we are unable to generate covered plans for predicates expressed this way, which is yet another potential improvement. Since the main request here has been completed, however, I'm wondering if we should close this ticket and open new ones to track tightening the bounds when a $type expression is used and the ability to generate covered plans with $expr. I'll return this ticket to the triage queue in the meantime. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 20/May/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
If the index is multikey I think it can still be used in cases where type is bounded on both sides like in this ticket's example, but it cannot be covered, obviously. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 20/May/22 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
With Just tested my example and it works:
The only thing I can think of is this ticket describes scenario where we could use a COVERED (non-multikey) index plan, which we currently do not. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Asya Kamsky [ 18/Aug/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
rangaraj.vignesh@gmail.com variables or not are expected to behave the same inside $expr
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Vignesh Rangaraj [ 18/Aug/20 ] | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Doesn't work on variables either.
|