[SERVER-82814] Support internal expr comparison operators when determining clustered collection scan bounds Created: 06/Nov/23  Updated: 14/Dec/23  Resolved: 11/Dec/23

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

Type: Improvement Priority: Major - P3
Reporter: Ivan Fefer Assignee: James Harrison
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Assigned Teams:
Query Optimization
Backwards Compatibility: Fully Compatible
Sprint: QO 2023-12-11
Participants:

 Description   

Currently, query on clustered collection will not use the index

db.coll.find({$expr: {$gt: ["$_id", <..>]}})

 

However, same syntax with a secondary index will work.

Because of slight semantic difference between match $gt and aggregation $gt
(match does type-bracketing, aggregation uses the specified BSON comparison order for values of different types), we have two sets of comparison match expressions:
 regular ones like $gt and $_internalExprGt which use slightly different logic.

When computing bounds for secondary index scan, $_internalExpr* operators are supported, however, they are not supported in clustered collection scan bounds:
https://github.com/mongodb/mongo/blob/bc654c2bf5fb81d7767c1c25aa09628fc5de90a8/src/mongo/db/query/planner_access.cpp#L449

There might be a reason for it, because of semantic difference, so we need to either fix it by adding internal expr comparison operations to the code, linked about, or add a comment about why we can't do it.



 Comments   
Comment by Githook User [ 14/Dec/23 ]

Author:

{'name': 'James Harrison', 'email': '00jamesh@gmail.com', 'username': 'jameseh96'}

Message: SERVER-84173: Avoid running multiversion tests for CLUSTERED_IXSCAN

Behaviour changed in SERVER-82814, and will be inconsistent in
multiversion tests unless backported.

GitOrigin-RevId: 26e5fb7e890b6477fa823934e799841da0eecc40
Branch: master
https://github.com/mongodb/mongo/commit/924478cbd15d57bb56a8eced22319c18ecd00cae

Comment by Githook User [ 08/Dec/23 ]

Author:

{'name': 'James Harrison', 'email': '00jamesh@gmail.com', 'username': 'jameseh96'}

Message: SERVER-82814: Refactor clustered coll scan tests to avoid repetition

GitOrigin-RevId: 4a27d87f0d424af3a246befbac191cea9922085a
Branch: master
https://github.com/mongodb/mongo/commit/3fb023056091806d3e5ea481759f153b228d9d67

Comment by Githook User [ 08/Dec/23 ]

Author:

{'name': 'James Harrison', 'email': '00jamesh@gmail.com', 'username': 'jameseh96'}

Message: SERVER-82814: Support $_internalExpr in clustered coll idx scan

INTERNAL_EXPR_* variants of

{EQ, LT, LTE, GT, GTE}

are less "precise"
than the non-internal equivalents; that is, they allow additional values
to be returned, and require a second stage of filtering.

When scanning records in a clustered index scan, these variants may
still be re-represented as min/max RecordIDs.

GitOrigin-RevId: 0884ff92a78d2b8dcaff307cf8fdd9da6af082da
Branch: master
https://github.com/mongodb/mongo/commit/5e7c8e0058b2935e905dff432959a34104596f58

Comment by John Page [ 01/Dec/23 ]

We are starting to encourage users to use clustered collections where appropriate and seeing significant performance benefits from them - we absolutely need this to work OR to make $lookup not require us to use $expr syntax (I'd prefer the latter)

Comment by Chris Hutchinson [ 17/Nov/23 ]

ivan.fefer@mongodb.com - please could you clarify the motivation and summarize the linked tickets?

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