Directus app request slows down with increasing amount of files

[domdus]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

When logging into the app it does a request to /users/me to receive the logged-in user data.

Request (made by Directus app):
/users/me?fields=*,avatar.*,role.*

The response time for the data actually seems to be related to the amount files stored with directus and it gets slower and slower, the more files are created.
With nearly 400.000 files the response time for the request reaches about 45 s and more.
While the initial wait would not be an issue, the app now started crashing with a JavaScript heap out of memory and I cannot continue on the project without decreasing the amount of files. (Its almost there! Only a few more files)

Debugging:

The request using field avatar + wildcard (*) seems to cause a very intense DB query. CPU usage is very high during this time.
/users/me?fields=*,avatar.*,role.*:

Just leave the wildcard (*),
/users/me?fields=*,avatar,role.*:

Explicitely call for avatar fields id, image:
/users/me?fields=*,avatar.id,avatar.image,role.*

Is it possible to change this request to call for explicit fields while still meet your requirements? I could not find any combination of requested fields that slows down as (*) does.
Let me know if you need further info or debugging.

Further Info:

(NODE_OPTIONS=--max_old_space_size=8192 did not help)

Browser Network:

Logs:
<--- Last few GCs ---> [4085:0x5e72fc0] 808322 ms: Mark-sweep (reduce) 4096.3 (4106.7) -> 4096.3 (4106.7) MB, 1732.5 / 0.0 ms (average mu = 0.123, current mu = 0.000) allocation failure scavenge might not succeed [4085:0x5e72fc0] 810307 ms: Mark-sweep (reduce) 4096.3 (4103.7) -> 4096.3 (4104.7) MB, 1984.5 / 0.0 ms (average mu = 0.068, current mu = 0.000) last resort GC in old space requested <--- JS stacktrace ---> FATAL ERROR: Ineffective mark-compacts near heap limit Allocation failed - JavaScript heap out of memory

To Reproduce

Create 400.000 files and log into directus app.

What version of Directus are you using?

v9.0.0-rc.90

What version of Node.js are you using?

14.15.4

What database are you using?

Postgres 13

What browser are you using?

Chrome

What operating system are you using?

Windows 10

How are you deploying Directus?

PaaS

[rijkvanzanten]

Hi @domdus! This sounds very similar to #6184. Do you have a lot of users? Do you have any o2m fields from files going out?

[domdus]

Nope, only 12 users. (editors & read-only application user)

No O2M at all. I have a few collections that use the multiple_files field (M2M) for the mass of files. Beside that very basic, only another M2M for a categories collection.

[rijkvanzanten]

Ah gotcha, thanks. The problem here is that that main file * also fetches the first level of that O2M under the M2Ms to files, causing #6184 to show its ugly head. You are correct in saying we should just fetch the correct fields in avatar, which is what we'll do to fix this, but the real underlying slow down is caused by that other issue 👍🏻