Prisma1: Prisma join performances

[Hebilicious]

Is your feature request related to a problem? Please describe.
I'm working on a large-ish IOT project with a non trivial amount of data. The project uses Prisma as a DAL on AWS Fargate, with an Aurora (Postgres) database. I've noticed that for some of the data retrieval needs of the project, I had to fallback to raw SQL for performance reasons. Some of the queries that I try to do with the prisma client end up crashing the Prisma server without returning any data.

Describe the solution you'd like
I would like a way to retrieve data from different tables (call it relationships or join) using Prisma or Prisma2 (which I haven't tried for this project yet) in an efficient way, one that doesn't crash the server and doesn't take more than 30s to run.

Describe alternatives you've considered
Raw SQL/ Low level tools (knex,pg) which defeats the point a little.
In a graphQL server context, Overriding resolvers provided by nexus-prisma.

Additional context
I'll provide as much information as I'm allowed to.
This is a simplified version of the datastructure. The missing fields are mostly strings and irrelevant to the issue, and each table has createdAt and updatedAt fields defined in the datamodel.

type Device {
    id: ID! @id
    deviceUpdates: [DeviceUpdate!]!
}
type DeviceUpdate {
    id: ID! @id
    device: Device!
    sensorUpdates: [SensorUpdate!]! @relation(onDelete: CASCADE)
}

type SensorUpdate {
    id: ID! @id
    sensor: Sensor!
    deviceUpdate: DeviceUpdate!
}
type Sensor {
    id: ID! @id
    sensorUpdate: [SensorUpdate!]
}

Two of those tables are 'growing', deviceUpdate and sensorUpdate, they get a considerable amount of new entries regularly.
The device table is expected to have on average thousands of entries (will scale up to 50000 entries).
On average each device makes 10 updates a day, so thedeviceUpdate table roughly grows by the number of devices * 10 every day.
The sensorUpdate table is between 1 and 5 times the size of the DeviceUpdate.
The sensor table is roughly a hundred entries.

The type of queries that i’m trying to run looks like this :

query{
  devices(first: 10){
    id
    deviceUpdates(first: 100){
      id
      sensorUpdates{
        id
        sensor{
          id
        }
      }
    }
  }
}

with potentially more query parameters, such as filtering and ordering.
This type of queries takes ages to complete, and very often they end up crashing the prisma server in most cases.
The data retrieval can be expressed with the following SQL queries :

Very slow query (minutes):

SELECT *
FROM "Device" d
LEFT JOIN "DeviceUpdate" du ON d.id = du.device
LEFT JOIN "SensorUpdate" su ON du.id = 'su.deviceUpdate'
LEFT JOIN "Sensor" s ON su.id = 's.sensorUpdate'
WHERE d.id IN(...)
WHERE s.id IN(...); 

But the same result can be achieved in a much more performant way.
Fast query (seconds) :

SELECT*
FROM "Sensor" s
INNER JOIN "SensorUpdate" su ON su.sensor = s.id
INNER JOIN "DeviceUpdate" du ON du.id = su."deviceUpdate"
INNER JOIN "Device" d ON d.id = du.device AND d.id IN(...)
WHERE s.id IN(...);

I'm sure it's possible to write a more performant SQL query, or maybe to play around with indexes to achieve desired performances. But I don't see a way to do that with Prisma.

[mavilein]

Hey @Hebilicious ,
thanks for reaching out with such an insightful writeup! 🙏 This kicked off a very nice discussion in our team slack. Internally we have talked about a query planner component for quite some time now. By chance, we just finished a 2 hour meeting discussing how we will move towards this idea. Just wanted to let you know this is very very useful for us! 🙏 It will take some more time though to implement it.

[Hebilicious]

@mavilein That's very good to hear ! Thank you for all your work on Prisma, I've been using it a lot recently and I'm happy to contribute (I have a few suggestions for prisma2, need to find the time to post them).
In the meantime, is there any kind of optimisations you would recommend using prisma in the context of a graphQL server to handle data retrieval with large tables like this ?

[crubier]

For what it's worth we are having similar problems. Our migration from MySql on Aurora to Postgres on Aurora degraded performances badly (Queries takes ~10x longer than on mysql, and manual queries with similar results are also much faster)

[juliendangers]

Hello, we also noticed poor performances with join on big queries (Postgres Aurora & Postgres on RDS).

Our model has some limitations we are currently addressing (see queries below), but some optimizations could be done at prisma level too, mainly with table relations. We have 20+ types, so I won't put the whole schema here, but if you need more information I'd be happy to help.

Here is a simple example:

type User @db(name: "User") {
  id: ID! @id
  name: String!
  email: String! @unique
  password: String!
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  deletedAt: DateTime
  expertOfMissions: [Mission!]! @relation(name: "MissionExpert", link: TABLE)
  organizations: [Organization!]! @relation(name: "OrganizationUsers", link: TABLE)
  adminIn: [Organization!]! @relation(name: "OrganizationAdmin")
}

type Organization @db(name: "Organization") {
  id: ID! @id
  name: String!
  description: String
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  deletedAt: DateTime
  admins: [User!]! @relation(name: "OrganizationAdmin", link: TABLE)
  users: [User!]! @relation(name: "OrganizationUsers")
}

type Mission @db(name: "Mission") {
  id: ID! @id
  name: String
  createdAt: DateTime! @createdAt
  updatedAt: DateTime! @updatedAt
  deletedAt: DateTime
  experts: [User!]! @relation(name: "MissionExpert")
}

Prisma would generate tables like the following:

Organization
Mission
_MissionExpert
_OrganizationUsers
_OrganizationAdmin

Now let's say I want mission ids and names which user cjvy026t7515i07463lkm5n32 is expert of.

query expertOfMissions($id: ID!) { 
  missions(where: {experts_some: {id: $id}}) {
    id
    name
  }
}

Prisma generates the following SQL query:

select 
  "Alias"."id", 
  "Alias"."name"
from "service$stage"."Mission" as "Alias"
where "Alias"."id" in (
  select "service$stage"."_MissionExpert"."A"
  from "service$stage"."_MissionExpert"
    join "service$stage"."User" as "User_Alias"
    on "User_Alias"."id" = "service$stage"."_MissionExpert"."B"
  where "User_Alias"."id" = 'cjvy026t7515i07463lkm5n32'
)
order by "Alias"."id" asc
offset 0

Query seems pretty fine, but since I'm not requesting any user's attribute, nor filtering on anything else than his id, the join on User is actually useless. And in big queries, many useless joins can have a high cost. Simple optimized query would look like:

select 
  "Alias"."id", 
  "Alias"."name"
from "service$stage"."Mission" as "Alias"
where "Alias"."id" in (
  select "service$stage"."_MissionExpert"."A"
  from "service$stage"."_MissionExpert"
  where "service$stage"."_MissionExpert"."B" = 'cjvy026t7515i07463lkm5n32'
)
order by "Alias"."id" asc
offset 0

So can you have an idea of the impact of such limitation, here is an example SQL query generated by Prisma, which timeout after 300 sec (I defined statement_timeout to 300s...):

SELECT "Alias"."id"
FROM "service$stage"."Structure" AS "Alias"
WHERE (("Alias"."id" IN
          (SELECT "service$stage"."Structure"."id"
           FROM "service$stage"."Structure"
           WHERE "service$stage"."Structure"."organization" IN
               (SELECT "service$stage"."_OrganizationUsers"."A"
                FROM "service$stage"."_OrganizationUsers"
                JOIN "service$stage"."User" AS "User_Organization_Alias" ON "User_Organization_Alias"."id" = "service$stage"."_OrganizationUsers"."B"
                WHERE "User_Organization_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureOwners"."A"
           FROM "service$stage"."_StructureOwners"
           JOIN "service$stage"."User" AS "User_Alias" ON "User_Alias"."id" = "service$stage"."_StructureOwners"."B"
           WHERE "User_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureDevelopers"."A"
           FROM "service$stage"."_StructureDevelopers"
           JOIN "service$stage"."User" AS "User_Alias" ON "User_Alias"."id" = "service$stage"."_StructureDevelopers"."B"
           WHERE "User_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureOperators"."A"
           FROM "service$stage"."_StructureOperators"
           JOIN "service$stage"."User" AS "User_Alias" ON "User_Alias"."id" = "service$stage"."_StructureOperators"."B"
           WHERE "User_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructurePlanStructures"."A"
           FROM "service$stage"."_StructurePlanStructures"
           WHERE "service$stage"."_StructurePlanStructures"."B" IN
               (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                FROM "service$stage"."_StructurePlanMissionPlan"
                WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                    (SELECT "service$stage"."Mission"."plan"
                     FROM "service$stage"."Mission"
                     JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Alias" ON "Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."Mission"."id"
                     WHERE ("Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionOperator"."A"
                               FROM "service$stage"."_MissionOperator"
                               JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Alias" ON "User_Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."_MissionOperator"."B"
                               WHERE "User_Mission_MissionPlan_StructurePlan_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionClients"."A"
                               FROM "service$stage"."_MissionClients"
                               JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Alias" ON "User_Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."_MissionClients"."B"
                               WHERE "User_Mission_MissionPlan_StructurePlan_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionInspector"."A"
                               FROM "service$stage"."_MissionInspector"
                               JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Alias" ON "User_Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."_MissionInspector"."B"
                               WHERE "User_Mission_MissionPlan_StructurePlan_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionExpert"."A"
                               FROM "service$stage"."_MissionExpert"
                               JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Alias" ON "User_Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."_MissionExpert"."B"
                               WHERE "User_Mission_MissionPlan_StructurePlan_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."Structure"."id"
                FROM "service$stage"."Structure"
                WHERE "service$stage"."Structure"."organization" IN
                    (SELECT "service$stage"."_OrganizationUsers"."A"
                     FROM "service$stage"."_OrganizationUsers"
                     JOIN "service$stage"."User" AS "User_Organization_Structure_Alias" ON "User_Organization_Structure_Alias"."id" = "service$stage"."_OrganizationUsers"."B"
                     WHERE "User_Organization_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureOwners"."A"
                FROM "service$stage"."_StructureOwners"
                JOIN "service$stage"."User" AS "User_Structure_Alias" ON "User_Structure_Alias"."id" = "service$stage"."_StructureOwners"."B"
                WHERE "User_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureDevelopers"."A"
                FROM "service$stage"."_StructureDevelopers"
                JOIN "service$stage"."User" AS "User_Structure_Alias" ON "User_Structure_Alias"."id" = "service$stage"."_StructureDevelopers"."B"
                WHERE "User_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureOperators"."A"
                FROM "service$stage"."_StructureOperators"
                JOIN "service$stage"."User" AS "User_Structure_Alias" ON "User_Structure_Alias"."id" = "service$stage"."_StructureOperators"."B"
                WHERE "User_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructurePlanStructures"."A"
                FROM "service$stage"."_StructurePlanStructures"
                WHERE "service$stage"."_StructurePlanStructures"."B" IN
                    (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                     FROM "service$stage"."_StructurePlanMissionPlan"
                     WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                         (SELECT "service$stage"."Mission"."plan"
                          FROM "service$stage"."Mission"
                          JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Structure_Alias" ON "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."Mission"."id"
                          WHERE ("Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionOperator"."A"
                                    FROM "service$stage"."_MissionOperator"
                                    JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."_MissionOperator"."B"
                                    WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionClients"."A"
                                    FROM "service$stage"."_MissionClients"
                                    JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."_MissionClients"."B"
                                    WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionInspector"."A"
                                    FROM "service$stage"."_MissionInspector"
                                    JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."_MissionInspector"."B"
                                    WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionExpert"."A"
                                    FROM "service$stage"."_MissionExpert"
                                    JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."_MissionExpert"."B"
                                    WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )) ) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."Structure"."id"
                     FROM "service$stage"."Structure"
                     WHERE "service$stage"."Structure"."organization" IN
                         (SELECT "service$stage"."_OrganizationUsers"."A"
                          FROM "service$stage"."_OrganizationUsers"
                          JOIN "service$stage"."User" AS "User_Organization_Structure_Structure_Alias" ON "User_Organization_Structure_Structure_Alias"."id" = "service$stage"."_OrganizationUsers"."B"
                          WHERE "User_Organization_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureOwners"."A"
                     FROM "service$stage"."_StructureOwners"
                     JOIN "service$stage"."User" AS "User_Structure_Structure_Alias" ON "User_Structure_Structure_Alias"."id" = "service$stage"."_StructureOwners"."B"
                     WHERE "User_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureDevelopers"."A"
                     FROM "service$stage"."_StructureDevelopers"
                     JOIN "service$stage"."User" AS "User_Structure_Structure_Alias" ON "User_Structure_Structure_Alias"."id" = "service$stage"."_StructureDevelopers"."B"
                     WHERE "User_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureOperators"."A"
                     FROM "service$stage"."_StructureOperators"
                     JOIN "service$stage"."User" AS "User_Structure_Structure_Alias" ON "User_Structure_Structure_Alias"."id" = "service$stage"."_StructureOperators"."B"
                     WHERE "User_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructurePlanStructures"."A"
                     FROM "service$stage"."_StructurePlanStructures"
                     WHERE "service$stage"."_StructurePlanStructures"."B" IN
                         (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                          FROM "service$stage"."_StructurePlanMissionPlan"
                          WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                              (SELECT "service$stage"."Mission"."plan"
                               FROM "service$stage"."Mission"
                               JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."Mission"."id"
                               WHERE ("Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionOperator"."A"
                                         FROM "service$stage"."_MissionOperator"
                                         JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."_MissionOperator"."B"
                                         WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionClients"."A"
                                         FROM "service$stage"."_MissionClients"
                                         JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."_MissionClients"."B"
                                         WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionInspector"."A"
                                         FROM "service$stage"."_MissionInspector"
                                         JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."_MissionInspector"."B"
                                         WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionExpert"."A"
                                         FROM "service$stage"."_MissionExpert"
                                         JOIN "service$stage"."User" AS "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."_MissionExpert"."B"
                                         WHERE "User_Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = 'cjvy026t7515i07463lkm5n32' )) ) ) ) ) ))
       AND "Alias"."id" IN
         (SELECT "service$stage"."Structure"."id"
          FROM "service$stage"."Structure"
          JOIN "service$stage"."StructureModel" AS "StructureModel_Alias" ON "StructureModel_Alias"."id" = "service$stage"."Structure"."model"
          WHERE "StructureModel_Alias"."type" IN ('WindTurbine',
                                                  'HighVoltagePowerLinePylon',
                                                  'MediumVoltagePowerLinePylon',
                                                  'Fence') ))
ORDER BY "Alias"."id" ASC
OFFSET 0

And here is the same query, after I performed the optimization of removing useless joins, which runs under 1 sec:

SELECT "Alias"."id"
FROM "service$stage"."Structure" AS "Alias"
WHERE (("Alias"."id" IN
          (SELECT "service$stage"."Structure"."id"
           FROM "service$stage"."Structure"
           WHERE "service$stage"."Structure"."organization" IN
               (SELECT "service$stage"."_OrganizationUsers"."A"
                FROM "service$stage"."_OrganizationUsers"
                WHERE "service$stage"."_OrganizationUsers"."B" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureOwners"."A"
           FROM "service$stage"."_StructureOwners"
           WHERE "service$stage"."_StructureOwners"."B" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureDevelopers"."A"
           FROM "service$stage"."_StructureDevelopers"
           WHERE "service$stage"."_StructureDevelopers"."B" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureOperators"."A"
           FROM "service$stage"."_StructureOperators"
           WHERE "service$stage"."_StructureOperators"."B" = 'cjvy026t7515i07463lkm5n32' )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructurePlanStructures"."A"
           FROM "service$stage"."_StructurePlanStructures"
           WHERE "service$stage"."_StructurePlanStructures"."B" IN
               (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                FROM "service$stage"."_StructurePlanMissionPlan"
                WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                    (SELECT "service$stage"."Mission"."plan"
                     FROM "service$stage"."Mission"
                     JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Alias" ON "Mission_MissionPlan_StructurePlan_Alias"."id" = "service$stage"."Mission"."id"
                     WHERE ("Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionOperator"."A"
                               FROM "service$stage"."_MissionOperator"
                               WHERE "service$stage"."_MissionOperator"."B" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionClients"."A"
                               FROM "service$stage"."_MissionClients"
                               WHERE "service$stage"."_MissionClients"."B" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionInspector"."A"
                               FROM "service$stage"."_MissionInspector"
                               WHERE "service$stage"."_MissionInspector"."B" = 'cjvy026t7515i07463lkm5n32' )
                            OR "Mission_MissionPlan_StructurePlan_Alias"."id" IN
                              (SELECT "service$stage"."_MissionExpert"."A"
                               FROM "service$stage"."_MissionExpert"
                               WHERE "service$stage"."_MissionExpert"."B" = 'cjvy026t7515i07463lkm5n32' )) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."Structure"."id"
                FROM "service$stage"."Structure"
                WHERE "service$stage"."Structure"."organization" IN
                    (SELECT "service$stage"."_OrganizationUsers"."A"
                     FROM "service$stage"."_OrganizationUsers"
                     WHERE "service$stage"."_OrganizationUsers"."B" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureOwners"."A"
                FROM "service$stage"."_StructureOwners"
                WHERE "service$stage"."_StructureOwners"."B" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureDevelopers"."A"
                FROM "service$stage"."_StructureDevelopers"
                WHERE "service$stage"."_StructureDevelopers"."B" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureOperators"."A"
                FROM "service$stage"."_StructureOperators"
                WHERE "service$stage"."_StructureOperators"."B" = 'cjvy026t7515i07463lkm5n32' ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructurePlanStructures"."A"
                FROM "service$stage"."_StructurePlanStructures"
                WHERE "service$stage"."_StructurePlanStructures"."B" IN
                    (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                     FROM "service$stage"."_StructurePlanMissionPlan"
                     WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                         (SELECT "service$stage"."Mission"."plan"
                          FROM "service$stage"."Mission"
                          JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Structure_Alias" ON "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" = "service$stage"."Mission"."id"
                          WHERE ("Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionOperator"."A"
                                    FROM "service$stage"."_MissionOperator"
                                    WHERE "service$stage"."_MissionOperator"."B" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionClients"."A"
                                    FROM "service$stage"."_MissionClients"
                                    WHERE "service$stage"."_MissionClients"."B" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionInspector"."A"
                                    FROM "service$stage"."_MissionInspector"
                                    WHERE "service$stage"."_MissionInspector"."B" = 'cjvy026t7515i07463lkm5n32' )
                                 OR "Mission_MissionPlan_StructurePlan_Structure_Alias"."id" IN
                                   (SELECT "service$stage"."_MissionExpert"."A"
                                    FROM "service$stage"."_MissionExpert"
                                    WHERE "service$stage"."_MissionExpert"."B" = 'cjvy026t7515i07463lkm5n32' )) ) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."Structure"."id"
                     FROM "service$stage"."Structure"
                     WHERE "service$stage"."Structure"."organization" IN
                         (SELECT "service$stage"."_OrganizationUsers"."A"
                          FROM "service$stage"."_OrganizationUsers"
                          WHERE "service$stage"."_OrganizationUsers"."B" = 'cjvy026t7515i07463lkm5n32' ) ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureOwners"."A"
                     FROM "service$stage"."_StructureOwners"
                     WHERE "service$stage"."_StructureOwners"."B" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureDevelopers"."A"
                     FROM "service$stage"."_StructureDevelopers"
                     WHERE "service$stage"."_StructureDevelopers"."B" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructureOperators"."A"
                     FROM "service$stage"."_StructureOperators"
                     WHERE "service$stage"."_StructureOperators"."B" = 'cjvy026t7515i07463lkm5n32' ) ) )
        OR "Alias"."id" IN
          (SELECT "service$stage"."_StructureChildren"."B"
           FROM "service$stage"."_StructureChildren"
           WHERE "service$stage"."_StructureChildren"."A" IN
               (SELECT "service$stage"."_StructureChildren"."B"
                FROM "service$stage"."_StructureChildren"
                WHERE "service$stage"."_StructureChildren"."A" IN
                    (SELECT "service$stage"."_StructurePlanStructures"."A"
                     FROM "service$stage"."_StructurePlanStructures"
                     WHERE "service$stage"."_StructurePlanStructures"."B" IN
                         (SELECT "service$stage"."_StructurePlanMissionPlan"."B"
                          FROM "service$stage"."_StructurePlanMissionPlan"
                          WHERE "service$stage"."_StructurePlanMissionPlan"."A" IN
                              (SELECT "service$stage"."Mission"."plan"
                               FROM "service$stage"."Mission"
                               JOIN "service$stage"."Mission" AS "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias" ON "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" = "service$stage"."Mission"."id"
                               WHERE ("Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionOperator"."A"
                                         FROM "service$stage"."_MissionOperator"
                                         WHERE "service$stage"."_MissionOperator"."B" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionClients"."A"
                                         FROM "service$stage"."_MissionClients"
                                         WHERE "service$stage"."_MissionClients"."B" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionInspector"."A"
                                         FROM "service$stage"."_MissionInspector"
                                         WHERE "service$stage"."_MissionInspector"."B" = 'cjvy026t7515i07463lkm5n32' )
                                      OR "Mission_MissionPlan_StructurePlan_Structure_Structure_Alias"."id" IN
                                        (SELECT "service$stage"."_MissionExpert"."A"
                                         FROM "service$stage"."_MissionExpert"
                                         WHERE "service$stage"."_MissionExpert"."B" = 'cjvy026t7515i07463lkm5n32' )) ) ) ) ) ))
       AND "Alias"."id" IN
         (SELECT "service$stage"."Structure"."id"
          FROM "service$stage"."Structure"
          JOIN "service$stage"."StructureModel" AS "StructureModel_Alias" ON "StructureModel_Alias"."id" = "service$stage"."Structure"."model"
          AND "StructureModel_Alias"."type" IN ('WindTurbine',
                                                  'HighVoltagePowerLinePylon',
                                                  'MediumVoltagePowerLinePylon',
                                                  'Fence') ))
ORDER BY "Alias"."id" ASC
OFFSET 0

Is there any way I could help to bring this improvment on prisma 1 version ?

[crubier]

😱 This is a 300x+ performance improvement that can be solved with just a few lines of code... Might be worth fixing now even if Prisma v2 is coming: not everyone is going to migrate to v2 tomorrow.

[juliendangers]

Just seen #4754, which seems to partially match the case I explained above

[juliendangers]

@mavilein @pantharshit00 I'm looking for a way to improve the problem stated above, could you just tell me if I'm looking in the right direction with the following function https://github.com/prisma/prisma/blob/250243b42c39799b5c361fba29518a030e4a9440/server/connectors/api-connector-jdbc/src/main/scala/com/prisma/api/connector/jdbc/database/FilterConditionBuilder.scala#L79-L114 ? thx

[mavilein]

@juliendangers : That looks like the right direction.

[peterrogov]

Same problem here, adding few inner objects to prisma query leads to a dramatic increase on query execution time. Would have been cool to see any ideas on possible workaround while a robust solution is on its way

[juliendangers]

Hello,

We created a patch on our fork of Prisma, which in most case prevent a useless join on nested filter when we only check for the id, which is available in the relation table. It does the job for our use cases, so if it can help some of you, please have a look at https://github.com/Sterblue/prisma/pull/1

FYI, it does not cover all operators on ids, we only implemented the one we needed (contains is missing for example). It produces the query I was hoping for in my comment above https://github.com/prisma/prisma/issues/4744#issuecomment-519952592

Not sure we'll create an official PR, since the energy has been redirected to Prisma v2. But if anyone in Prisma team is willing to spend some time on it, we'd be happy to help !