[SERVER-26580] allow using partial index on query where predicate matches only partial filter expression Created: 11/Oct/16 Updated: 30/May/23 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Asya Kamsky | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 5 |
| Labels: | QFB, asya | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||
| Sprint: | Query 2017-05-08, Query 2017-05-29 | ||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||
| Case: | (copied to CRM) | ||||||||||||||||||||||||
| Description |
|
If I have partial index where partial filter expression is {x:1} it can be used for queries where the only condition is {x:1} (we would scan the entire index but that might be a lot faster than collection scan. It's currently possible to hint such an index, but it doesn't look like we ever choose or consider it. Possibly related |
| Comments |
| Comment by Eric Maciel [ 30/Mar/23 ] | |||||||
|
Is this resolved in any of the later versions of mongo? In particular the issue outlined here severely limits the utility of partialFilterExpression indexes or it requires us to have all of the fields in our partialFilterExpression also included in our index. | |||||||
| Comment by Asya Kamsky [ 26/Jun/20 ] | |||||||
|
The last example has a workaround - include the field from the partial filter expression in the index as another key. This will allow fully covered plan. | |||||||
| Comment by Thilo Schmalfuß [ 16/Jun/20 ] | |||||||
|
I recently stumbled in this problem, too: https://jira.mongodb.org/browse/SERVER-48777 For us this is a critical issue, since it limit the usefulness of partial indices a lot. If this problem is not considered for development soon, than it should at least be reflected in the documentation: * https://docs.mongodb.com/manual/core/query-optimization/#covered-query | |||||||
| Comment by Brad Vogel [ 20/Sep/17 ] | |||||||
|
Here's one more where the index is not used:
From | |||||||
| Comment by Kyle Suarez [ 27/Apr/17 ] | |||||||
|
There are multiple requests in this ticket related to partial indexes and query planning. For my own sanity, I'm going to list all the scenarios below. When the collection has a partial index {a: 1} with partialFilterExpression: {b: 5}:
When the collection also has a compound index {a: 1, b: 1}:
| |||||||
| Comment by Asya Kamsky [ 27/Jan/17 ] | |||||||
|
This is important for views where you want to have a single large collection appear as several smaller collections so every query under the hood will start with something like tenant_id='X' and each view is for a specific tenant_id only (which would be easier if we fix | |||||||
| Comment by Asya Kamsky [ 05/Jan/17 ] | |||||||
|
When I just specify {b:5}as condition without sort, neither find nor aggregate considers the partial index at all (which is the original bug, it's not different between agg and find). But another discrepancy is which index we use whether it's an aggregation or find command (i.e. views vs collection), same two indexes as above, a:1 is partial on b:5 condition and also a regular index a:1, b:1.
These two commands (find and aggregate) are identical in both meaning and results. But aggregate gets the "wrong" index instead of perfectly valid partial index. Is that an instance of agg preferring non-blocking stage (they are both non-blocking though!) so the issue is we don't properly realize partial index is non-blocking? Note that when I change a:$exists to be $gt:0 it doesn't change index selection, but find uses partial index but not as covered index which may be a separate bug:
Distinct shows strange behavior of using a:1,b:1 covered even though it's not most efficient, but even with condition on a added, it does not use covered on partial index.
| |||||||
| Comment by Asya Kamsky [ 05/Jan/17 ] | |||||||
|
Here's a case where we don't consider a partial index for distinct even though partial filter matches the query exactly.
We use a collection scan even though we could use the index. We can use DISTINCT_SCAN same as we would on index b:1,a:1 if it existed. |