Listing collection takes 30+ seconds

System Information
  • Strapi Version:3.2.4
  • Operating System:Linux (Docker official node:12 image)
  • Database:Mongo
  • **Node Version **: 12.20.0
  • NPM Version:
  • Yarn Version: 1.22.5

We have a fairly extreme setup with around 30k media files, and there will be more. I didn’t notice until starting to use more restricted custom roles that this is a problem. What happens is that the call on the collection list page, it takes 30+ seconds to execute when being logged in with a custom role, while the same view takes about 1s for the super user. The API call taking time is this one:

/content-manager/explorer/application::exam.exam?_limit=20&_sort=internalName%3AASC&_start=0

We’re a bit at a loss at what to do in this case. We need the custom roles, and the amount of media files is going to keep growing, and it’ll be at 100k in a not too distant future. We tried removing the images in the collection listing, as we figured that might be the culprit but we get the same times also without showing that column.

When looking at the query sent to Mongo it looks pretty nasty:

Query
{
  "command": {
    "aggregate": "collection_type",
    "pipeline": [
      {
        "$lookup": {
          "from": "upload_file",
          "as": "images",
          "let": {
            "localId": "$_id",
            "localAlias": "$images"
          },
          "pipeline": [
            {
              "$unwind": {
                "path": "$related",
                "preserveNullAndEmptyArrays": true
              }
            },
            {
              "$match": {
                "$expr": {
                  "$and": [
                    {
                      "$eq": [
                        "$related.ref",
                        "$$localId"
                      ]
                    },
                    {
                      "$eq": [
                        "$related.field",
                        "images"
                      ]
                    }
                  ]
                }
              }
            }
          ]
        }
      },
      {
        "$lookup": {
          "from": "collection_type2",
          "as": "collection_type2",
          "let": {
            "localId": "$_id",
            "localAlias": "$collection_type2"
          },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$eq": [
                    "$collection_type",
                    "$$localId"
                  ]
                }
              }
            },
            {
              "$lookup": {
                "from": "upload_file",
                "as": "documents",
                "let": {
                  "localId": "$_id",
                  "localAlias": "$documents"
                },
                "pipeline": [
                  {
                    "$unwind": {
                      "path": "$related",
                      "preserveNullAndEmptyArrays": true
                    }
                  },
                  {
                    "$match": {
                      "$expr": {
                        "$and": [
                          {
                            "$eq": [
                              "$related.ref",
                              "$$localId"
                            ]
                          },
                          {
                            "$eq": [
                              "$related.field",
                              "documents"
                            ]
                          }
                        ]
                      }
                    }
                  }
                ]
              }
            },
            {
              "$lookup": {
                "from": "upload_file",
                "as": "images",
                "let": {
                  "localId": "$_id",
                  "localAlias": "$images"
                },
                "pipeline": [
                  {
                    "$unwind": {
                      "path": "$related",
                      "preserveNullAndEmptyArrays": true
                    }
                  },
                  {
                    "$match": {
                      "$expr": {
                        "$and": [
                          {
                            "$eq": [
                              "$related.ref",
                              "$$localId"
                            ]
                          },
                          {
                            "$eq": [
                              "$related.field",
                              "images"
                            ]
                          }
                        ]
                      }
                    }
                  }
                ]
              }
            }
          ]
        }
      },
      {
        "$lookup": {
          "from": "strapi_administrator",
          "as": "uploadedBy",
          "let": {
            "localId": "$_id",
            "localAlias": "$uploadedBy"
          },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$eq": [
                    "$$localAlias",
                    "$_id"
                  ]
                }
              }
            }
          ]
        }
      },
      {
        "$lookup": {
          "from": "upload_file",
          "as": "document",
          "let": {
            "localId": "$_id",
            "localAlias": "$document"
          },
          "pipeline": [
            {
              "$unwind": {
                "path": "$related",
                "preserveNullAndEmptyArrays": true
              }
            },
            {
              "$match": {
                "$expr": {
                  "$and": [
                    {
                      "$eq": [
                        "$related.ref",
                        "$$localId"
                      ]
                    },
                    {
                      "$eq": [
                        "$related.field",
                        "document"
                      ]
                    }
                  ]
                }
              }
            }
          ]
        }
      },
      {
        "$lookup": {
          "from": "strapi_administrator",
          "as": "created_by",
          "let": {
            "localId": "$_id",
            "localAlias": "$created_by"
          },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$eq": [
                    "$$localAlias",
                    "$_id"
                  ]
                }
              }
            }
          ]
        }
      },
      {
        "$lookup": {
          "from": "strapi_administrator",
          "as": "updated_by",
          "let": {
            "localId": "$_id",
            "localAlias": "$updated_by"
          },
          "pipeline": [
            {
              "$match": {
                "$expr": {
                  "$eq": [
                    "$$localAlias",
                    "$_id"
                  ]
                }
              }
            }
          ]
        }
      },
      {
        "$match": {
          "$and": [
            {
              "$or": [
                {
                  "$and": [
                    {
                      "created_by._id": {
                        "$oid": "5fce289787560b0013ba34c8"
                      }
                    }
                  ]
                }
              ]
            }
          ]
        }
      },
      {
        "$project": {
          "_id": true
        }
      }
    ],
    "cursor": {},
    "lsid": {
      "id": {
        "$binary": "iAIJyatuQUadZfGQ1Mukqg==",
        "$type": "03"
      }
    },
    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1607348145,
          "i": 2
        }
      },
      "signature": {
        "hash": {
          "$binary": "PHh4eHh4eD4=",
          "$type": "00"
        },
        "keyId": {
          "$numberLong": "6851269278675501057"
        }
      }
    },
    "$db": "deepThoughtLive"
  },
  "planSummary": [
    {
      "COLLSCAN": {}
    }
  ],
  "keysExamined": 0,
  "docsExamined": 16,
  "cursorExhausted": 1,
  "numYields": 21687,
  "nreturned": 0,
  "reslen": 219,
  "locks": {
    "Global": {
      "acquireCount": {
        "r": 48276
      }
    },
    "Database": {
      "acquireCount": {
        "r": 24138
      }
    },
    "Collection": {
      "acquireCount": {
        "r": 24137
      }
    }
  },
  "protocol": "op_msg",
  "millis": 33208
}

Is this a known issue? Are there any workarounds? For us it’s obviously a show stopper if our data entry people will have to wait 30-50 seconds when saving and I’m really hoping there’s a fix or workaround out there.

Is this the correct place or should I open an issue in Github?

That’s because mongo is not doing so well with relations (files are in polymorphic relation with content types). As I remember that’s a known issue. You can check the GitHub issues. I would recommend switching to a relational database for your needs.