[SERVER-71763] CSI query planner enhancements for filter count, etc. Created: 01/Dec/22  Updated: 29/Oct/23  Resolved: 13/Dec/22

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

Type: Improvement Priority: Major - P3
Reporter: Steve Tarzia Assignee: Alyssa Clark
Resolution: Fixed Votes: 0
Labels: pm2646-m4
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Problem/Incident
Backwards Compatibility: Fully Compatible
Participants:
Linked BF Score: 35

 Description   

Column scan generally beats collection scans even when the collection and documents are small if the number of filters that get pushed down is large. This should be considered in the CSI plan selection heuristics.

Also, we should look at combining the document size and collection size heuristics in a more intelligent way. My sense is that the heuristic should be something like this:

Use a column scan only if:

  • the collection is larger than RAM
  • or the collection is smaller than RAM and
    • the docs are large
    • or a large number of filters (at least 2 or 3?) can be pushed down

This should let us choose column scan for the charts workload running on a low-memory instance and it may activate it for some appropriate queries on the regular instances.



 Comments   
Comment by Githook User [ 13/Dec/22 ]

Author:

{'name': 'Alyssa Wagenmaker', 'email': 'alyssa.wagenmaker@mongodb.com', 'username': 'awagenmaker'}

Message: SERVER-71763 CSI planning heuristics enhancements
Branch: master
https://github.com/mongodb/mongo/commit/246c58be582a45bc6ac0648f5e7b38f43c1b6902

Comment by Steve Tarzia [ 05/Dec/22 ]

Note that we also have a heuristic for projection counts – I think it enables CSI only if the number of projected fields is less than 5 (with no filter pushdown) or less than 12 (if filters are being pushed down). This is a good time to think about how that heuristic should interact with the others.

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