MongoDB very slow when using $group function

2019-08-23 20:59发布

MongoDB seems very slow when adding a $group pipeline to simply count the number of records for each occurrence. Below is an example of running it without $group, using $lookup perform a join. This runs in less than 1 second.

Note: I'm doing the lookup so I can eventually count the attendance code type for each student as well as the total

db.attendances.aggregate(
    [
        {
            $lookup: {
                from: 'attendance_codes',
                localField: 'attendance_code',
                foreignField: '_id',
                as: 'attendance_code'
            }
        },
        {
            $unwind: '$attendance_code'
        }
    ]
)

Adding in the $group step to simply group by the student and count the number of records causes this to exceed my maximum execution time of 30 seconds.

db.session_attendances.aggregate(
    [
        {
            $lookup: {
                from: 'attendance_codes',
                localField: 'attendance_code',
                foreignField: '_id',
                as: 'attendance_code'
            }
        },
        {
            $unwind: '$attendance_code'
        },
        {
            $group : {
                _id: {
                    student: "$student"
                },
                count:{ $sum:1 }
             }
        }
    ]
)

Below are examples of each table

Example Attendance record

Table has around 650,000 records

{
    "_id" : ObjectId("object id")
    "attendance_code" : ObjectId("foreign object id"), (is indexed)
    "date" : "2018-09-02 00:00:00.000000",
    "student" : ObjectId("foreign object id"), (is indexed)
    "created_at" : ISODate("2019-04-01T09:32:15.776Z"),
    "updated_at" : ISODate("2019-07-02T08:15:07.096Z")
}

Example Attendance Code record

Table has around 25 records

{
    "_id" : ObjectId("object id"),
    "code" : "/",
    "created_at" : ISODate("2019-04-01T09:32:13.602Z"),
    "type" : "PRESENT",
    "updated_at" : ISODate("2019-08-07T20:31:43.426Z")
}

0条回答
登录 后发表回答