[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:
Duplicate
duplicates SERVER-15086 Allow for efficient range queries ove... Closed
Related
is related to SERVER-15059 index on date range on embedded colle... Closed
is related to SERVER-15985 plan change in 2.6.5 causes query to ... Closed
Operating System: ALL
Steps To Reproduce:

I have the following two indexes:

{Groups: 1, Duplicate: 1, NotFound: 1, DontShow: 1, TSCreated: 1, FacebookShares: -1}, {name: 'DateGroupIndex-FacebookShares'}
{Groups: 1, Duplicate: 1, NotFound: 1, DontShow: 1, TSCreated: 1, TwitterShares: -1}, {name: 'DateGroupIndex-TwitterShares'}

A) When I run the following query:

.find({Groups:1,Duplicate:0,NotFound:0,DontShow:0,TSCreated:{$lt:1405695600,$gt:1405609200}}).hint("DateGroupIndex-FacebookShares").limit(10).sort({FacebookShares: -1}).explain()
 
"cursor" : "BtreeCursor DateGroupIndex-FacebookShares"
...
"indexBounds": {
...
"TSCreated" : [
		[
				-Infinity,
				1405695600
		]
],
...}

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:

"cursor" : "BtreeCursor DateBasicIndex-TwitterShares"
...
"indexBounds": {
...
"TSCreated" : [
		[
				1405609200,
				1405695600
		]
],
...}
 

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:
db.foo.find({a:1, b:{$lt:80, $gt:20}}).hint("idx1").explain()

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

{a: [5, 7]}

This document satisfies the query

{$and: [{a: 5}, {a: 7}]}

For the index {a: 1} we have the keys {“”: 5} and {“”: 7}. Each child of the AND is tagged with the index {a: 1}. The interval for the {a: 5} branch is [5, 5]. The interval for the {a: 7} branch is [7, 7]. The intersection of the intervals is empty. Therefore, if we scan over the empty intersection we will retrieve nothing.

You mention three indexes in your description:

  • DateGroupIndex-FacebookShares
  • DateGroupIndex-TwitterShares
  • DateBasicIndex-TwitterShares

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,
Ramón.

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:

db.foo.drop()
 
for (i=0; i<10000; i++) {
    db.foo.insert({a: [
      Math.floor(100 * Math.random()),
      Math.floor(100 * Math.random()) ],
    b : Math.floor(100 * Math.random()),
    c : Math.floor(100 * Math.random())})
}
 
db.foo.ensureIndex({a: 1, b:-1}, {name: "idx1"})
db.foo.ensureIndex({a: 1, c:-1}, {name: "idx2"})
 
db.foo.find({a:[1, 2], b:{$lt:80, $gt:20}}).hint("idx1").explain()
...
                "b" : [
                        [
                                80,
                                -Infinity
                        ]
                ]

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,
Ramón.

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,
Ramón.

Generated at Thu Feb 08 03:35:25 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.