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