This is the problematic query:
```javascript
[
{
"$lookup": {
"from": "orders",
"let":
{
"let_product_id___1": "$product_id"
}
,
"pipeline": [
{
"$group": {
"_id":
{
"product_id": "$product_id"
}
,
"count":
{
"$sum": 1
}
}
},
{
"$sort":
{
"_id": 1
}
},
{
"$project":
{
"_id": false,
"product_id": "$_id.product_id",
"count": true
}
},
{
"$project":
{
"_id": false,
"product_id": "$product_id",
"count": "$count"
}
},
{
"$match": {
"$expr":
{
"$eq": [
"$$let_product_id___1",
"$product_id"
]
}
}
}
],
"as": "join_alias_mongo8 orders grouped by product category and id - Product"
}
},
{
"$unwind":
{
"path": "$join_alias_mongo8 orders grouped by product category and id - Product",
"preserveNullAndEmptyArrays": true
}
},
{
"$group": {
"_id": null,
"count":
{
"$sum": 1
}
}
},
{
"$sort":
{
"_id": 1
}
},
{
"$project":
{
"_id": false,
"count": true
}
}
]
```
I noticed that the performance is restored if a limit is added above the `$match`, which i believe prevents the optimizer from moving the `$match`.
```javascript
[
{
"$lookup": {
"from": "orders",
"let":
{
"let_product_id___1": "$product_id"
}
,
"pipeline": [
{
"$group": {
"_id":
{
"product_id": "$product_id"
}
,
"count":
{
"$sum": 1
}
}
},
{
"$sort":
{
"_id": 1
}
},
{
"$project":
{
"_id": false,
"product_id": "$_id.product_id",
"count": true
}
},
{
"$project":
{
"_id": false,
"product_id": "$product_id",
"count": "$count"
}
},
{
"$limit": 9223372036854775807
}
,
{
"$match": {
"$expr":
{
"$eq": [
"$$let_product_id___1",
"$product_id"
]
}
}
}
],
"as": "join_alias_mongo9, orders grouped by product id and category - Product"
}
},
{
"$unwind":
{
"path": "$join_alias_mongo9, orders grouped by product id and category - Product",
"preserveNullAndEmptyArrays": true
}
},
{
"$group": {
"_id": null,
"count":
{
"$sum": 1
}
}
},
{
"$sort":
{
"_id": 1
}
},
{
"$project":
{
"_id": false,
"count": true
}
}
]
```
Bug
Major - P3
