Aggregate $lookup and $group takes so long time

[slava]

If you just need to get usersIds from example collection and then just count how many users with that ids are online, bbetter to collect those ids into one array and then $lookup with that array of ids.

db.getCollection('example').aggregate([
  {
    $group: {
      _id: null,
      usersIds: {
        $addToSet: '$userId',
      },
    },
  },
  {
    $lookup: {
      from: 'users',
      let: {
        usersIds: '$usersIds',
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $in: ['$_id', '$$usersIds'] },
                { $eq: ['$isLogin', true] },
              ],
            },
          },
        },
      ],
      as: 'users',
    },
  },
  {
    $project: {
      _id: null,
      totalUsers: {
        $size: '$users',
      },
    },
  },
]);

[Valentine_Soin]

Thanks for your reply, slava.

Your replay is not what I wanna know. I should use $group after $lookup.
FYI, I want to know how many records are that online users, not users count.

I am thinking why $group after $lookup is taking so long time. This is so simple query.

So do you think this is mongodb critical bug?
No solution to resolve this issue?

[slava]

Can you share your aggregation here?
It seems $lookup and $group stages are used inefficiently.

[Valentine_Soin]

a. Users Collection which has 1 reocrd

{
	"_id" : ObjectId("5a8531bae2557229f8eaf603"),
	"nickname" : "kaka",
	"isLogin" : true,
}

b. Example Collection which has 50,000 records

{
	 "_id" : ObjectId("5ee86942aeadbc47b8d7ad4f"),
	 "id" : ObjectId("5a8531bae2557229f8eaf603"),
	 "commet" : "blabla",
 }

c. This is the aggregate which takes over 4 secs.

db.getCollection('example').aggregate([
                {
                    $lookup: {
                        from: "users",
                        localField: "id",
                        foreignField: "_id",
                        as: "user"
                    }
                },
                {
                    $unwind: "$user"
                },                
                {       
                    $match: {"user.isLogin" : true}
                },
                { $count: "total_count" }                
            ])

d.This is the aggregate explain.

 {
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {},
                "fields" : {
                    "id" : 1,
                    "_id" : 0
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,                   
                    "indexFilterSet" : false,
                    "parsedQuery" : {},
                    "winningPlan" : {
                        "stage" : "EOF"
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$lookup" : {
                "from" : "users",
                "as" : "user",
                "localField" : "id",
                "foreignField" : "_id",
                "unwinding" : {
                    "preserveNullAndEmptyArrays" : false
                },
                "matching" : {
                    "isLogin" : {
                        "$eq" : true
                    }
                }
            }
        }, 
        {
            "$group" : {
                "_id" : {
                    "$const" : null
                },
                "total_count" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            }
        }, 
        {
            "$project" : {
                "_id" : false,
                "total_count" : true
            }
        }
    ],
    "ok" : 1.0
}

The issue is the $group after lookup is taking so long time terribly.

[slava]

OK, so your aggregation has following problems:

• $lookup per each record: 50.000 records = 50.000 lookups
• $lookup joins non-relevant users, and you need only relevant ones
• $group is used to cal number of documents, while there are other tools for that: $size, $count.

You achieve your result much efficiently if you do $match before $lookup and reduce number of $lookups;
Like this:

db.getCollection('users').aggregate([
  // match only relevant users
  {
    $match: {
      isLogin: true,
    },
  },
  // collect usersIds into array
  {
    $group: {
      _id: null,
      userIds: {
        $addToSet: '$_id',
      },
    },
  },
  // join example data using array of userIds
  {
    $lookup: {
      from: 'logs',
      localField: 'userIds',
      foreignField: 'userId',
      as: 'logs',
    },
  },
  // count total logs, that were made by user.isLogin only
  {
    $project: {
      _id: null,
      totalLogs: {
        $size: '$logs',
      },
    },
  },

]);

[Valentine_Soin]

Yes, I think your aggregate is more optimized, I agree on it.

