-
Type: Improvement
-
Resolution: Fixed
-
Priority: Major - P3
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
-
Fully Compatible
-
(copied to CRM)
-
200
In SERVER-82549 we have a query where we consider 14 plans with the same structure (index scan + fetch + filter). The index scans are technically different, but are guaranteed to have the same performance, and return the same RIDs:
Plan 1
filter | seek | index scan X: [100, 100], Y: [Minkey, Maxkey]
Plan 2
filter | seek | index scan X: [100, 100], Z: [Minkey, Maxkey]
Plan 3
filter | seek | index scan X: [100, 100], Y: [Minkey, Maxkey], Z: [Minkey, Maxkey]
The non-open bounds in these index scans are identical. They will produce the same RIDs. For queries without a sort we should consider these duplicates and only trial one of them, preferably the one with the least number of indexed fields.
We've had a number of issues recently where multiplanning is slow. Removing identical plans is one way to help fix this, and generally improve multiplanner performance.
- depends on
-
SERVER-86251 Cleanup plan enumerator in preparation for plan deduplication
- Closed
- is depended on by
-
SERVER-86639 Improve index deduplication logic
- Open
- is related to
-
SERVER-62150 SBE Multiplanning can be slow when suboptimal plan runs first
- Closed
-
SERVER-86510 Always choose covering plan if it satisfies superset of predicates compared to other plans
- Open
-
SERVER-92232 Create property-based test to assert that pruned plans are not better than remaining plans
- In Progress
-
SERVER-94738 Disable index pruning during query planning
- Closed
- related to
-
SERVER-82549 MongoDB 7.0.2 SBE query slow when direct index exists
- Closed