High Performance Overhead with relationJoins preview feature in Nested Joins #22596
Description
Bug description
When utilizing relationJoins in nested joins, a significant performance degradation is observed. In comparison:
Without relationJoins: ~5ms
With relationJoins: ~250ms
This performance hit becomes even more pronounced when dealing with a small dataset of fewer than 10 rows per table. Additionally, the impact intensifies with an increased number of nested joins, and it appears that "lateral joins" play a crucial role in the degradation.
How to reproduce
Switching between with and without relationJoins
Expected behavior
No response
Prisma information
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
previewFeatures = ["nativeDistinct", "relationJoins", "views"]
}
model Category {
id Int @id @default(autoincrement())
enabled Boolean @default(true)
categoryDatas CategoryData[]
productsDatas ProductData[]
}
model CategoryData {
id Int @id @default(autoincrement())
createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz()
category Category @relation(fields: [categoryId], references: [id])
categoryId Int @map("category_id")
}
model ProductData {
id Int @id @default(autoincrement())
createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz()
name Translation @relation(fields: [nameId], references: [id])
nameId Int @map("name_id")
image String
product Product @relation(fields: [productId], references: [id])
productId Int @map("product_id")
category Category @relation(fields: [categoryId], references: [id])
categoryId Int @map("category_id")
}
model Product {
id Int @id @default(autoincrement())
enabled Boolean @default(true)
productDatas ProductData[]
}await client.product.findMany({
select: {
productDatas: {
include: {
category: {
include: {
categoryDatas: true
}
}
}
}
}
})Environment & setup
- OS: Ubuntu 22.04.03
- Database: PostgreSQL
- Node.js version: 20.10.0
Prisma Version
prisma : 5.7.1
@prisma/client : 5.7.1
Computed binaryTarget : debian-openssl-3.0.x
Operating System : linux
Architecture : x64
Node.js : v20.10.0
Query Engine (Node-API) : libquery-engine 0ca5ccbcfa6bdc81c003cf549abe4269f59c41e5 (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Schema Engine : schema-engine-cli 0ca5ccbcfa6bdc81c003cf549abe4269f59c41e5 (at node_modules/@prisma/engines/schema-engine-debian-openssl-3.0.x)
Schema Wasm : @prisma/prisma-schema-wasm 5.7.1-1.0ca5ccbcfa6bdc81c003cf549abe4269f59c41e5
Default Engines Hash : 0ca5ccbcfa6bdc81c003cf549abe4269f59c41e5
Studio : 0.495.0
Preview Features : nativeDistinct, postgresqlExtensions, views