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

Compound partialFilterExpression not being used - despite correct index chosen

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 4.0.10, 4.2.0-rc6
    • Component/s: 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 .

      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.

        2. execution-stats-4.0.10.json
          5 kB
          Eliott Coyac
        3. execution-stats-4.2.0-rc6.json
          5 kB
          Eliott Coyac

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

              Created:
              Updated:
              Resolved: