[SERVER-14618] Wrong index bounds when using "hint" Created: 18/Jul/14 Updated: 10/Dec/14 Resolved: 20/Aug/14 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.6.3 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Frédéric Giudicelli | Assignee: | Ramon Fernandez Marina |
| Resolution: | Done | Votes: | 1 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
|||||||||||||||||||||||||||
| Operating System: | ALL | |||||||||||||||||||||||||||
| Steps To Reproduce: | I have the following two indexes:
A) When I run the following query:
B) And when I run same query without calling "hint", the index bounds for TSCreated is properly computed, although it's not the proper index which is picked up:
|
|||||||||||||||||||||||||||
| Participants: | ||||||||||||||||||||||||||||
| Description |
|
It seems like than when using a range matching {$gt: XX, $lt YY} and forcing an index with "hint", the parser can properly parse the lower bound value. I've tried switching $gt and $lt places, it's all the same. |
| Comments |
| Comment by Ramon Fernandez Marina [ 28/Aug/14 ] | |||||||||||||||||||||
|
giudicelli, unfortunately we're not able to provide user support in the SERVER project, which is for reporting bugs or feature suggestions for the MongoDB server and tools. For MongoDB-related support discussion please post on the mongodb-user group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-user group. | |||||||||||||||||||||
| Comment by Frédéric Giudicelli [ 24/Aug/14 ] | |||||||||||||||||||||
|
The thing is I'm querying for an exact value on 'Groups', therefore it should only fetch the branch "Groups_1" of the multikey index, no ? The equivalent of my query would be: And since the upper and lower bound of "a" are the same, I see no reason why the second's field bound should not be properly handled, there is only one branch on the field "a", no ? What solution do I have, as the IDs in Groups are not stable, and I would rather avoid having to maintain a list of indexes for each current and future possible values in Groups ? | |||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 20/Aug/14 ] | |||||||||||||||||||||
|
giudicelli, further internal discussion shows that the behavior you describe when using hint() is expected because of the multikey index on Groups. Bounds cannot be intersected for a multikey index, because intersecting index bounds is only a valid if a field has at most one value. Intersection is implicitly an AND, but if there are multiple values for an array field, one value might satisfy one branch of the AND while a completely different value satisfies the other branch of the AND. For example, let’s say we have the document
You mention three indexes in your description:
In your case DateGroupIndex-FacebookShares is a multikey index. The predicates given on TSCreated generate bounds [-inf, 80] and [20, inf], but the query planner can't intersect them because of the multikey index on Groups, so it applies only one of these intervals as you see on the explain() output. When you remove hint() the query planner chooses a different index, DateBasicIndex-TwitterShares, which does calculate tight bounds for your query. Regards, | |||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 20/Aug/14 ] | |||||||||||||||||||||
|
Thanks giudicelli; I'm able to reproduce the behavior you describe using hint() with a small reproducer:
When removing hint() the behavior persists though, so while this doesn't exactly match your report we can still investigate. If you're able to tweak the reproducer above to match exactly the behavior you're seeing with/without hint() please let us know. Regards, | |||||||||||||||||||||
| Comment by Frédéric Giudicelli [ 20/Aug/14 ] | |||||||||||||||||||||
|
Indeed, "Groups" is an Array. However the "TSCreated" on which I'm having an issue is a plain Int. | |||||||||||||||||||||
| Comment by Ramon Fernandez Marina [ 19/Aug/14 ] | |||||||||||||||||||||
|
giudicelli, can you please send us a sample document? We're interested in knowing whether any of the document fields are arrays and this could be an issue with multikey indexes. Thanks, |