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

Return only latest document with multi-key index

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • None
    • None
    • None

    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?

       

      Attachments

        Activity

          People

            Unassigned Unassigned
            thomas.kiendl-ext@rio.cloud Thomas Kiendl
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: