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

Missing index scan bound in time-series query rewrite

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 5.2.0, 5.0.16
    • Affects Version/s: None
    • Component/s: None
    • None
    • Fully Compatible
    • ALL
    • v5.0
    • 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

      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.

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

              Created:
              Updated:
              Resolved: