P
Prisma•2mo ago
max

Horrendous performance with relationalJoins active for deep includes through models/views

I have difficulties finding out why out prisma queries are running so slow. Im firing 15 queries simultanously and they seem to return in batches of 6 each block being 600ms delayed. We use the default postgres docker-image but our schema is fairly complex. I have no idea where the batch of 6 is coming from and also why a single prisma sql query would even take 600ms to begin with for a db that doesnt even have 2000 rows. I am aware that the inbcludes im doing sometimes are like model.include.model.include.view.include.model.include.model . SO like 5 deep sometimes with multiple sibling includes aswell. But i would expec this to be good performance. I dont think the schema would fit in here due to length limits and privacy reasons, but generally: we have 
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "views"]
}
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["relationJoins", "views"]
}
As soon as I remove relational joins the individual queries are "only" 400ms compared to the previous 1000ms, and our db can handle the multiple concurrent queries smootly without accumulating slowness We really do like Prisma and use it in the client where we create the prisma args and send them to the server in a graphQl manner, which gives us full flexibilty in the client to request any dynamic joins that we like, but if we do have a query thats nested deeply that passes through a sql VIEW or too many models, it gets super slow. All necessary indices are set, but we suspect that the postgres query optimizer cannot pass down filters all the way into views or views make it slow cause they cannot be indexed, but those are just assumptions. But we're at the point where the ORM becomes pretty unusable with the flexibility we desire
5 Replies
Prisma AI Help
Prisma AI Help•2mo ago
Howdy, friend! I'm the Prisma AI Help Bot — fast answers from me, or slow, hand-crafted wisdom from a dev? Choose wisely, adventurer.
Max
Max•2mo ago
I was working on this exact issue yesterday! I was working with postgres and had something like 15-20 nested joins. My first improvement was to use different indexes because we were doing string contains operations (ie. ILIKE "%something%") . Instead of B-trees we switched to GIN indexes. Also make sure you don't use mode: 'insensitive' if you're doing string queries because that forces a sequential scan. Even still, with indexes that actually work, it was REALLY slow. I we really had to just reduce the amount of data we were pulling :/ But, if you're doing text searches, you could try switching your indexes and see if that improves things. Also see if you can't turn on query logging and throw the output into postgres or mysql and dig through the query planning results If you see a lot of seqscan operations indexes either aren't set up properly or (in the case of postgres) its being silly and not using indexes when it really should
max
maxOP•2mo ago
@Max thanks, I will try looking into indexes again and use explain analyze for some o fthe logged queries which ive done in the past but even with a LLM explaining me whats going on its often hard to get to a solution. Especially what I think is weird is that it seems to send thos super slkow running queries in batches of like 5/6. Max connections on postgres is at the default 100 but it seems like the entire db is blocked by like 5 requests and only when those are done iot stars to process the others. If i enable logging in PrismaClient i also see those batches being sent one after the other and not immediately on trigger from the client. It feels like Prisma is only letting 5 or paralell queries through and once the response comes in it looks at the next queued ones
Max
Max•2mo ago
It's entirely possible, I'm not on the prisma team, so I can't really say how their engine works in that regards. I am curious to see how things change performance-wise with the engine rewrite into pure typescript, but ya that's definitely an interesting observation
Nurul
Nurul•2mo ago
Im firing 15 queries simultanously and they seem to return in batches of 6 each block being 600ms delayed.
Is this happening in latest prisma version? Would it be possible for you to provide a small reproduction so that I can share it with ORM team? I am not aware of any default limits which would cause the responses to be returned in batches of 6. We are working on bringing the "views" preview feature to GA very soon (in one of the upcoming releases) which I think should be helpful in improving performance. Also, let me know if adding appropriate indexes helped in improving the performance?

Did you find this page helpful?