-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
3
-
None
-
None
-
None
-
None
-
None
-
None
-
None
One of the decisions CBR needs to make is whether to do an IXSCAN on the $sort key followed by a FETCH or an IXSCAN on the $match key followed by SORT.
For a lot of queries, CBR does the right thing but in the example below it select a worse plan than multiPlanning.
db.foo.drop(); var docs = []; for (let i = 0; i < 1000000; i++) { docs.push({a:i}); } db.foo.insertMany(docs); db.foo.createIndex({a: 1}); db.foo.createIndex({b: 1}); db.foo.runCommand({analyze: "foo", key: "a"}); db.foo.runCommand({analyze: "foo", key: "b"}); db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"}); db.foo.getPlanCache().clear(); const start = Date.now(); db.foo.aggregate([{$match: {"a" : {$lt: 100000}}},{$sort: {"b": 1}}, {$addFields: {"x" : "a"}},{$match: {x: "b"}}]); <- PICKS A NON-OPTIMAL PLAN Date.now()-start; db.foo.getPlanCache().clear(); const start = Date.now(); db.foo.aggregate([{$match: {"a" : {$lt: 100000}}},{$sort: {"b": 1}}, {$addFields: {"x" : "a"}},{$match: {x: "b"}}], {hint: "a_1"}); <- FASTER PLAN Date.now()-start; db.foo.getPlanCache().clear(); const start = Date.now(); db.foo.aggregate([{$match: {"a" : {$lt: 100000}}},{$sort: {"b": 1}}, {$addFields: {"x" : "a"}},{$match: {x: "b"}}], {hint: "b_1"}); Date.now()-start; db.foo.getPlanCache().clear(); db.adminCommand({setParameter: 1, planRankerMode: "multiPlanning"}); const start = Date.now(); db.foo.aggregate([{$match: {"a" : {$lt: 100000}}},{$sort: {"b": 1}}, {$addFields: {"x" : "a"}},{$match: {x: "b"}}]); Date.now()-start;