[SERVER-77280] $or queries only produce collscan for clustered collections Created: 18/May/23  Updated: 29/Oct/23  Resolved: 08/Jun/23

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

Type: Bug Priority: Major - P3
Reporter: Gil Alon Assignee: Gil Alon
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-61259 $or queries with text indexes fail on... Closed
is depended on by SERVER-77867 Investigate when to avoid pushing dow... Backlog
Related
related to SERVER-78045 Nested $or queries with $text nodes s... Backlog
related to SERVER-77601 Support sort keys in clustered collec... Backlog
related to SERVER-78044 Cache rooted $or queries that use clu... Backlog
Backwards Compatibility: Fully Compatible
Sprint: QI 2023-05-29, QI 2023-06-12
Participants:

 Description   

The following code will execute a COLLSCAN.

db.createCollection("c", { clusteredIndex: { key: { _id: 1 }, unique: true } }));
db.c.createIndex({ a: 1 }); 
db.c.insert({ b: "foo", a: 1, c: 2 });
db.c.explain().find({ $or: [{ _id: 123 }, { a: 5 }] }).finish();  

However, a better plan would be a OR stage with a CLUSTERED_IXSCAN and a IXSCAN stage. Currently, CLUSTERED_IXSCAN plans are not considered when we subplan the children of $or queries. We only try IXSCAN for the children and miss out on better plans. This will also address the bug in SERVER-61259. If we can support CLUSTERED_IXSCAN in the children of $or queries then we can support $text queries under $or, since we will not need to fallback on a collection scan.



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

Author:

{'name': 'Gil Alon', 'email': 'gil.alon@mongodb.com', 'username': 'galon1'}

Message: SERVER-77280 Allow OR stage to use clustered indexes
Branch: master
https://github.com/mongodb/mongo/commit/563a37b0e63f6ee82ad23d55b92fc305857d1770

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