[SERVER-83524] Enable indexed plans for expressions with $$NOW, $$CLUSTER_TIME, and $$USER_ROLES Created: 21/Nov/23  Updated: 19/Jan/24

Status: Investigating
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Steve Tarzia Assignee: Jacob Evans
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Fix
Problem/Incident
Assigned Teams:
Query Optimization
Participants:

 Description   

This code from SERVER-74264 disabled constant folding for expressions involving $$NOW, $$CLUSTER_TIME, or $$USER_ROLES.  This prevents some relatively simple queries from being satisfied by an index.  For example:

db.coll.find({"$expr": {"$lt": ["$_ts",

{"$subtract": ["$$NOW",604800]}

]}})

will lead to a collection scan even if there is an index on the _ts field.

We should find a way to build an IXSCAN plan for such queries while still avoiding the plan cache bug fixed by SERVER-74264.

Note that this was inspired by HELP-52526 for a query generated by online archive.


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