prisma1 Prisma join performances

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.

Jul 16 '19 09:07 Hebilicious

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.

Jul 16 '19 14:07 mavilein

@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 ?

Jul 17 '19 23:07 Hebilicious

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)

Aug 06 '19 09:08 crubier

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:

User
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 ?

Aug 09 '19 15:08 juliendangers

😱 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.

Aug 09 '19 17:08 crubier

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

Aug 12 '19 10:08 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

Aug 27 '19 09:08 juliendangers

@juliendangers : That looks like the right direction.

Sep 09 '19 15:09 mavilein

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

Dec 02 '19 12:12 peterrogov

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 !

Jan 20 '20 14:01 juliendangers

is that fixed?

Feb 03 '22 08:02 hosmanoglu

@hosmanoglu prisma1 is not maintained anymore, see 4898

Feb 03 '22 08:02 juliendangers

@hosmanoglu prisma1 is not maintained anymore, see 4898

i use "prisma": "^3.9.0". its still run 2 select instead of join . its realy bad performance

Feb 03 '22 08:02 hosmanoglu

The join already happens when we use relations on the where condition. I don't know the source code but maybe some work is already done.

Mar 21 '22 09:03 seromenho

Kinda related. I've created a repo with a reproducible error because of a huge query generated instead of a join here: https://github.com/seromenho/prisma-join-performance Failing test here: https://github.com/seromenho/prisma-join-performance/runs/6396987212?check_suite_focus=true

May 11 '22 22:05 seromenho

Kinda related. I've created a repo with a reproducible error because of a huge query generated instead of a join here: https://github.com/seromenho/prisma-join-performance Failing test here: https://github.com/seromenho/prisma-join-performance/runs/6396987212?check_suite_focus=true

that's what i am talking about. its been a long time so i couldn't give spesific example. i use raw query somewhere cause of this and i saw one time if the query which is too big its kill connection.its just not only bad working somethimes even not working

May 13 '22 10:05 hosmanoglu

I think you should open an issue on the actual prima repo, or look for performance related issues on the prisma repo. This project is not maintained.

May 13 '22 12:05 juliendangers

I think you should open an issue on the actual prima repo, or look for performance related issues on the prisma repo. This project is not maintained.

wait is that really prisma1 repo :D i didnt know that :D whatever probably i dont use prisma anymore :D cause of that i can't be bothered

but really thans to answer me i appreciate that

May 13 '22 12:05 hosmanoglu

Kinda related. I've created a repo with a reproducible error because of a huge query generated instead of a join here: https://github.com/seromenho/prisma-join-performance Failing test here: https://github.com/seromenho/prisma-join-performance/runs/6396987212?check_suite_focus=true

Not sure if you did but you should open an issue in https://github.com/prisma/prisma/issues (this is prisma 1). Pretty sure prisma2 has the same issue though.

May 14 '22 16:05 Hebilicious

@Hebilicious Oh yes I did. This is still something from prisma1 so left a comment also here. Ref: prisma/prisma/issues/13306

May 16 '22 22:05 seromenho