[SERVER-18115] The planner can add an unnecessary in-memory sort stage for .min()/.max() queries Created: 19/Apr/15 Updated: 17/Nov/16 Resolved: 02/Feb/16 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.9, 3.0.2 |
| Fix Version/s: | 3.2.3, 3.3.2 |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Pierre Coquentin | Assignee: | Tess Avitabile (Inactive) |
| Resolution: | Done | Votes: | 0 |
| Labels: | code-and-test | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Ubuntu 12.04 |
||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Operating System: | Linux | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Backport Completed: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Steps To Reproduce: | So, first step, create a collection with a compound multikey index.
In 2.4.13, we have this when executing an ordered query using min / max
And, now, the same query but on 2.6.9 (same result with MongoDB 3.0.2). MongoDB set the scanAndOrder to true whereas the index is already sorted.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Sprint: | Query F (02/01/16), Query 10 (02/22/16) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Description |
|
IndexScanNode::computeProperties() computes sort orders provided by the index scan. It obtains some of these sort orders by looking at the contents of its IndexBounds instance. However, it fails to compute some obvious sort orders when isSimpleRange is true. As a result, plans for .min()/.max() queries may involve an unnecessary in-memory SORT stage. Original Description We currently run a sharding in production in 2.4.13 with a fairly large collection and a compound multikey index. We met the problem with multi key index with unbound limit that we solved using cursor.min and cursor.max. |
| Comments |
| Comment by Githook User [ 02/Feb/16 ] |
|
Author: {u'username': u'tessavitabile', u'name': u'Tess Avitabile', u'email': u'tess.avitabile@mongodb.com'}Message: (cherry picked from commit ecfac2be1f855c19ef6b50eef712fd3b6b9631f7) |
| Comment by Githook User [ 02/Feb/16 ] |
|
Author: {u'username': u'tessavitabile', u'name': u'Tess Avitabile', u'email': u'tess.avitabile@mongodb.com'}Message: |
| Comment by Ramon Fernandez Marina [ 25/Nov/15 ] |
|
PierreCoquentin, this ticket is currently not scheduled, so we can't provide an estimate of when it will be fixed. Any scheduling changes will be posted here when they happen. |
| Comment by Pierre Coquentin [ 17/Nov/15 ] |
|
Hello, Is it possible to know if this bug will be corrected in the next version of mongodb ? Because, we are blocked to upgrade to mongodb 3.0 and thus benefit of all improvements done. |
| Comment by Pierre Coquentin [ 20/Apr/15 ] |
|
The explanation is quite clear. |
| Comment by David Storch [ 20/Apr/15 ] |
|
Hi pcoq, Thanks for reporting this issue. I can confirm that the rewrite to the query engine introduced this problem. Note that this is purely a performance problem, i.e. the query engine is failing to recognize that an in-memory sort of the result set is unnecessary. However, given that this optimization was present in previous versions and is now missing, it is fair to consider this a bug. The problem stems from the fact that index bounds creating using the .min()/.max() operators are stored using a different format than those created from regular query predicates (see index_bounds.h for details). We have the special format for .min()/.max() because these operators allow bounds to be expressed that are not expressable using the standard format. I have a comment explaining why on The sort analysis code, which is responsible for determining whether or not an in-memory SORT stage is needed, operates only over the standard index bounds format. It does not consider the special "isSimpleRange" bounds format when doing sort analysis, which is the root cause of the problem. Hope that helps to clear things up, but let me know if you have any further questions. I'm going to move this ticket into "Needs Triage" state. Please continue to watch this ticket for progress updates. Best, |