[SERVER-42647] Compound partialFilterExpression not being used - despite correct index chosen Created: 06/Aug/19  Updated: 09/Aug/19  Resolved: 09/Aug/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 4.0.10, 4.2.0-rc6
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Eliott Coyac Assignee: Danny Hatcher (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Zip Archive data.zip     File execution-stats-4.0.10.json     File execution-stats-4.2.0-rc6.json    
Issue Links:
Duplicate
duplicates SERVER-28889 Partial index shouldn't do fetch on c... Closed
Operating System: ALL
Steps To Reproduce:

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.

Participants:

 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.



 Comments   
Comment by Eliott Coyac [ 09/Aug/19 ]

This seems to be a duplicate indeed. Thank you.

Comment by Danny Hatcher (Inactive) [ 09/Aug/19 ]

Thanks for your report. You are correct that there is still a FETCH stage. We have SERVER-28889 open to address this issue for partial indexes. As such, I'll close this ticket as a duplicate. Please let me know if you think that SERVER-28889 isn't a match for what you're seeing.

Generated at Thu Feb 08 05:01:03 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.