Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-31122

Count by regex match on index is not a covered query

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.4.7
    • Component/s: Querying
    • Labels:
      None
    • ALL
    • Hide

      Use case

          use db1
          db.foo.insert({"keyword": "abc"})
          db.foo.insert({"keyword": "acd"})
          db.foo.createIndex({"keyword":1})
          db.bar.createIndex(index)
          db.foo.explain("executionStats").count({"keyword":/a/i})
      

      Execution stats

          "executionStats" : {
              "nReturned" : 0,
              "totalKeysExamined" : 2,
              "totalDocsExamined" : 2
          }
      

      The confusion is:

      Why examine the documents even if the index has covered the querying field?

      What we found is:

      There is still a FETCH stage is used. And I think this stage can be optimized by removing.

      Details:

        {
              "nReturned" : 0,
              "totalKeysExamined" : 2,
              "totalDocsExamined" : 2,
              "executionStages" : {
                  "stage" : "COUNT",
                  "nReturned" : 0,
                  "nCounted" : 2,
                  "inputStage" : {
                      "stage" : "FETCH",
                      "nReturned" : 2,
                      "docsExamined" : 2,
                      "inputStage" : {
                          "stage" : "IXSCAN",
                          "nReturned" : 2,
                          "indexName" : "keyword_1",
                          "keysExamined" : 2
                      }
                  }
              }
          }
      
      
      Show
      Use case use db1 db.foo.insert({ "keyword" : "abc" }) db.foo.insert({ "keyword" : "acd" }) db.foo.createIndex({ "keyword" :1}) db.bar.createIndex(index) db.foo.explain( "executionStats" ).count({ "keyword" :/a/i}) Execution stats "executionStats" : { "nReturned" : 0, "totalKeysExamined" : 2, "totalDocsExamined" : 2 } The confusion is: Why examine the documents even if the index has covered the querying field? What we found is: There is still a FETCH stage is used. And I think this stage can be optimized by removing. Details: { "nReturned" : 0, "totalKeysExamined" : 2, "totalDocsExamined" : 2, "executionStages" : { "stage" : "COUNT" , "nReturned" : 0, "nCounted" : 2, "inputStage" : { "stage" : "FETCH" , "nReturned" : 2, "docsExamined" : 2, "inputStage" : { "stage" : "IXSCAN" , "nReturned" : 2, "indexName" : "keyword_1" , "keysExamined" : 2 } } } }

      When all of the querying fields are defined in the index, there is no need to fetch documents for a count() query by regex match on that index.

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            huyingming Alex Hu
            Votes:
            0 Vote for this issue
            Watchers:
            8 Start watching this issue

              Created:
              Updated:
              Resolved: