I have a problem with very slow query execution on the mongodb. Generally I have persons collection and each person (about 40000000 records) has some documents (a few). I would like to get documents for concrete person, sort them and finally get only one page of items.
The query look like:
db.getCollection('persons').aggregate([
{ "$match" : { "personIdCardNumber" : "XXXXX" } },
{ "$unwind" : "$documents" },
{ "$match" : { } },
{ "$sort" : { "documents.submitDate" : -1 } },
{ "$skip" : 0 },
{ "$limit" : 11 },
{ "$group" : { "_id" : "$_id", "documents" : { "$push" : "$documents" } } }
],(
{ collation: { "locale" : "pl"}}))
When I will remove unwind, sort, group etc. the query also is very slow. When I remove collation the query becomes very fast (from 12s to ~50ms). When I set collation for "en" the query is a bit of faster but still is realy slow (~12s for "pl" vs ~8s for "en")
If someone has idea what can I do to have collation and provide better performance?
As for my c# code it is:
collection.AggregateWithCollation()
.Match(x => x.personIdCardNumber == idCardNumber)
.Unwind(x => x.Documents)
.Sort(sort)
.Skip((page - 1) * pageSize)
.Limit(pageSize + 1)
.Group(group)
.FirstOrDefaultAsync(cancellationToken);
no difference if i run the c# code or run the query directly on the database, the execution time is very similar.