[SERVER-7959] Potentially unexpected scans with compound indexes when some fields are multikey Created: 17/Dec/12 Updated: 03/Sep/14 Resolved: 02/Sep/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.2.2 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Max Schireson | Assignee: | hari.khalsa@10gen.com |
| Resolution: | Duplicate | Votes: | 13 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Attachments: |
|
||||||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||||||
| Operating System: | ALL | ||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||
| Description |
|
Lets say you are querying for { a:{ $gt:10, $lt:20 } } and you have an index on a, b If b is multikey, the query optimizer will scan a much wider range of index and it will have to scan the objects to complete query evaluation. This is because the optimizer doesn't know if all the values of a are scalar; if they are not a document like { _id:2, { a:[ 5, 25 ] } } would match the query. This makes the advice that an index on a is not needed if you have an index on a, b incorrect and makes performance optimization confusing. |
| Comments |
| Comment by Thomas Rueckstiess [ 02/Sep/14 ] |
|
Closing this ticket as duplicate of Even though this ticket precedes |
| Comment by Zack Slayton [ 06/Jun/14 ] |
|
@Max Schireson What was the workaround provided? I've been wrestling with this issue for months. I'd love to see a fix, even if it's a temporary solution. |
| Comment by Benety Goh [ 30/Oct/13 ] |
|
smoke test script to reproduce issue |
| Comment by Max Schireson [ 18/Dec/12 ] |
|
There is a very simple special case that may be worth doing if the bigger fix will take a while: at least we know that _id is scalar even when it is a part of a multi-key compound index. In the specific case that inspired this ticket, that would have solved the problem. |
| Comment by Max Schireson [ 17/Dec/12 ] |
|
Workaround provided, but this issue is at the root of that case. |