Estimate of OR stage over disjoint intervals should be equal to the sum of inputs

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Won't Fix
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide 
      const dataset = [ { "a" : 1, "b" : 1, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 4 }, { "a" : 1, "b" : 1, "c" : 3 }, { "a" : 2, "b" : 3, "c" : 5 }, { "a" : 2, "b" : 2, "c" : 5 }, { "a" : 4, "b" : 3, "c" : 2 }, { "a" : 3, "b" : 2, "c" : 5 }, { "a" : 2, "b" : 2, "c" : 1 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 1, "b" : 3, "c" : 4 }, { "a" : 3, "b" : 3, "c" : 5 }, { "a" : 3, "b" : 1, "c" : 1 }, { "a" : 2, "b" : 2, "c" : 4 }, { "a" : 1, "b" : 2, "c" : 1 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 3 }, { "a" : 1, "b" : 1, "c" : 5 }, { "a" : 5, "b" : 5, "c" : 2 }, { "a" : 1, "b" : 3, "c" : 3 }, { "a" : 4, "b" : 2, "c" : 5 }, { "a" : 2, "b" : 4, "c" : 5 }, { "a" : 4, "b" : 2, "c" : 3 }, { "a" : 4, "b" : 5, "c" : 4 }, { "a" : 2, "b" : 3, "c" : 2 }, { "a" : 2, "b" : 1, "c" : 2 }, { "a" : 5, "b" : 4, "c" : 5 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 2 }, { "a" : 1, "b" : 1, "c" : 4 }, { "a" : 1, "b" : 3, "c" : 1 }, { "a" : 3, "b" : 3, "c" : 1 }, { "a" : 3, "b" : 4, "c" : 1 }, { "a" : 1, "b" : 4, "c" : 4 }];

const indexes = [ { "a" : 1} ];
const predicate = {  "$and" : [ { "$or" : [ { "a" : { "$gte" : 5 } }, { "a" : { "$gte" : 2 } } ] }, { "$or" : [ { "$and" : [  { "a" : { "$eq" : 3 } } ] }, { "$and" : [ { "a" : { "$gte" : 2 } }, { "a" : { "$eq" : 4 } } ] } ] } ] };

db.foo.drop();
db.foo.insert(dataset);
for (const index of indexes) {
   db.foo.createIndex(index);
}

db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
db.foo.runCommand({analyze: "foo", key: "a"});
db.foo.runCommand({analyze: "foo", key: "b"});
db.foo.runCommand({analyze: "foo", key: "c"});
db.foo.find(predicate).explain().queryPlanner.winningPlan;
db.foo.find(predicate).count();
      Show
      const dataset = [ { "a" : 1, "b" : 1, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 4 }, { "a" : 1, "b" : 1, "c" : 3 }, { "a" : 2, "b" : 3, "c" : 5 }, { "a" : 2, "b" : 2, "c" : 5 }, { "a" : 4, "b" : 3, "c" : 2 }, { "a" : 3, "b" : 2, "c" : 5 }, { "a" : 2, "b" : 2, "c" : 1 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 1, "b" : 3, "c" : 4 }, { "a" : 3, "b" : 3, "c" : 5 }, { "a" : 3, "b" : 1, "c" : 1 }, { "a" : 2, "b" : 2, "c" : 4 }, { "a" : 1, "b" : 2, "c" : 1 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 3 }, { "a" : 1, "b" : 1, "c" : 5 }, { "a" : 5, "b" : 5, "c" : 2 }, { "a" : 1, "b" : 3, "c" : 3 }, { "a" : 4, "b" : 2, "c" : 5 }, { "a" : 2, "b" : 4, "c" : 5 }, { "a" : 4, "b" : 2, "c" : 3 }, { "a" : 4, "b" : 5, "c" : 4 }, { "a" : 2, "b" : 3, "c" : 2 }, { "a" : 2, "b" : 1, "c" : 2 }, { "a" : 5, "b" : 4, "c" : 5 }, { "a" : 1, "b" : 5, "c" : 3 }, { "a" : 5, "b" : 2, "c" : 2 }, { "a" : 1, "b" : 1, "c" : 4 }, { "a" : 1, "b" : 3, "c" : 1 }, { "a" : 3, "b" : 3, "c" : 1 }, { "a" : 3, "b" : 4, "c" : 1 }, { "a" : 1, "b" : 4, "c" : 4 }]; const indexes = [ { "a" : 1} ]; const predicate = { "$and" : [ { "$or" : [ { "a" : { "$gte" : 5 } }, { "a" : { "$gte" : 2 } } ] }, { "$or" : [ { "$and" : [ { "a" : { "$eq" : 3 } } ] }, { "$and" : [ { "a" : { "$gte" : 2 } }, { "a" : { "$eq" : 4 } } ] } ] } ] }; db.foo.drop(); db.foo.insert(dataset); for ( const index of indexes) { db.foo.createIndex(index); } db.adminCommand({setParameter: 1, planRankerMode: "histogramCE" }); db.foo.runCommand({analyze: "foo" , key: "a" }); db.foo.runCommand({analyze: "foo" , key: "b" }); db.foo.runCommand({analyze: "foo" , key: "c" }); db.foo.find(predicate).explain().queryPlanner.winningPlan; db.foo.find(predicate).count();
    • QO 2025-02-03
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      IF the OR stage has two inputs that are over the same index, their cardinalities should be added directly without any complications:

       inputStage: {
    stage: 'OR',
    costEstimate: 0.0317433,
    cardinalityEstimate: 6.751767736289423, <- INCORRECT, should be 4 + 5
    estimatesMetadata: { ceSource: 'Histogram' },
    inputStages: [
      {
        stage: 'IXSCAN',
        costEstimate: 0.0156698,
        cardinalityEstimate: 4,
        numKeysEstimate: 4,
        estimatesMetadata: { ceSource: 'Histogram' },
        keyPattern: { a: 1 },
        indexName: 'a_1',
        isMultiKey: false,
        multiKeyPaths: { a: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { a: [ '[4, 4]' ] }
      },
      {
        stage: 'IXSCAN',
        costEstimate: 0.0160735,
        cardinalityEstimate: 5,
        numKeysEstimate: 5,
        estimatesMetadata: { ceSource: 'Histogram' },
        keyPattern: { a: 1 },
        indexName: 'a_1',
        isMultiKey: false,
        multiKeyPaths: { a: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { a: [ '[3, 3]' ] }
      }
    ]
  }

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

              Created:
              Updated:
              Resolved: