You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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鈥檓 trying to retrieve over 35,000 rows, and it fails.
Do you have any insight into why Directus constructs queries in this manner?
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
The text was updated successfully, but these errors were encountered:
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)
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鈥檓 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
The text was updated successfully, but these errors were encountered: