How to use index in $group with $sum then $sort

MongoDB Atlas
,
13

You are sorting on the computed value count. No index can be used when sorting on a computed value. Indexes are used on stored field. A $sort on computed values will always be a memory sort, so it uses a lot of memory.

A $group stage is blocking. All incoming documents must be processed before the first resulting document is passed to the next stage, so it uses a lot of memory.

One trick I use to reduce the memory used by $group is to $group without an accumulator, then use $lookup to implement the accumulator logic. In your case, it may reduce the amount of memory needed by group by 50%. What I mean is something like:

{ "$group" : {
    "_id" : "$partNumber"
} }
lookup = { "$lookup" : {
    "from" : "product_part_number" ,
    "as" : "count" ,
    "localField" : "_id" ,
    "foreignField" : "partNumber" ,
    "pipeline" : [
        "$count" :  "_result"
    ]
} }
project = { "$project" : {
    "count" : { "$arrayElemAt" : [ "$count._result" , 0 ] }
} }

So the above 3 way more complicated states may reduce the memory footprint compared to the single $group. But not for the in memory sort on a computed field.