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
-
SERVER-15005 Connections Spike on Secondary, Load Jumps, Server Becomes Unresponsive
- Closed
-
SERVER-17260 Wrong index is often chosen for query: { "foo": "fooValue", "_id": { "$gte": "idValue" } }, sort: { "_id": 1 }
- Closed