[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: |
|
||||||||||||||||||||||||||||
| Issue Links: |
|
||||||||||||||||||||||||||||
| 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). 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. |
| 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: 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. |
| 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 Does this make sense? |