Watch keynotes and sessions from MongoDB.live, our virtual developer conference.

What is the right index for an Aggregation which matches with _id´s and sorts by date?

hey i have a slight problem with my aggregation that looks like this:

db.documents.aggregate([
    {
        $match: {
            "_id": { $in: [ObjectId("5ed78b0e7ae41374c00ca1b1")] },
            "deleted":false,
            "companyId":"5beb0743ad228201803435c0",
            "isExport":{"$ne":true},
            "folders":{"$in": ["5beb0743ad228201803435c0"] },
        }
    },
    {
        "$sort": {
            "createdAt": -1
        }
    },
    {
        "$limit": 20
    },
]).explain('allPlansExecution')

with the chosen index it uses that looks like this

{
  "createdAt" : 1,
  "deleted" : 1,
  "folders" : 1
}

but that will result in my DB in a 800+ms query execution time.
if i remove either the $sort or the _id in $match it uses either just the standard _id index or one of my indexes i defined:

{
  "companyId" : 1,
  "folders" : 1,
  "deleted" : 1,
  "createdAt" : -1
}

and both result in a 1-5ms query exeution time.
For the question why i query for { _id: { $in: [...] } }, its because i get a list of ids from another collection and then just wanna filter out any document that isn’t e.g in the current companyId and then sort it to send them in the correct order to the client.
So my question is how should my index look like to get the most performant response and why is it that index?

i tried already indexes that look like e.g.

{
  "createdAt" : 1,
  "_id" : 1,
  "companyId" : 1,
  "folders" : 1,
  "deleted" : 1,
  "isExport" : 1
}

and similar variations of that where the keys are in a different order or some keys are missing but they just get not used even if i use the hint option.

thanks already for the help :slight_smile:

Hello Steffen,

To start with please take a look at the following post, and it has somewhat similar issue about using a compound index with matching and sorting: Index scan not filtering as expected

EDIT Add: Also, please include the query plan results in the post.