Is it possible a solution for a very slow query with aggregation?

Sergi_Ramos_Aguilo · 2022-05-19T18:13:03.949Z

Hi , I’m working on a query that works fine and does what I want but it’s too slow (1.5s-2s) and I don’t know why. Could you help me understand the reason for this slowness and a possible solution.

I have a collection where each document is like this:

{
     _id: 
     date: Date
     technicalOne:   ObjectId
     client:   ObjectId
     center:   String
     appointments: [
          isBlockTime:   boolean
          isRecurrentBreak:   boolean
          isOcasionalMeet:   boolean
          isRemoteWork:   boolean
          isOcasionalRemote:   boolean
          isOcasionalClientRemote:   boolean
          busyDays:   Array of numbers
          busyDaysWithout:   Array of numbers
          state:    number
          _id:
          isTechnicalOne:   boolean
          technical:    String
          technicalId:   ObjectId
          date:    Date
     ]
}

And de pipleline is this:

const pipeline = [
        {
            '$unwind': {
                'path': '$appointments',
                'preserveNullAndEmptyArrays': false
            }
        },
        {
            '$match': {
                'appointments.technical': technicalSelected,
                'center': workCenter,
                'appointments.state': 0,
                'appointments.date': {
                    '$gte': moment(startDate).startOf('month').toDate(),
                    '$lte': moment(endDate).endOf('month').toDate()
                }
            }
        },
        {
            '$sort': {
                'appointments.date': 1,
                'appointments.busyDays': 1
            }
        },
        {
            '$lookup': {
                'from': 'clienthistories',
                'localField': 'client',
                'foreignField': '_id',
                'as': 'client'
            }
        },
        {
            '$unwind': {
                'path': '$client',
                'preserveNullAndEmptyArrays': true
            }
        },
        {
            '$lookup': {
                'from': 'users',
                'localField': 'appointments.technicalSave',
                'foreignField': '_id',
                'as': 'user'
            }
        }, {
            '$unwind': {
                'path': '$user',
                'preserveNullAndEmptyArrays': true
            }
        },
        {
            '$project': {
                'center': '$center',
                'technical': '$technical.technical',
                'savedFor': '$user.technical',
                'date': '$appointments.date',
                'dateTakeAppointment': '$dateTakeAppointment',
                'busyDays': '$appointments.busyDays',
                'client': '$client',
                'appointment': '$appointments',
                'busyDays': '$appointments.busyDays',
                'busyDaysWithout': '$appointments.busyDaysWithout',
                'appointmentObservation': '$appointmentObservation',
            }
        },
        {
            '$sort': {
                'busyDays': 1
            }
        }
    ]


    const AppointmentsCollection = Appointments.collection

    const appointments = await AppointmentsCollection.aggregate(pipeline).toArray()

The maxium results are 120-180 documents.
What is wrong with this query?

Thank you very much

steevej · 2022-05-20T12:20:21.493Z

Make sure you have an index on the fields you use in $match
You should $match before $unwind:$appointments
Since you terminate with $sort:{busyDays:1}, I am pretty sure the first $sort is useless.
You should do $unwind:$clients after $lookup:{from:users}. Doing it before increases the work that has to be done in $lookup because you have more documents.
In you $project, you already do appointment:$appointements so all other fields from appointments like busyDays are useless and just send more duplicated data over the network.

Sergi_Ramos_Aguilo · 2022-05-26T12:05:23.816Z

Thank you very much! the query now is faster than before.