[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:
Related
is related to SERVER-56618 Change SBE to count keys inspected wh... Backlog
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:

  • numReads: the number of keysExamined + the number of seeks
  • keysExamined: the name says it all

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: SERVER-55335 Change plan ranking formula for SBE
Branch: master
https://github.com/mongodb/mongo/commit/e27c9f5b7f2298a947996f95431686fc44dec3da

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:
keysExamined would equal the number of keys examined in bounds and out of bounds.
numReads would be equal keysExamined plus the number of seeks.

It seems to make sense to lump this in as part of this ticket, though we could split it off.

Generated at Thu Feb 08 05:36:10 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.