[SERVER-53296] Return only latest document with multi-key index Created: 09/Dec/20  Updated: 10/Dec/20  Resolved: 10/Dec/20

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

Type: Question Priority: Major - P3
Reporter: Thomas Kiendl Assignee: Unassigned
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

I have a multi-key index (key1: 1, key2: 1, timestamp: -1) in an event-based database (i.e. thousands of documents per key1 and key2, ordered by timestamp).

What I want: Perform a query to return only the latest events, grouped by key1 and key2 - without scanning all past events!

 

However, when performing a query like the following, MongoDB actually scans all past events:

 

collection.aggregate([
      {
        "$match": {
          "key1": "someKey1",
        }
      },
      {
        "$sort": {
          "timestamp": -1
        }
      },
      {
        "$group": {
          "_id": {
            "key1": "$key1",
            "key2": "$key2",
          },
          'timestamp': { $first: '$timestamp' },
        }
      }
    ]

When I run 'explain' on the query, it says

"indexBounds" : {
    "key1" : [ 
        "[\"someKey1\"]"
    ],
    "key2" : [ 
        "[MinKey, MaxKey]"
    ],
    "timestamp" : [ 
        "[MaxKey, MinKey]" // <-- All past events are scanned.
    ]
},

Is it possible to avoid this behavior, and make MongoDB only scan the latest object?

 



 Comments   
Comment by Eric Sedor [ 10/Dec/20 ]

Hi thomas.kiendl-ext@rio.cloud

For this question, I encourage you to ask our community by posting on the MongoDB Developer Community Forums. The SERVER project is for bugs and feature suggestions for the MongoDB server.

Gratefully,
Eric

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