Details
-
Bug
-
Resolution: Duplicate
-
Major - P3
-
None
-
None
-
None
-
ALL
-
Hide
Run the script above. Output is:
WriteResult({ "nInserted" : 1 })WriteResult({ "nInserted" : 1 })WriteResult({ "nInserted" : 1 }){"createdCollectionAutomatically" : false,"numIndexesBefore" : 2,"numIndexesAfter" : 2,"note" : "all indexes already exist","ok" : 1}{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.foo","indexFilterSet" : false,"parsedQuery" : {},"winningPlan" : {"stage" : "PROJECTION","transformBy" : {"_id" : 0,"price" : 1,"customerid" : 1},"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"price" : 1,"customerid" : 1},"indexName" : "price_1_customerid_1","isMultiKey" : false,"multiKeyPaths" : {"price" : [ ],"customerid" : [ ]},"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 2,"direction" : "forward","indexBounds" : {"price" : ["[MinKey, MaxKey]"],"customerid" : ["[MinKey, MaxKey]"]}}},"rejectedPlans" : [ ]},"serverInfo" : {"host" : "nuc1","port" : 27017,"version" : "3.4.6-1.7","gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"},"ok" : 1}{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.foo","indexFilterSet" : false,"parsedQuery" : {"customerid" : {"$lt" : 0}},"winningPlan" : {"stage" : "PROJECTION","transformBy" : {"_id" : 0,"price" : 1,"customerid" : 1},"inputStage" : {"stage" : "FETCH","filter" : {"customerid" : {"$lt" : 0}},"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"price" : 1,"customerid" : 1},"indexName" : "price_1_customerid_1","isMultiKey" : false,"multiKeyPaths" : {"price" : [ ],"customerid" : [ ]},"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 2,"direction" : "forward","indexBounds" : {"price" : ["[MinKey, MaxKey]"],"customerid" : ["[MinKey, MaxKey]"]}}}},"rejectedPlans" : [ ]},"serverInfo" : {"host" : "nuc1","port" : 27017,"version" : "3.4.6-1.7","gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"},"ok" : 1}{"queryPlanner" : {"plannerVersion" : 1,"namespace" : "test.foo","indexFilterSet" : false,"parsedQuery" : {"$and" : [{"customerid" : {"$lt" : 0}},{"price" : {"$gte" : 0}}]},"winningPlan" : {"stage" : "PROJECTION","transformBy" : {"_id" : 0,"price" : 1,"customerid" : 1},"inputStage" : {"stage" : "IXSCAN","keyPattern" : {"price" : 1,"customerid" : 1},"indexName" : "price_1_customerid_1","isMultiKey" : false,"multiKeyPaths" : {"price" : [ ],"customerid" : [ ]},"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 2,"direction" : "forward","indexBounds" : {"price" : ["[0.0, inf.0]"],"customerid" : ["[-inf.0, 0.0)"]}}},"rejectedPlans" : [ ]},"serverInfo" : {"host" : "nuc1","port" : 27017,"version" : "3.4.6-1.7","gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"},"ok" : 1}byeShowRun the script above. Output is: WriteResult({ "nInserted" : 1 }) WriteResult({ "nInserted" : 1 }) WriteResult({ "nInserted" : 1 }) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 2, "note" : "all indexes already exist", "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[MinKey, MaxKey]" ], "customerid" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "customerid" : { "$lt" : 0 } }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "FETCH", "filter" : { "customerid" : { "$lt" : 0 } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[MinKey, MaxKey]" ], "customerid" : [ "[MinKey, MaxKey]" ] } } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.foo", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "customerid" : { "$lt" : 0 } }, { "price" : { "$gte" : 0 } } ] }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "_id" : 0, "price" : 1, "customerid" : 1 }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1, "customerid" : 1 }, "indexName" : "price_1_customerid_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ], "customerid" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "[0.0, inf.0]" ], "customerid" : [ "[-inf.0, 0.0)" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "nuc1", "port" : 27017, "version" : "3.4.6-1.7", "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2" }, "ok" : 1 } bye
Description
In my test case below I expect the 3 queries at the end to be index only, but the second one isn't. The third query is like the second but it also references the first column in the index (price) and that query is index only.
db.foo.insert({ price:0, customerid:0});
|
db.foo.insert({ price:1, customerid:1});
|
db.foo.insert({ price:2, customerid:2});
|
|
|
db.foo.createIndex({ price:1, customerid:1});
|
|
|
db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
|
|
mdcallag@nuc1:~/b/pmon346$
|
mdcallag@nuc1:~/b/pmon346$
|
mdcallag@nuc1:~/b/pmon346$ cat bug.js
|
|
|
db.foo.insert({ price:0, customerid:0});
|
db.foo.insert({ price:1, customerid:1});
|
db.foo.insert({ price:2, customerid:2});
|
|
|
db.foo.createIndex({ price:1, customerid:1});
|
|
|
db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
|
Attachments
Issue Links
- duplicates
-
SERVER-13197 Tighten index bounds and allow compound index to be chosen when predicate on leading field is not provided
-
- Backlog
-