-
Type: Bug
-
Resolution: Duplicate
-
Priority: Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
None
-
ALL
Creating documents of this form with a compound index:
> for (var i = 0; i < 5000; ++i) { var arr = []; for (var j = 0; j < 10; ++j) { arr.push({n: "prop" + j, v: Math.floor(Math.random() * 1000) }) }; db.generic.insert({props: arr}) } > db.generic.findOne() { "_id": ObjectId("515dd3b4f0bd676b816aa9b0"), "props": [ { "n": "prop0", "v": 40 }, { "n": "prop1", "v": 198 }, ... { "n": "prop9", "v": 652 } ] } > db.generic.ensureIndex({"props.n": 1, "props.v": 1})
Matching with equality on the value works well:
> db.generic.find({props:{$elemMatch:{n:'prop1', v:800}}}).explain() { "cursor" : "BtreeCursor props.n_1_props.v_1", "isMultiKey" : true, "n" : 143, "nscannedObjects" : 143, "nscanned" : 143, "nscannedObjectsAllPlans" : 143, "nscannedAllPlans" : 143, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 11, "indexBounds" : { "props.n" : [ [ "prop1", "prop1" ] ], "props.v" : [ [ 800, 800 ] ] }, "server" : "agmac.local:27017" }
Range queries on value do not work well though, see the upper bound is largest possible integer:
> db.generic.find({props:{$elemMatch:{n:'prop1', v:{$gt:800, $lt: 1000}}}}).explain() { "cursor" : "BtreeCursor props.n_1_props.v_1", "isMultiKey" : true, "n" : 25287, "nscannedObjects" : 25287, "nscanned" : 25287, "nscannedObjectsAllPlans" : 25287, "nscannedAllPlans" : 25287, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 114, "indexBounds" : { "props.n" : [ [ "prop1", "prop1" ] ], "props.v" : [ [ 800, 1.7976931348623157e+308 ] ] }, "server" : "agmac.local:27017" }
If stacking a 2nd $elemMatch (just for kicks) then the ranges seem correct in explain but it does not match any document:
> db.generic.find({props:{$elemMatch:{n:'prop1', v:{$elemMatch: {$gt:800, $lt: 1000}}}}}).explain() { "cursor" : "BtreeCursor props.n_1_props.v_1", "isMultiKey" : true, "n" : 0, "nscannedObjects" : 25287, "nscanned" : 25287, "nscannedObjectsAllPlans" : 25287, "nscannedAllPlans" : 25287, "scanAndOrder" : false, "indexOnly" : false, "nYields" : 0, "nChunkSkips" : 0, "millis" : 224, "indexBounds" : { "props.n" : [ [ "prop1", "prop1" ] ], "props.v" : [ [ 800, 1000 ] ] }, "server" : "agmac.local:27017" }
- duplicates
-
SERVER-15086 Allow for efficient range queries over non-array fields in multikey indices
- Closed
- is duplicated by
-
SERVER-10880 $elemMatch doesn't effectively use indexes
- Closed
- is related to
-
SERVER-10394 when using a generic index over a list of subobjects, $elemMatch query does not properly apply bounds across data types
- Backlog