But I want to say the aggregate is taking 0.028 s when without using $count.
It means the lookup is not taking much time,

The main issue is concerned with $count($group) after $lookup.

Why $count($group) after $lookup is taking terrible time?

[Valentine_Soin]

Hi, slava. What is your think?

[slava]

@Valentine_Soin

Why $count($group) after $lookup is taking terrible time?

For this, I do not know, mate

[Valentine_Soin]

Alright. Thanks for your effort.
But I want to get an answer. How can I get it?

[slava]

I think you can get answer to your question in MongoDB Help Center

[Valentine_Soin]

Thanks. I can’t get more answers this forum, mate?

[kevinadi (Kevin Adistambha)]

Hi,

This aggregate takes more than 4 seconds now.
When I remove the $count, it takes 0.173 secs.

I believe it’s because without $count, the aggregation was not actually executed by the server. That is, the server returns a cursor for the result set, but not the result themselves. That’s usually why it can return a sub-second performance.

Also, in the mongo shell, if the aggregation returns a number of documents, it will fetch only the first 20, so it will still be quicker than fetching the whole result set if there’s a lot of documents there.

In contrast, $count would actually execute the query, fetch the documents, and load them from disk if necessary. This is why it would take a much longer time. In this case, it’s more than a magnitude slower.

You should have a similar timing (i.e. ~4 seconds or so) if you force the aggregation to execute, for example with db.collection.aggregate(...).toArray(). Bear in mind that depending on how warm the cache is, you may see a lower/higher number.

Best regards,
Kevin

[Prasad_Saya]

The $count stage is actually an equivalent to the following $group + $project sequence:

db.collection.aggregate( [
   { $group: { _id: null, total_count: { $sum: 1 } } },
   { $project: { _id: 0 } }
] )

So, the $count stage scans all the documents to arrive at the total_count value. Scanning the documents within a stage takes time. And, that is what is happening.

Instead of using the $count you can use the itcount cursor method. This returns the count of documents in the cursor. Note that an aggregation query returns a cursor with documents. Using itcount() is likely to be faster than scanning the documents in the $count stage.

db.getCollection('example').aggregate([
   // pipeline stages here (excluding the $count) ...
]).itcount()

[Valentine_Soin]

Hi, Kevin.
Thanks for your kind reply.

I’ve checked all carefully. Your saying does make sense for me and is right by the test.

But I am wondering on these

• The lookup & group is widely used in the aggregate but when and how can it be used if there is a speed problem?
• Is there no way to reduce the time of lookup & count(group) query?

Thanks.

[Valentine_Soin]

Hi, Parsad.
Thanks for your clear explanation.

I tested it with itcount(), but it takes too long, just as the $count is in the aggregation stage.
it takes 17.6 secs to count 90051 documents

I am wondering how we can use it if it takes so much time to even get a count of matched documents.

What is the solution to speed up dramatically on $lookup & group in aggregate?
Thanks.

[Valentine_Soin]

Hello, friends.

What do you think about this

Thanks.

[Valentine_Soin]

No more solution for this aggregate, friends?

[kevinadi (Kevin Adistambha)]

Hi,

So far based on what we’ve seen, there are a couple of ways you can improve this:

1. Use a pre-aggregated report: each time you insert a document that satisfies the aggregation criteria, you also increment a count on a separate document. This way, every time you need the count, you only need to find() on that document and read the latest figure.
2. If using a pre-aggregated report is not appealing, provisioning a larger RAM would be the only way forward.

The main issue is that your aggregation-based count requires the server to execute the query, get the matching documents, then count them. There is no shortcut to do this, so the two methods described above are the only ways I can think of to speed it up.

If you need further help, please post the output of db.collection.explain('executionStats').aggregate(...) and the output of db.collection.stats(). Please also describe your deployment, e.g. how much RAM you have, what kind of disk you have, is it deployed bare metal or using Docker-like method, etc.

Best regards,
Kevin