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

Compound partialFilterExpression not being used - despite correct index chosen

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • 4.0.10, 4.2.0-rc6
    • None
    • Index Maintenance, Querying
    • None
    • ALL
    • Hide

      Restore the attached collection 'data.companies-test' (a much lighter version of my dataset).

      In a shell, run the following commands:

      use data
      db.getCollection('companies-test').createIndex({"financial.gross": -1}, {partialFilterExpression: {"state": "open", "financial.gross": {$exists: true}}, name: "company-test-open-gross"});
      

      Wait for the index to be finished, then run the command:

      use data
      db.getCollection('companies-test').explain("executionStats").count({"financial.gross": {$exists: true}, state: "open"})
      

      Witness that there is a filter step in the execution stages, on both financial.gross and state.

      Show
      Restore the attached collection 'data.companies-test' (a much lighter version of my dataset). In a shell, run the following commands: use data db.getCollection( 'companies-test' ).createIndex({ "financial.gross" : -1}, {partialFilterExpression: { "state" : "open" , "financial.gross" : {$exists: true }}, name: "company-test-open-gross" }); Wait for the index to be finished, then run the command: use data db.getCollection( 'companies-test' ).explain( "executionStats" ).count({ "financial.gross" : {$exists: true }, state: "open" }) Witness that there is a filter step in the execution stages, on both financial.gross and state .

    Description

      I have this index on my collection:

      db.getCollection('companies').createIndex({"financial.gross": -1}, {partialFilterExpression: {"state": "open", "financial.gross": {$exists: true}}, name: "company-open-gross"});
      

      Then I want to count the documents in the index:

      db.getCollection('companies').explain("executionStats").count({"financial.gross": {$exists: true}, state: "open"})
      

      It does use the index created, but there is still a filter stage where both financial.gross and state are tested, as seen in the execution stats (attached). It shouldn't filter at all, since those conditions are the exact same as the partialFilterExpression.

      Ironically, doing something like:

      db.getCollection('companies').explain("executionStats").count({"financial.gross": {$gte: 0}, state: "open"})
      

       is faster since then it only filters on state.

      Attachments

        1. data.zip
          3.67 MB
        2. execution-stats-4.0.10.json
          5 kB
        3. execution-stats-4.2.0-rc6.json
          5 kB

        Issue Links

          Activity

            People

              daniel.hatcher@mongodb.com Danny Hatcher (Inactive)
              eliott.coyac@gmail.com Eliott Coyac
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: