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

Missing index scan bound in time-series query rewrite

    XMLWordPrintable

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Fixed
    • None
    • 5.2.0
    • None
    • None
    • Fully Compatible
    • ALL
    • Hide

      db.raw.find({ device: "MongoThing_001", ts: { $gt: new Date(new Date() - 3000) } }).explain() 
      

      Yields:

               winningPlan: {
                  stage: 'FETCH',
                  filter: { _id: { '$gt': ObjectId("616ed578ffffffffffffffff") } },
                  inputStage: {
                    stage: 'IXSCAN',
                    keyPattern: { meta: 1, 'control.min.ts': 1, 'control.max.ts': 1 },
                    indexName: 'device_1_ts_1',
                    isMultiKey: false,
                    multiKeyPaths: { meta: [], 'control.min.ts': [], 'control.max.ts': [] },
                    isUnique: false,
                    isSparse: false,
                    isPartial: false,
                    indexVersion: 2,
                    direction: 'forward',
                    indexBounds: {
                      meta: [ '["MongoThing_001", "MongoThing_001"]' ],
                      'control.min.ts': [ '[MinKey, MaxKey]' ],
                      'control.max.ts': [ '(new Date(1634657160521), MaxKey]' ]
                    }
                  }
      

      Show
      db.raw.find({ device: "MongoThing_001", ts: { $gt: new Date(new Date() - 3000) } }).explain() Yields: winningPlan: { stage: 'FETCH', filter: { _id: { '$gt': ObjectId("616ed578ffffffffffffffff") } }, inputStage: { stage: 'IXSCAN', keyPattern: { meta: 1, 'control.min.ts': 1, 'control.max.ts': 1 }, indexName: 'device_1_ts_1', isMultiKey: false, multiKeyPaths: { meta: [], 'control.min.ts': [], 'control.max.ts': [] }, isUnique: false, isSparse: false, isPartial: false, indexVersion: 2, direction: 'forward', indexBounds: { meta: [ '["MongoThing_001", "MongoThing_001"]' ], 'control.min.ts': [ '[MinKey, MaxKey]' ], 'control.max.ts': [ '(new Date(1634657160521), MaxKey]' ] } }
    • QO 2021-11-01, QO 2021-11-15

    Description

      When you have an index on

      { meta:1, ts:1 }

      , and query on meta and ts greater than some value, you get a plan with only a bound on the control.max.ts field, but not on the control.min.ts field. This results in a complete index scan, which is expensive. Instead the query should use the bucketMaxSpanSeconds to bound the control.min.ts field.

      Attachments

        Activity

          People

            matt.boros@mongodb.com Matt Boros
            geert.bosch@mongodb.com Geert Bosch
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: