samplingCE: cardinalityEstimate is incorrect for $regex that is not a perfect prefix match

XMLWordPrintableJSON

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Blocker - P1
    • None
    • Affects Version/s: None
    • Component/s: None
    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      With samplingCE, the cardinalityEstimate for $regex is incorrectly always zero while the numKeysEstimate is correct.

      To reproduce:

      db.foo.drop();
      
      for (let i = 0; i < 1000; i++) {
          db.foo.insert({a: 'c'});
          db.foo.insert({a: 1});
      }
      db.foo.createIndex({a: 1});
      
      Enterprise test> db.foo.find({a: { '$regex': /c/ } } ).explain().queryPlanner.winningPlan;
      {
        isCached: false,
        stage: 'FETCH',
        costEstimate: 0.4352537,
        cardinalityEstimate: 0,
        estimatesMetadata: { ceSource: 'Sampling' },
        inputStage: {
          stage: 'IXSCAN',
          costEstimate: 0.427765,
          cardinalityEstimate: 0, <<<=== WRONG
          numKeysEstimate: 1000,
          estimatesMetadata: { ceSource: 'Sampling' },
          filter: { a: { '$regex': 'c' } },
          keyPattern: { a: 1 },
          indexName: 'a_1',
          isMultiKey: false,
          multiKeyPaths: { a: [] },
          isUnique: false,
          isSparse: false,
          isPartial: false,
          indexVersion: 2,
          direction: 'forward',
          indexBounds: { a: [ '["", {})', '[/c/, /c/]' ] }
        }
      }
      

            Assignee:
            Kartal Kaan Bozdogan
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: