MongoDB aggregation error operation exceeded time limit

Question

I've been struggling to complete an aggregation process due to the timeout error and none of the solutions online have worked for me. I've reduced the workload as much as possible through match

Mongo version: 4.1.3 Using MongoDB Free tier

Attempt:

   return this.abyssBattleModel
      .aggregate([
        {
          $match: {
            floor_level: floorLevel,
            battle_index: battleIndex,
          },
        },
        {
          $lookup: {
            from: 'playercharacters',
            localField: 'party',
            foreignField: '_id',
            as: 'party',
          },
        },
        {
          $project: {
            _id: 0,
            party: {
              $map: {
                input: '$party',
                as: 'pc',
                in: '$$pc.character',
              },
            },
            floor_level: 1,
            battle_index: 1,
            star: 1,
          },
        },
        {
          $match: {
            party: { $all: characterIds },
          },
        },
        {
          $group: {
            _id: {
              party: '$party',
              floorLevel: '$floor_level',
              battleIndex: '$battle_index',
            },
            count: {
              $sum: 1,
            },
            avgStar: {
              $avg: '$star',
            },
            winCount: {
              $sum: {
                $cond: { if: { $eq: ['$star', 3] }, then: 1, else: 0 },
              },
            },
          },
        },
        {
          $sort: {
            count: -1,
          },
        },
        {
          $limit: limit,
        },
      ])
      .option({ maxTimeMS: 21600000, allowDiskUse: true, noCursorTimeout: true })
      .exec();

In this query the field party is an array of PlayerCharacter ObjectIds. A PlayerCharacter object has a field character which references a Character ObjectId. In this query I am using $lookup to change party to an array of PlayerCharacter ObjectIds to Character ObjectIds so that I can filter them by Character ObjectIds.

Models

// Filtered by floor_level and battle_index and party
// Party is an array of PlayerCharacter ObjectId
// which need to be joined with Character model to filter by Character id
export class AbyssBattle {
  @Field(() => String)
  _id: MongooseSchema.Types.ObjectId;

  @Field(() => String)
  @Prop({ required: true })
  floor_level: string;

  @Field(() => Number)
  @Prop({ required: true })
  battle_index: number;

  @Field(() => Number)
  @Prop({ required: true })
  star: number;

  @Field(() => [String])
  @Prop({
    type: [MongooseSchema.Types.ObjectId],
    ref: 'PlayerCharacter',
    required: true,
  })
  party: MongooseSchema.Types.ObjectId[];
}

export class PlayerCharacter {
  @Field(() => String)
  _id: MongooseSchema.Types.ObjectId;

  @Field(() => String)
  @Prop({
    type: MongooseSchema.Types.ObjectId,
    ref: 'Character',
    required: true,
  })
  character: MongooseSchema.Types.ObjectId;
}

Is there another way to increase the timeout or is this a limitation of MongoDB free tier that I'm unaware of? Is this purely an optimization issue? Thank you in advance.

Answer 1

Apart from proper index on { floor_level: 1, battle_index: 1 } you can try to optimize the $lookup:

{
   $lookup:
      {
         from: 'playercharacters',
         pipeline: [ 
            { $match: { _id: { $all: characterIds } } },
            { $match: { $expr: { $eq: [ "$party",  "$$_id" ] } }
         ],
         as: 'party'
      }
}

Or as concise correlated subquery (requires MongoDB 5.0)

{
  $lookup: {
     from: "playercharacters",
     localField: "party",
     foreignField: "_id",
     pipeline: [ { $match: { _id: { $all: characterIds } } } ],
     as: "party"
  }
}