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
}
]
>
- is related to
-
SERVER-14034 Sorted $in query with large number of elements can't use merge sort
-
- Closed
-
- related to
-
-
- Closed
-
-
-
- Closed
-