-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
Forward and backward scans are costed identically, so a backward scan may be chosen. This also applies to the multiplanner.
I think forward scans should be preferred , if not strictly for performance reasons, in order to have more queries use one of the indexes only producing better cache hit rates. For explicit descending sort, a forward scan on the descending index should be preferred.
coll = db.foo; coll.drop(); for (let i = 0; i < 1000; i++) { coll.insertMany([{a: i}]); } coll.createIndex({a:1}); coll.createIndex({a:-1}); db.adminCommand({setParameter: 1, planRankerMode: "multiPlanning"}); db.runCommand({analyze: "foo", key: "a"}); db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"}); db.foo.aggregate([{$match: {a:1}}]).explain().queryPlanner.winningPlan; db.foo.aggregate([]).sort({a:1}).explain().queryPlanner.winningPlan; db.foo.aggregate([]).sort({a:-1}).explain().queryPlanner.winningPlan;