[SERVER-12281] When choosing multikey index bounds, never choose a superset if a subset is available Created: 07/Jan/14 Updated: 06/Dec/22 |
|
| Status: | Backlog |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | 2.5.4 |
| Fix Version/s: | None |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | David Storch | Assignee: | Backlog - Query Optimization |
| Resolution: | Unresolved | Votes: | 11 |
| Labels: | asya | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||||||||||||||||||||||||||
| Assigned Teams: |
Query Optimization
|
||||||||||||||||||||||||||||||||||||||||
| Participants: | |||||||||||||||||||||||||||||||||||||||||
| Description |
|
For queries with multiple predicates over a multikey field, you can't intersect the index bounds. Suppose there is a multikey index on field 'a'. The following query
would have empty index bounds if 'a' were not a multikey field. Since it is multikey, however, we either use bounds
or
Which bounds to use is chosen arbitrarily. As an optimization, we could make sure that we always choose the smallest interval over which to perform the index scan. For instance, for the query
we would choose
rather than
because the former is a subset of the latter. |
| Comments |
| Comment by David Storch [ 27/Oct/15 ] |
|
As of the fix for As an optimization, we could still consider suppressing generation of plans whose bounds are a superset of another plan's bounds. However, |
| Comment by David Storch [ 11/May/15 ] |
|
Hi tredman@fb.com, An update on your question:
We have a fix for this query under related ticket Best, |
| Comment by Asya Kamsky [ 09/May/15 ] |
|
tredman@fb.com we can request a backport only once there is a fix committed to master. |
| Comment by Travis Redman [ 08/May/15 ] |
|
This bit us too. Query is in the form { foo: { $in: [ a ], $nin: [ b, c, ..., z ] }}. Basically results in a full index scan. Any chance of this getting back-ported to 2.6? |
| Comment by Sebastian Friedel [ 26/Jan/15 ] |
|
I was bitten by this as well, query went from 7ms to 71661ms after upgrade to MongoDB 2.6.7 (from 2.4.12). Query and details in case anyone is interested: http://pastebin.com/Sca2c3qS |
| Comment by Thomas Boutell [ 14/Nov/14 ] |
|
+1; I was surprised by the harsh slowdown imposed by the presence of $nin as well as $in. |