Large aggregation is slow and needs hours

Working with Data
,
1

Hi,

I’m trying to create a view based on an aggregation with a huge amount of Data.
To a specific point the query is running pretty fast. But the last lookup is killing the run time.

I’ve created the following index:
INVENTORY: (“Hostname”, “Hostname_Customer”, “Hostname_Customer_Measure”)
SERVER: (“COMPANY”, “SUPPORT_GROUP”)

db.getCollection("SERVER").aggregate(
[
  {
    "$lookup": {
      "from": "FRAMEWORK_MATRIX",
      "localField": "COMPANY",
      "foreignField": "CUSTOMER",
      "as": "FRAMEWORK_CUSTOMER"
    }
  },
  {
    "$unwind": "$FRAMEWORK_CUSTOMER"
  },
  {
    "$addFields": {
      "FRAMEWORK_CUSTOMER": "$FRAMEWORK_CUSTOMER.CUSTOMER"
    }
  },
  {
    "$lookup": {
      "from": "SUPPORT_MATRIX",
      "localField": "SUPPORT_GROUP",
      "foreignField": "GROUP",
      "as": "SUPPORT"
    }
  }
  {
    "$match": {
      "$or": [
        {
          "SUPPORT": { "$ne": [] }
        }
  },
  {
    "$project": {
      "_id": 0,
      "COMPANY": 1,
      "NAME": 1,
      "SUPPORT_GROUP": 1,
      "FRAMEWORK_CUSTOMER": 1
    }
  },
  {
    "$match": {
      "STATUS": "Live"
    }
  },
    ---->>> TILL HERE EVERYTHING IS FAST
  {
    "$lookup": {
      "from": "INVENTORY",
      "let": {
        "name": "$NAME",
        "frameworkCustomer": "$FRAMEWORK_CUSTOMER"
      },
      "pipeline": [
        {
          "$match": {
            "Measure": "Operating-System-Information",
            "$expr": {
              "$and": [
                {
                  "$regexMatch": {
                    "input": "$Hostname",
                    "regex": "$$name",
                    "options": "i"
                  }
                },
                {
                  "$regexMatch": {
                    "input": "$Customer",
                    "regex": "$$frameworkCustomer",
                    "options": "i"
                  }
                }
              ]
            }
          }
        }
      ],
      "as": "Inventory"
    }
  }
]
);

Any idea how I could speed up things? Am I doing anything wrong?
Thanks
Eric

2

Please share the explain plan.

Share the indexes you have on INVENTORY.

Regular expression are slower than normal, especially when not anchored at the beginning.