[SERVER-66267] Exploit RID = _id for secondary index on clustered collections? Created: 05/May/22  Updated: 06/Dec/22

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

Type: Improvement Priority: Major - P3
Reporter: David Percy Assignee: Backlog - Query Optimization
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query Optimization
Participants:

 Description   

If I understand correctly, on a clustered collection the _id field is the same as the record ID (RID). Does this mean that any secondary index also includes the _id value? For example, an index on {a: 1} is really a set of (a, _id) entries, almost like an index on {a: 1, _id: 1}.

Could we use an {a: 1} index to satisfy queries like {$sort: {a: 1, _id: 1}}?



 Comments   
Comment by Louis Williams [ 10/May/22 ]

david.percy@mongodb.com, if just for sorting, that that's okay. We require the _id to be an ObjectId for time-series collections using a document validator, so yes, I think that would work.

Comment by David Percy [ 06/May/22 ]

Interesting, thanks for the explanation!

If we're only interested in sorting, not covered projections, I think it's ok that the type bits aren't included. Things like NumberLong(5) vs 5.0, which have the same value but different type bits, are supposed to compare equal when we sort them. So if an index on {a: 1} gives us documents ordered by (keystring(a), keystring(_id)), I think that's a correct order to satisfy {$sort: {a: 1, _id: 1}}.

If we did want to do covered projections, just for timeseries collections, maybe we could assume that the _id field is always an ObjectId?

Comment by Louis Williams [ 06/May/22 ]

david.percy@mongodb.com, the issue is that we discard the TypeBits when KeyString-ifying the _id value to generate a RecordId. See here. We don't take the TypeBits from the KeyString::Builder and we don't store them anywhere. The reason this is safe is that the original type information is stored in the BSON object, which we would have already paged into cache by seeking on the clustered collection.

The consequence is that a covered index scan would not be able to know the original type of the _id. For example: If the _id was NumberLong(42), the RecordId (as a KeyString) would encode that as a small integer. When we query, we would need the TypeBits to tell us that the original value was NumberLong, not an int.

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