Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-82548

MongoDB 7.0.2 SBE selects a different index when doing $in with a large array

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Execution
    • ALL
    • Hide

      You can see this by running explain with the following query on our production cluster:

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

      The full response is below, note that it doesn't complete even after running for minutes.

       

       

      Show
      You can see this by running explain with the following query on our production cluster: find: "reservations" filter: deleted_at: null _id: $in: Array[1000] sort: st: 1 shop_id: 1 The full response is below, note that it doesn't complete even after running for minutes.    
    • QO 2023-11-13, QE 2023-11-27, QE 2023-12-11

      (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}

       

        1. atlas-lpiap3-shard-00-02.wg2kc.mongodb.net_2023-11-03T20_00_00_2023-11-04T04_00_00_mongodb.log.gz
          95.71 MB
          Peter Oh
        2. image-2023-10-29-19-39-59-851.png
          152 kB
          Johnny Shields
        3. Screenshot 2023-11-06 at 9.50.07 am.png
          300 kB
          Peter Oh
        4. SERVER-82548_100_member_fast_case.txt
          79 kB
          Johnny Shields
        5. SERVER-82548_101_member_slow_case.txt
          72 kB
          Johnny Shields

            Assignee:
            ivan.fefer@mongodb.com Ivan Fefer
            Reporter:
            shields@tablecheck.com Johnny Shields
            Votes:
            2 Vote for this issue
            Watchers:
            13 Start watching this issue

              Created:
              Updated:
              Resolved: