MongoDB - very slow query execution when collation is set

Viewed 654

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.

0 Answers
Related