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

$in queries do not use clustered index

    XMLWordPrintableJSON

Details

    • Query Optimization
    • Fully Compatible
    • v6.3, v6.0, v5.0
    • QO 2023-03-20, QO 2023-04-03

    Description

      A query that uses $in on _id does not plan against a clustered index if one exists. If no suitable secondary index exists, we fall back to a collection scan.

      This affects queries directly on time-series buckets collections.

      test> db.createCollection('clustered', {clusteredIndex: {key: {_id: 1}, unique: true}})
      		 
      test> db.clustered.find({_id: {$in: [0,1,2,3]}}).explain()
      		 
      {
      		 
        explainVersion: '1',
      		 
        queryPlanner: {
      		 
          namespace: 'test.clustered',
      		 
          indexFilterSet: false,
      		 
          parsedQuery: { _id: { '$in': [ 0, 1, 2, 3 ] } },
      		 
          queryHash: '0D7870DC',
      		 
          planCacheKey: '0D7870DC',
      		 
          maxIndexedOrSolutionsReached: false,
      		 
          maxIndexedAndSolutionsReached: false,
      		 
          maxScansToExplodeReached: false,
      		 
          winningPlan: {
      		 
            stage: 'COLLSCAN',
      		 
            filter: { _id: { '$in': [ 0, 1, 2, 3 ] } },
      		 
            direction: 'forward'
      		 
          },
      		 
          rejectedPlans: []
      		 
        },

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. SERVER-76102 handleRIDRangeScan() sets hasCompatibleCollation wrong in recursive cases

          • Major - P3 - Major loss of function.
          • Closed
          is related to

          Improvement - An improvement or enhancement to an existing feature or task. SERVER-75063 Support more complex scan bounds for clustered index

          • Major - P3 - Major loss of function.
          • Backlog

          Activity

            People

              hana.pearlman@mongodb.com Hana Pearlman
              louis.williams@mongodb.com Louis Williams
              Votes:
              2 Vote for this issue
              Watchers:
              22 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: