costEstimate of IXSCAN is incorrect in the presence of an unrelated hashed index

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • ALL
    • Hide 
      db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
db.foo.drop();
db.foo.insert({a:1,b:1});
db.foo.createIndex({b:1});
db.foo.createIndex({a:'hashed'});
db.foo.runCommand({analyze: "foo", key: "a"});
db.foo.runCommand({analyze: "foo", key: "b"});
db.foo.find({a:1,b:1}).explain().queryPlanner.rejectedPlans[0].inputStage.inputStages[0].costEstimate;
      Show
      db.adminCommand({setParameter: 1, planRankerMode: "histogramCE" }); db.foo.drop(); db.foo.insert({a:1,b:1}); db.foo.createIndex({b:1}); db.foo.createIndex({a: 'hashed' }); db.foo.runCommand({analyze: "foo" , key: "a" }); db.foo.runCommand({analyze: "foo" , key: "b" }); db.foo.find({a:1,b:1}).explain().queryPlanner.rejectedPlans[0].inputStage.inputStages[0].costEstimate;
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      IF we have a hashed index on a, a cost estimate on b is incorrect, even though the cardinality estimate is correct.

      Right now any plans involving a hashed index will not be put through the CBR, so the fact that the cost estimate is incorrect does not seem to matter.

      Enterprise test> db.foo.find({a:1,b:1}).explain().queryPlanner.rejectedPlans[0].inputStage;
{
  stage: 'AND_SORTED',
  inputStages: [
    {
      stage: 'IXSCAN',
      costEstimate: 1.7976931348623157e+308, <- HERE
      cardinalityEstimate: 1,
      numKeysEstimate: 1,
      estimatesMetadata: { ceSource: 'Histogram' },
      keyPattern: { b: 1 },
      indexName: 'b_1',
      isMultiKey: false,
      multiKeyPaths: { b: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { b: [ '[1, 1]' ] }
    },
    {
      stage: 'IXSCAN',
      keyPattern: { a: 'hashed' },
      indexName: 'a_hashed',
      isMultiKey: false,
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { a: [ '[5902408780260971510, 5902408780260971510]' ] }
    }
  ]
}

      Compare with the estimate for the same index and interval if it is in a stand-alone IXSCAN:

         inputStage: {
      stage: 'IXSCAN',
      costEstimate: 0.0144587,<- THIS VALUE IS MORE REASONABLE
      cardinalityEstimate: 1,
      numKeysEstimate: 1,
      estimatesMetadata: { ceSource: 'Histogram' },
      keyPattern: { b: 1 },
      indexName: 'b_1',
      isMultiKey: false,
      multiKeyPaths: { b: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: { b: [ '[1, 1]' ] }
    }
  },

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

              Created:
              Updated: