[SERVER-71872] [CQF] Add heuristic: don't generate multi-index plans with a full index scan Created: 05/Dec/22  Updated: 29/Oct/23  Resolved: 07/Jun/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.1.0-rc0

Type: Improvement Priority: Major - P3
Reporter: David Percy Assignee: Chi-I Huang
Resolution: Fixed Votes: 0
Labels: bonsai-onboarding, search-space
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Sprint: QO 2023-05-29, QO 2023-06-12
Participants:

 Description   

Sometimes we generate plans that involve more than one index scan, and one or more of those scans has no bounds--it scans the whole index. Are these plans useful?

If index scans and collection scans have similar cost, then a single collection scan + filter should be cheaper than multiple full index scans. So we may want a heuristic to prevent union or intersection involving a full index scan.

On the other hand, a collection scan may be much more expensive than an index scan, and the more complex indexed plan can help fetch fewer documents. So if we add this heuristic, we should have an internal-only query knob to decide whether to apply it.



 Comments   
Comment by Githook User [ 07/Jun/23 ]

Author:

{'name': 'Chi-I Huang', 'email': 'chii.huang@mongodb.com', 'username': 'chiihuang'}

Message: SERVER-71872 Add heuristic to discard unbound candidate index in a multi-index plan
Branch: master
https://github.com/mongodb/mongo/commit/f62ba793d417efde6a7cfa4b9ce646e46196b425

Generated at Thu Feb 08 06:20:12 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.