[SERVER-58276] Add time-series bounded collection scan plans to query multi-planner Created: 06/Jul/21  Updated: 29/Oct/23  Resolved: 02/Nov/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 6.2.0-rc0, 6.0.7

Type: Improvement Priority: Trivial - P5
Reporter: Katherine Wu (Inactive) Assignee: Ivan Fefer
Resolution: Fixed Votes: 0
Labels: M2
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Documented
is documented by DOCS-15714 [Server]: Add time-series bounded col... Closed
Duplicate
Problem/Incident
causes SERVER-73423 CLUSTERED_IXSCAN with sort generates ... Closed
Related
related to SERVER-59341 Query planner choses collscan over co... Backlog
related to SERVER-54398 Extend query planner to generate boun... Closed
Backwards Compatibility: Fully Compatible
Backport Requested:
v6.0
Sprint: QO 2021-09-20, QO 2021-10-04, QE 2022-10-17, QE 2022-10-31, QE 2022-11-14
Participants:
Linked BF Score: 35

 Description   

After SERVER-54398, queries on time-series collections will only consider a bounded collection scan when no indexed solution is available. However there are cases where the bounded collection scan results in better performance than an indexed plan, for example when the indexed solution has wide index bounds and must fetch many documents. We should add the bounded collection scan as a potential plan and compare it with indexed solutions in the query multi-planning process.



 Comments   
Comment by Githook User [ 24/May/23 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-58276 Add collscan plan if collection is clustered and collscan uses clustered index

(cherry picked from commit 76e583ed12d536aba872920568b5324d1dcae713)
Branch: v6.0
https://github.com/mongodb/mongo/commit/5efae388204b02f89c0ad060cee63d548efbfcfe

Comment by Githook User [ 02/Nov/22 ]

Author:

{'name': 'Ivan Fefer', 'email': 'ivan.fefer@mongodb.com', 'username': 'Fefer-Ivan'}

Message: SERVER-58276 Add collscan plan if collection is clustered and collscan uses clustered index
Branch: master
https://github.com/mongodb/mongo/commit/76e583ed12d536aba872920568b5324d1dcae713

Comment by Rushan Chen [ 23/Aug/22 ]

Additional note from a cloesd PoC ticket which is a duplicate of this:

"Our current query planner does not consider _id index for multi-planning. In the presence of a secondary index on timeField, we always use the secondary index. Using the index on time field, we scan the index tree, and then do another lookup in the record store to fetch the bucket document. Where as if we use the _id index, then we can answer the query with just scans on the record store. So some query might perform better with _id over time index.

The downside of _id index is that we only index the control.min.time, where as an index on timeField with be compounded with control.min.time: 1, control.max.time. So the bounds will be narrower with timeField index. We need to evaluate this trade-off using multi-planning."

Early experiment showed this could speed up queries with filter on time by about 5%.

 

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