Details
Description
Suppose indexes {a:1} and {b:1} exist on a collection, and the user issues the following query:
find({a: {$gt: 0}, b: {$gt: 0}}).sort({b: 1})
|
The query planner will unconditionally choose (and cache) the plan using the {b:1} index if no documents match either the {a: {$gt: 0}} or {b: {$gt: 0}} predicates.
This is because both the {a:1} index plan and the {b:1} index plan have the same "productivity", but the plan using {b:1} hits EOF first because it does not have a blocking SORT stage.
Note that this issue is not limited to range predicates; it is reproducible with any bounds-generating predicates.
Reproduce with the following:
> db.foo.ensureIndex({a:1})
|
{
|
"createdCollectionAutomatically" : true, |
"numIndexesBefore" : 1, |
"numIndexesAfter" : 2, |
"ok" : 1 |
}
|
> db.foo.ensureIndex({b:1})
|
{
|
"createdCollectionAutomatically" : false, |
"numIndexesBefore" : 2, |
"numIndexesAfter" : 3, |
"ok" : 1 |
}
|
> db.foo.find({a:{$gt:0},b:{$gt:0}}).sort({b:1}).explain().cursor
|
BtreeCursor b_1
|
Plan cache detailed info:
> db.foo.find({a:{$gt:0},b:{$gt:0}}).sort({b:1})
|
> db.foo.getPlanCache().getPlansByQuery({a:{$gt:0},b:{$gt:0}},{},{b:1})
|
[
|
{
|
"details" : {
|
"solution" : "(index-tagged expression tree: tree=Node\n---Leaf \n---Leaf { b: 1.0 }, pos: 0\n)"
|
},
|
"reason" : {
|
"score" : 1.0003000000000002,
|
"stats" : {
|
"type" : "FETCH",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"alreadyHasObj" : 0,
|
"forcedFetches" : 0,
|
"matchTested" : 0,
|
"children" : [
|
{
|
"type" : "IXSCAN",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"keyPattern" : "{ b: 1.0 }",
|
"boundsVerbose" : "field #0['b']: (0.0, inf.0]",
|
"isMultiKey" : 0,
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 0,
|
"children" : [ ]
|
}
|
]
|
}
|
},
|
"feedback" : {
|
"nfeedback" : 0,
|
"averageScore" : 0,
|
"stdDevScore" : 0,
|
"scores" : [ ]
|
},
|
"filterSet" : false
|
},
|
{
|
"details" : {
|
"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { a: 1.0 }, pos: 0\n---Leaf { b: 1.0 }, pos: 0\n)"
|
},
|
"reason" : {
|
"score" : 1.0002,
|
"stats" : {
|
"type" : "FETCH",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"alreadyHasObj" : 0,
|
"forcedFetches" : 0,
|
"matchTested" : 0,
|
"children" : [
|
{
|
"type" : "AND_HASH",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"flaggedButPassed" : 0,
|
"flaggedInProgress" : 0,
|
"memUsage" : 0,
|
"memLimit" : 33554432,
|
"children" : [
|
{
|
"type" : "IXSCAN",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 1,
|
"keyPattern" : "{ a: 1.0 }",
|
"boundsVerbose" : "field #0['a']: (0.0, inf.0]",
|
"isMultiKey" : 0,
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 0,
|
"children" : [ ]
|
},
|
{
|
"type" : "IXSCAN",
|
"works" : 0,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"keyPattern" : "{}",
|
"boundsVerbose" : "field #0['b']: (0.0, inf.0]",
|
"isMultiKey" : 0,
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 0,
|
"children" : [ ]
|
}
|
]
|
}
|
]
|
}
|
},
|
"feedback" : {
|
|
|
},
|
"filterSet" : false
|
},
|
{
|
"details" : {
|
"solution" : "(index-tagged expression tree: tree=Node\n---Leaf { a: 1.0 }, pos: 0\n---Leaf \n)"
|
},
|
"reason" : {
|
"score" : 1.0002,
|
"stats" : {
|
"type" : "SORT",
|
"works" : 1,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"forcedFetches" : 0,
|
"memUsage" : 0,
|
"memLimit" : 33554432,
|
"children" : [
|
{
|
"type" : "KEEP_MUTATIONS",
|
"works" : 0,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"children" : [
|
{
|
"type" : "FETCH",
|
"works" : 0,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"alreadyHasObj" : 0,
|
"forcedFetches" : 0,
|
"matchTested" : 0,
|
"children" : [
|
{
|
"type" : "IXSCAN",
|
"works" : 0,
|
"yields" : 0,
|
"unyields" : 0,
|
"invalidates" : 0,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needFetch" : 0,
|
"isEOF" : 0,
|
"keyPattern" : "{}",
|
"boundsVerbose" : "field #0['a']: (0.0, inf.0]",
|
"isMultiKey" : 0,
|
"yieldMovedCursor" : 0,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0,
|
"matchTested" : 0,
|
"keysExamined" : 0,
|
"children" : [ ]
|
}
|
]
|
}
|
]
|
}
|
]
|
}
|
},
|
"feedback" : {
|
|
|
},
|
"filterSet" : false
|
}
|
]
|
>
|
Attachments
Issue Links
- is related to
-
SERVER-14034 Sorted $in query with large number of elements can't use merge sort
-
- Closed
-
- related to
-
-
- Closed
-
-
-
- Closed
-