Performance issues with SQL queries on multiple collections

[lucashamon]

Describe the Bug

Hello! 👋

I am facing a significant performance issue with queries that involve retrieving large amounts of data.

When trying to get data from a relational schema, Directus always constructs multiple queries using the SQL IN operator, rather than a simple JOIN. This approach works fine with a small amount of data, but in my case, I’m trying to retrieve over 35,000 rows, and it fails.

Do you have any insight into why Directus constructs queries in this manner?

Regards,

To Reproduce

Exemple with a simple relational schema :

{{directus_url}}/items/referentiel_code_postal_commune?fields[]=commune_id.*

Directus is performing two queries :

select "referentiel_code_postal_commune"."commune_id", "referentiel_code_postal_commune"."id" from "referentiel_code_postal_commune" order by "referentiel_code_postal_commune"."id" asc;

select "referentiel_commune"."id", "referentiel_commune"."zone_id" from "referentiel_commune" where "referentiel_commune"."id" in ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10); [c1c14734-470f-4073-869f-6a54e9879cf1, c82cbb9e-3de3-4103-807b-c9af26a58d55, 05e9f61d-1dad-4e40-80ae-2e968f9caa5d, 92df3ae3-a411-49a3-9048-a827901c4127, aed7af07-4006-413b-a89e-ff397a359783, 51f93a63-cc31-47ad-b209-300363d8398a, 8cc43864-ceab-4de4-82ae-1327b5828e49, 1dd8f2db-133e-465f-afd9-9935485a2d6b, 7bd86e3c-e1c1-443d-bbd3-d6298d0c7f6e, 327ac52d-e8fe-4a2d-879b-00211aed4054]

Directus Version

9.26.0

Hosting Strategy

Self-Hosted (Docker Image)

Database

No response

[lucashamon]

Hi again,

I installed a fresh new Directus instance, in its latest version. The issue is still present, even with a completely different data model.

http://localhost:8055/items/annonces?fields=*,type.*

Directus is still performing two queries and doesn’t use the JOIN operator. 😞

[br41nslug]

What database vendor are you using?

[lucashamon]

What database vendor are you using?

PostgreSQL for my app, SQLite for the fresh instance in first comment

[BenoitAverty]

I can confirm this is happening for my case as well (directus 10.9)

Retrieving items from a collection with several relationships can result in many queries. While each query is using the primary key index, this is still using up several hundred milliseconds just for retrieving data (fortunately directus correctly does JOINS for nested filters, the problem is only when selecting nested fields as far as I can tell)