(This ticket is one of several issues we at TableCheck had when upgrading from MongoDB 6.0.11 to 7.0.2; refer to https://support.mongodb.com/case/01207868)

The following query seems to perform worse on MongoDB 7.0 vs. 6.0:

find: "reservations"
filter:
  deleted_at: null
  _id: 
     $in: Array[1000]
sort:
  st: 1
  shop_id: 1

We expect this to ignore sort and select the 1000 _id members based on the build-in _id index.

We observe similar effects on other indexed fields (aside from _id) where the sort condition seems to be taking priority over a direct index selection of the $in members.

Note that this sort of query is very important to avoid N+1 queries on the client side. For example, suppose we have 1000 payments, we will select the payments, map Payment.reservation_id, and then query for Reservation _id: $in => {reservation_ids}