Compound partialFilterExpression not being used - despite correct index chosen

XMLWordPrintableJSON

    • Type: Bug
    • Resolution: Duplicate
    • Priority: 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 .
    • None
    • 3
    • None
    • None
    • None
    • None
    • None
    • None

      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.

        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

            Assignee:
            Danny Hatcher (Inactive)
            Reporter:
            Eliott Coyac
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: