samplingCE: Cardinality estimate for indexes that are read backwards is 0

XMLWordPrintableJSON

    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide 
      db.foo.drop();
db.adminCommand({setParameter: 1, planRankerMode: "samplingCE"});
db.foo.insert({a:1,b:1});
db.foo.createIndex({a:1});
db.foo.createIndex({b:-1});

Enterprise test> db.foo.find({}).sort({a:1}).explain().queryPlanner.winningPlan.cardinalityEstimate;
1
Enterprise test> db.foo.find({}).sort({a:-1}).explain().queryPlanner.winningPlan.cardinalityEstimate;
0 <- incorrect
 Enterprise test> db.foo.find({}).sort({b:-1}).explain().queryPlanner.winningPlan.cardinalityEstimate;
0 <- incorrect

       

      Show
      db.foo.drop(); db.adminCommand({setParameter: 1, planRankerMode: "samplingCE" }); db.foo.insert({a:1,b:1}); db.foo.createIndex({a:1}); db.foo.createIndex({b:-1}); Enterprise test> db.foo.find({}).sort({a:1}).explain().queryPlanner.winningPlan.cardinalityEstimate; 1 Enterprise test> db.foo.find({}).sort({a:-1}).explain().queryPlanner.winningPlan.cardinalityEstimate; 0 <- incorrect Enterprise test> db.foo.find({}).sort({b:-1}).explain().queryPlanner.winningPlan.cardinalityEstimate; 0 <- incorrect  
    • 0
    • None
    • None
    • None
    • None
    • None
    • None
    • None
    • 0

      If an index is read backwards, either because the index was defined with a direction of -1 or the sort predicate itself has a direction of -1, the cardinality estimate and the cost are zero.

      This causes the plan that sorts using an index to be picked always, at the expense of any other plans, often with disastrous results.

            Assignee:
            Maddie Zechar
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: