histogramCE: $in, $nin, $or not estimated without an index

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Duplicate
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide 
      db.foo.drop();

let docs = [];
for (let i = 0; i < 50; i++) {
    docs.push({a: i});
}


db.foo.insertMany(docs);
db.foo.runCommand({analyze: "foo", key: "a"});
db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
db.foo.find({a: {$in: [1,2]}}).count();
db.foo.find({a: {$in: [1,2]}}).explain().queryPlanner.winningPlan.cardinalityEstimate;
      Show
      db.foo.drop(); let docs = []; for (let i = 0; i < 50; i++) { docs.push({a: i}); } db.foo.insertMany(docs); db.foo.runCommand({analyze: "foo" , key: "a" }); db.adminCommand({setParameter: 1, planRankerMode: "histogramCE" }); db.foo.find({a: {$in: [1,2]}}).count(); db.foo.find({a: {$in: [1,2]}}).explain().queryPlanner.winningPlan.cardinalityEstimate;
    • QO 2025-02-03
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      $in, $nin, $or are not estimated with histogramCE, they fall back to heuristicCS.

      The same predicates are correctly estimated with an index, therefore there is no obvious reason that prevents them from being estimated without one – the information in the histograms should be sufficient. In all cases, a single column is involved, therefore the estimate of the $in should be the sum of the cardinalities of its constituent values.

            Assignee:
            Timour Katchaounov
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Created:
              Updated:
              Resolved: