[SERVER-41594] Covered query don't use index for range $gt+$lt. Only for one side and FETCH filter for another Created: 10/Jun/19  Updated: 12/Jun/19  Resolved: 12/Jun/19

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 4.0.9, 4.0.10
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Bocharov Vladislav Assignee: Eric Sedor
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File signedTest.json    
Issue Links:
Duplicate
duplicates SERVER-23118 Update index bounds generation rules ... Backlog
is duplicated by SERVER-41590 Covered query делает FETCH Closed
Related
is related to SERVER-6720 Range query with compound multikey in... Closed
is related to SERVER-35223 $elemMatch forces mongo to fetch docu... Closed
is related to SERVER-23118 Update index bounds generation rules ... Backlog
Participants:

 Description   

There is field contracts, that contain array of objects, that objects have indexed string field dateSigned (example value "2016-09-23T18:02:00+02:02"). Query with count and $lt (same for $gt) use index and all works great, stage COUNT_SCAN. But query with both $gt and $lt use index only for one bound and FETCH for another, that cause very poor perfomence.

Why index can't use both bounds?

Also I try to convert string value of dateSigned to ISODate, add new field dateSignedDate. Create index on that field - works same way.



 Comments   
Comment by Eric Sedor [ 12/Jun/19 ]

Thanks devbocharov. We are going to close this ticket as a duplicate of SERVER-23118, which is an effort to improve the generation of index bounds (independent of implementation details). You can watch that ticket for updates.

Comment by Bocharov Vladislav [ 12/Jun/19 ]

UPD: I understand that my approach will also give wrong result sometimes if array has more then one elements, so it probably can be done (at least I have no suggestions how do this).
So you can close this issue.

UPD2: One developer came up with such idea: add number of element of the array before recordId to index. So for two index scans we will get two sets like {(5, recId12), (0, recId434), ...} and intersect of it will give correct results.
What about such index upgrade or array of fields? Maybe it already in work?

Comment by Bocharov Vladislav [ 12/Jun/19 ]

Thanks for replay, I read your sources. So problem is that: when we do only one side search, we get correct result, but when we add another bound we get another results, that maybe don't match first filter. But what about such scenario:
1) we use index with first predicate and get set of docs references (or _id, don't know how its work in fact), let call it A;
2) then we use index for second predicate, get set B;
3) simple intersect them - A&B, add count or FETCH result, whatever we need.

So instead of intersect index ranges, we intersect its subsets results for each predicate. In my case it is $lt and $gt, but generally it could be any filter on array field.
It's simple idea, but I can't see why it wont work.

Comment by Eric Sedor [ 11/Jun/19 ]

Hi devbocharov; this behavior is expected for multikey indexes and is documented here.

You can read more details about this in SERVER-35223 and SERVER-6720, but in short: Counting on a single criteria without $elemMatch is an exception that can be handled with a COUNT_SCAN, but other combinations cannot.

Does this make sense?

Generated at Thu Feb 08 04:58:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.