[SERVER-55335] Productivity formula in SBE plan ranker should include the number of index seeks, along with index reads Created: 19/Mar/21 Updated: 29/Oct/23 Resolved: 05/May/21 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Query Planning |
| Affects Version/s: | None |
| Fix Version/s: | 5.0.0-rc0 |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | Anton Korshunov | Assignee: | Ian Boros |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||
| Sprint: | Query Execution 2021-05-03, Query Execution 2021-05-17 | ||||||||
| Participants: | |||||||||
| Description |
|
At present the productivity formula in the SBE plan scorer is based on the 'numReads' metric. In case of an index scan, the 'numReads' would only get incremented when an index key has been located and fetched. This aligns with how the 'keysExamined' metrics is computed in the classic engine. However, in SBE we can construct a complex sub-tree implementing a generic index scan for multi-interval bounds, which may perform a lot of index 'seeks' which would be counted in the 'numReads', if the returned key is not within the bounds of the current interval and we need to restart the search from a new seek key. This would mean that we may do a lot of work which wouldn't be reflected in the productivity formula, an we potentially may pick a bad plan. To address this issue we may want to consider collecting two metrics:
We would use 'numReads' for plan ranking and 'keysExamined' for reporting the summary stats. |
| Comments |
| Comment by Githook User [ 05/May/21 ] |
|
Author: {'name': 'Ian Boros', 'email': 'ian.boros@mongodb.com', 'username': 'puppyofkosh'}Message: |
| Comment by Ian Boros [ 06/Apr/21 ] |
|
After some discussion with david.storch we agreed that we should also ensure keysExamined includes the number of keys which are read, yet out of bounds. This is what we do in the classic engine (see here). In SBE, numReads currently does not account for keys which are examined but out of bounds. So to summarize: It seems to make sense to lump this in as part of this ticket, though we could split it off. |