Directus API sluggish when requesting relational data

[hmaskat17]

Describe the Bug

When making a request to the Directus API with minimal amount of fields and including any relational data, the server response is very slow. For example, when requesting id fields for 6000 items with any relational property, the response may take 40 seconds and sometimes much longer. The response is still sluggish when requesting fewer items.

Example request fetching locations with related M2M categories: https://my-directus.net/items/location?fields=id,category.category_id&limit=-1

When checking the servers tracer logs, there are two SQL queries being made (just an example with a limit of 1):

select "location"."id" from "location" order by "location"."id" asc limit $1 [1]

select "location_category"."category_id", "location_category"."location_id", "location_category"."id" from "location_category" where "location_category"."location_id" in ($1) order by "location_category"."id" asc limit $2 [00021bef-fe68-4d46-9f2g-67e991dfbkc2, 25000]

When testing these queries manually and requesting all items in the PostgreSQL database they respond quickly within milliseconds so database seems fine.

Tested API requests with caching enabled and disabled. When enabled the first request is slow and following are responsive as expected.

When removing relational data from the API request, the server response is fast and responds in 374 ms.

Here are response times with different limits when requesting above URL (including the relational data):

Response in ms/s	Request limit
175 ms	50
1.51 s	1000
11.04 s	3000
37.04 s	-1 (5700 items)

Any help and guidance is appreciated.

To Reproduce

1. Make an API GET request to a collection asking for a larger amount of items and including relational data.

Directus Version

v10.7.2

Hosting Strategy

Self-Hosted (Docker Image)

[br41nslug]

Could there be network issues between the directus instance and the database? or perhaps not enough resources available to the directus instance?

[hmaskat17]

Could there be network issues between the directus instance and the database? or perhaps not enough resources available to the directus instance?

Cannot be certain but i don't think it is a network issue since other requests and Directus app work normally. We tested scaling our resources up without any changes. I believe we tested using resources above the recommendation and when monitoring the usage of CPU and memory everything looked fine (not hitting any usage limits).

[rijkvanzanten]

@hmaskat17 Would you be able to share a postgres sql dump of a setup where this is easily reproducible? I haven't seen other reports yet on the same issue, but it could be that you're hitting a specific bottleneck somewhere in this specific schema 🙂

[hmaskat17]

@rijkvanzanten I'm trying to test the dump file in a Directus instance but I'm not too sure how to create a minimal and viable working dump file that i can share here (that have relational data included). Are there any directus specific database entities needed for the database dump?

[rijkvanzanten]

@hmaskat17 If at all possible, the easiest way for me to be able to reproduce the exact issue is by having a pg_dump that I can just important and have everything 1-1. This is the type of issue that's hard to reproduce without the exact hardware and config, so the closer I can get to your exact setup the better 👍🏻

[hmaskat17]

@rijkvanzanten Understandable but unfortunately we cannot share the whole database here as it is an application in production and contains sensitive data. Will need to debug this issue another way. Do you have any tips on how and where to investigate further?

Since we are almost purely using Directus backend as-is without any hooks etc, I think we can rule out any issues in the code if there are no other reports here either. The only reasoning would be a database schema and configuration problem? So we should probably try testing schema changes and debugging database?

We are temporarily setting up a custom endpoint extension in Directus to retrieve the same relational data and it is currently returning data in ~500 ms.