Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
Query Optimization
Description
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.
Attachments
Issue Links
- depends on
-
SERVER-86251 Cleanup plan enumerator in preparation for plan deduplication
-
- In Code Review
-
- is related to
-
SERVER-62150 SBE Multiplanning can be slow when suboptimal plan runs first
-
- Open
-
- related to
-
SERVER-82549 MongoDB 7.0.2 SBE query slow when direct index exists
-
- Closed
-