[SERVER-12923] Plan ranking is bad for plans with blocking stages Created: 26/Feb/14 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.4.8, 2.6.0-rc0 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | David Storch | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 12 |
| Labels: | 26qa, bonsai, query-44-grooming | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||||||
| Description |
|
Currently we rank plans by work()'ing them some number of times and seeing who produces the most results. If there is a plan with a blocking stage, it will therefore almost always be ranked lower than plans without a blocking stage. This is because plans without a blocking stage immediately start producing results. Although the plan with the blocking stage doesn't appear to be making progress immediately, it may end up looking much better / hitting EOF much faster if we were to run the plans to completion. Blocking stages for which this is an issue:
Here's one way to reproduce the issue, for the blocking SORT stage:
The query ends up using index {a: 1}, which involves scanning 10,000 docs and fetching them from disk if they're not in memory. We would have liked to use index {b: 1}, which would involve fetching only 150 docs and then sorting them in memory. |
| Comments |
| Comment by David Storch [ 11/May/15 ] |
|
It's hard to say without seeing specific details whether or not this ticket is the root cause of your issue. I suggest opening a new SERVER ticket with the following details:
This additional information will aid in the debugging process. Best, |
| Comment by Adrien Jarthon [ 11/May/15 ] |
|
We also experienced this issue on a big collection (3 million records) where a query iterated over the 3 million records instead of 1 or 2 by using the proper index. |