MongoDB slow delete with onDelete: SetNull

[boenni23]

Bug description

When having a schema with a 1:n relation where you delete the left side. It takes on big collections (>1.Mio documents) long time to delete the document (>1 minute).

How to reproduce

You generate a bad query to check the depending documents

{"params":"[]","duration":12735,"query":"db.Broadcast.aggregate([ { $match: { $expr: { $and: [ { }, { $and: [ { $in: [ \"$productionId\", [ { $literal: ObjectId(\"5f913feb235d8600075e1797\"), }, ], ], }, { $ne: [ \"$productionId\", \"$$REMOVE\", ], }, ], }, ], }, }, }, { $project: { _id: 1, productionId: 1, }, }, ])"}

There need to be a query which can use the normal index in mongodb like
db.Broadcast.find({productionId: ObjectId(\"5f913feb235d8600075e1797\")})

Expected behavior

No response

Prisma information

model Broadcast {
  id String @id @default(auto()) @map("_id") @db.ObjectId

  productionId String?     @db.ObjectId
  production   Production? @relation("ProductionBroadcasts", fields: [productionId], references: [id], onDelete: SetNull, onUpdate: Cascade)
}

model Production {
  id               String                 @id @default(auto()) @map("_id") @db.ObjectId
}

prisma.production.delete({where: {id: "...."}})

Environment & setup

Node: 19.7.0
Mongo: 6.0.5

Prisma Version

PrismaClient: 4.14.0

[boenni23]

Probably related to #16916, #17142 and #15156

In all of these tickets you find unoptimized queries on mongo which cannot use the index.

If you query a relation you should rewrite the query.

[laplab]

This performance problem is fixed in Prisma 5.14.0