[SERVER-24027] The planner does not consider reversing index scan direction in order to obtain a SORT_MERGE plan Created: 03/May/16 Updated: 09/Oct/17 Resolved: 13/Jan/17 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 3.2.3 |
| Fix Version/s: | 3.4.2, 3.5.2 |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Mark Brinsmead | Assignee: | Tess Avitabile (Inactive) |
| Resolution: | Done | Votes: | 1 |
| Labels: | neweng | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||
| Operating System: | ALL | ||||||||||||||||
| Backport Requested: |
v3.4, v3.2
|
||||||||||||||||
| Steps To Reproduce: | Ascending case:
descending case:
In the "ascending case" both $OR clauses are handles with ascending index-based sorts, and a SORT-MERGE. In the descending case, one clause gets a descending range scan and the other an ascending range scan. The results are then passed to an in-memory sort. Providing a descending index and hinting it provides the "descending case" with a plan that is a mirror image to the "ascending case", as one would hope. |
||||||||||||||||
| Sprint: | Query 2017-01-23 | ||||||||||||||||
| Participants: | |||||||||||||||||
| Description |
| Comments |
| Comment by Alessandro Gherardi [ 21/Jan/17 ] | |||
|
Thanks! | |||
| Comment by Githook User [ 20/Jan/17 ] | |||
|
Author: {u'username': u'tessavitabile', u'name': u'Tess Avitabile', u'email': u'tess.avitabile@mongodb.com'}Message: | |||
| Comment by David Storch [ 19/Jan/17 ] | |||
|
Hi agherardi, After review of the backport requests, we have decided that we can safely backport this change to 3.4 but not to 3.2. This should become available in one of the next patch releases of the 3.4.x series. If you are on 3.2 and require this fix, you will need to upgrade. Best, | |||
| Comment by David Storch [ 17/Jan/17 ] | |||
|
Hi agherardi, A backport may not be possible, especially to the v3.2 branch. However, I will put this ticket in line to go through our normal backport approval process for both the v3.2 and v3.4 branches in order to determine whether the fix is possible/admissible in these branches. Best, | |||
| Comment by Alessandro Gherardi [ 17/Jan/17 ] | |||
|
Thank you for fixing this issue. Would you consider back-porting the patch to 3.2.X? | |||
| Comment by Githook User [ 13/Jan/17 ] | |||
|
Author: {u'username': u'tessavitabile', u'name': u'Tess Avitabile', u'email': u'tess.avitabile@mongodb.com'}Message: | |||
| Comment by David Storch [ 20/Dec/16 ] | |||
|
Hi agherardi, this ticket has a fixVersion of "planned but not scheduled", which means we intend to implement the improvement but currently have no timeframe for this work. Looking at its position in our backlog, I would say there is some chance that this will be released as part of version 3.6. | |||
| Comment by Alessandro Gherardi [ 20/Dec/16 ] | |||
|
Hello - Any updates on this issue? Thanks. | |||
| Comment by Nate Smith [ 14/Jul/16 ] | |||
|
Mark, thank you for the excellent description of the issue. We've also run into this issue using Mongo 3.0.11 and were expecting a single index to be able to support both forward and backward direction scans, but find that using an $or limits us to a forward direction on the index currently. | |||
| Comment by David Storch [ 04/May/16 ] | |||
|
Simpler repro:
| |||
| Comment by David Storch [ 04/May/16 ] | |||
|
Hi mark.brinsmead, Thanks for the issue report. I looked into the cause of this behavior and found that this is indeed a missing optimization in the query planner. The code with the defect dates to version 2.6.0, so I believe this affects all versions of 2.6, 3.0, and 3.2. There is even a comment in the planner with a TODO about this: https://github.com/mongodb/mongo/blob/r3.3.5/src/mongo/db/query/planner_access.cpp#L1110-L1112 I will move this ticket onto our team's backlog in a Needs Triage state, and we will review together at our next triage meeting. Best, |