[SERVER-15636] Query Optimizer Index Scans when first predicate is unbounded. Created: 13/Oct/14 Updated: 19/Oct/21 Resolved: 19/Oct/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.0 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Osmar Olivo | Assignee: | James Wahlin |
| Resolution: | Duplicate | Votes: | 1 |
| Labels: | 2426 | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Participants: | |||||||||
| Case: | (copied to CRM) | ||||||||
| Description |
|
Imagine you have fields A & B and a compound index defined as
Now imagine a query
In 2.4 it appears we would jump to all values of A and within each subset of A skip to entries where B="value" in the index. Leading to a smaller nscannedObjects() value in the query plan. As of 2.6 it appears indexed plans where the first field in the index is unconstrained are not considered. Which means we end up not using plans in 2.6 that would sometimes get used in 2.4 In this particular case, we end up essentially doing a full index scan. Which depending on data distribution, will most likely be slower than in 2.4. |
| Comments |
| Comment by James Wahlin [ 19/Oct/21 ] | ||||
|
Closing as a duplicate of SERVER-13197. It is worth noting for anyone following that we generally recommend ordering index keys in the following priority: For more on this see the following blog post: https://www.alexbevi.com/blog/2020/05/16/optimizing-mongodb-compound-indexes-the-equality-sort-range-esr-rule/ | ||||
| Comment by David Storch [ 11/Oct/21 ] | ||||
|
I'll mark this for triage. We can consider as a team whether we want to resolve this ticket as a dupe of SERVER-13197. | ||||
| Comment by Christopher Price [ 16/Oct/14 ] | ||||
|
Use case:
In an odd twist, there are some people that don't have a lastName. | ||||
| Comment by Christopher Price [ 13/Oct/14 ] | ||||
|
Actually, in the examples I provided that led to this ticket, the index on {A:1, B:1} was correctly selected. |