[SERVER-60830] Missing index scan bound in time-series query rewrite Created: 19/Oct/21  Updated: 29/Oct/23  Resolved: 05/Nov/21

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 5.2.0, 5.0.16

Type: Bug Priority: Major - P3
Reporter: Geert Bosch Assignee: Matt Boros
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Backports
Backwards Compatibility: Fully Compatible
Operating System: ALL
Backport Requested:
v5.0
Steps To Reproduce:

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]' ]
              }
            }

Sprint: QO 2021-11-01, QO 2021-11-15
Participants:

 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.



 Comments   
Comment by Githook User [ 15/Mar/23 ]

Author:

{'name': 'Matt Boros', 'email': 'matt.boros@mongodb.com', 'username': 'mattBoros'}

Message: SERVER-60830 Added more predicates for $eq $lt $lte in internal unpack bucket

(cherry picked from commit 406e8f1bebdc7e506b1dfcf013c528de53807e78)
Branch: v5.0
https://github.com/mongodb/mongo/commit/3f23a831ecd8d03cc6ed73202bbfe238b5a21857

Comment by Githook User [ 05/Nov/21 ]

Author:

{'name': 'Matt Boros', 'email': 'matt.boros@mongodb.com'}

Message: SERVER-60830 Added more predicates for $eq $lt $lte in internal unpack bucket
Branch: master
https://github.com/mongodb/mongo/commit/406e8f1bebdc7e506b1dfcf013c528de53807e78

Comment by Geert Bosch [ 19/Oct/21 ]

david.percy, this is the issue we talked about.

Generated at Thu Feb 08 05:50:51 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.