-
Type:
Improvement
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
The index
{a:1}and the index
{a:1, b:1}have the same cost . This causes the shorter index to be used in case the longer index would be more appropriate and vice versa.
In those cases, the multiplanner picks the correct index at all times.
coll = db.foo; coll.drop(); for (let i = 0; i < 1000; i++) { coll.insertMany([{a: i,b:1}]); } coll.createIndex({a:1}); coll.createIndex({a:1, b:1}); db.adminCommand({setParameter: 1, planRankerMode: "multiPlanning"}); db.runCommand({analyze: "foo", key: "a"}); db.runCommand({analyze: "foo", key: "b"}); db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
1. a_1 and a_1_b_1 have identical costs, so a_1 is correctly chosen purely by accident
db.foo.aggregate([{$match: {a:1}}]).explain().queryPlanner;
2. The shorter index a_1 is incorrectly chosen because the cardinalityEstimate for "new_value_since_last_analyze" is zero, making all the costs zero-ish. The safer choice would be a_1_b_1, in case the histogram has become stale
db.foo.aggregate([{$match: {a:"new_value_since_last_anaylze", b:1}}]).explain().queryPlanner;
3. The longer index a_1_b_1 is correctly chosen as it has the tiniest advantage in cost
db.foo.aggregate([{$match: {a:1, b:1}}]).explain().queryPlanner;
4. The shorter index a_1 is incorrectly chosen because samplingCE reports cardinalityEstimate=0
db.adminCommand({setParameter: 1, planRankerMode: "samplingCE"});
db.foo.aggregate([{$match: {a:"very_rare_value", b:1}}]).explain().queryPlanner;
- duplicates
-
-
- Backlog
-
- related to
-
-
- Backlog
-