The aggregate below runs in less than 1 second without the final $count stage. But with the $count it takes 562 seconds to run (8 vcpus and 62GB RAM). The count result is 212436.
Any directions for having a faster count?
{
"$match": {
"isDeleted": false,
"tenant_id": ObjectId( "5ec2a723a73af34fd5964c93" ),
"$or": [
{
"emails": {
"$exists": true,
"$not": {
"$size": 0
}
}
},
{
"cellphones": {
"$exists": true,
"$not": {
"$size": 0
}
}
}
]
}
},
{ "$lookup": {
"from": "events",
"let": { "cId": "$_id" },
"pipeline": [
{ "$match":
{ "$expr":
{ "$and":
[
{ "$eq": [ "$contact_id", "$cId"] }
]
}}},
{
"$group":{
"_id":"$contact_id",
"6059ff5a2aa6a105ae85d7f1": {
"$sum": {
"$cond": [
{ "$and":
[
{ "$eq": [ "$channel", "email" ] },
{ "$eq": [ "$event", "open"] },
{ "$eq": [ "$campaign_id", ObjectId( "60648747f78ba3fd5b00e8ba" ) ] }
]
}, 1, 0 ]
}
}
}
}
],
"as": "events"
}},
{
"$unwind": {
"path": "$events",
"preserveNullAndEmptyArrays": true
}},
{
"$addFields": {
"6059ff5a2aa6a105ae85d7f1": "$events.6059ff5a2aa6a105ae85d7f1"
}},
{
"$project": {
"events": 0
}},
{ "$lookup": {
"from": "events",
"let": { "cId": "$_id" },
"pipeline": [
{ "$match":
{ "$expr":
{ "$and":
[
{ "$eq": [ "$contact_id", "$cId"] }
]
}}},
{
"$group":{
"_id":"$contact_id",
"6075ecec3319af23fd597b0f": {
"$sum": {
"$cond": [
{ "$and":
[
{ "$eq": [ "$channel", "email" ] },
{ "$eq": [ "$event", "open"] },
{ "$eq": [ "$campaign_id", ObjectId( "601c1b8343e5614118d6afa5" ) ] }
]
}, 1, 0 ]
}
}
}
}
],
"as": "events"
}},
{
"$unwind": {
"path": "$events",
"preserveNullAndEmptyArrays": true
}},
{
"$addFields": {
"6075ecec3319af23fd597b0f": "$events.6075ecec3319af23fd597b0f"
}},
{
"$project": {
"events": 0
}},
{ "$lookup": {
"from": "tagcontacts",
"let": { "cId": "$_id" },
"pipeline": [
{ "$match":
{ "$expr":
{ "$and":
[
{ "$eq": [ "$contact_id", "$cId"] }
]
}}},
{
"$group":{
"_id":"$contact_id",
"6075ecec3319afd31e597b0c": {
"$sum": {
"$cond": [ { "$eq": [ "$tag", ObjectId( "60478086f4ac576583614c56" ) ] }, 1, 0 ]
}
}
}
}
],
"as": "tags"
}},
{
"$unwind": {
"path": "$tags",
"preserveNullAndEmptyArrays": true
}},
{
"$addFields": {
"6075ecec3319afd31e597b0c": "$tags.6075ecec3319afd31e597b0c"
}},
{
"$project": {
"tags": 0
}},
{ "$lookup": {
"from": "tagcontacts",
"let": { "cId": "$_id" },
"pipeline": [
{ "$match":
{ "$expr":
{ "$and":
[
{ "$eq": [ "$contact_id", "$cId"] }
]
}}},
{
"$group":{
"_id":"$contact_id",
"6075ecec3319af462d597b0b": {
"$sum": {
"$cond": [ { "$eq": [ "$tag", ObjectId( "606f1b593b1622a3ec817f80" ) ] }, 1, 0 ]
}
}
}
}
],
"as": "tags"
}},
{
"$unwind": {
"path": "$tags",
"preserveNullAndEmptyArrays": true
}},
{
"$addFields": {
"6075ecec3319af462d597b0b": "$tags.6075ecec3319af462d597b0b"
}},
{
"$project": {
"tags": 0
}},
{
"$match": {
"$and": [
{
"$and": [
{
"6075ecec3319af23fd597b0f": 0
},
{
"6059ff5a2aa6a105ae85d7f1": 0
}
]
},
{
"$and": [
{
"6075ecec3319afd31e597b0c": {
"$lt": 1
}
},
{
"6075ecec3319af462d597b0b": {
"$lt": 1
}
}
]
}
]
}
},
{
"$count": "Quantos"
}
],
{ "allowDiskUse": true })´´´