Slow queries on MongoDB using include for relations #15156
Description
Bug description
When you use {include: {relationModel: true}} on prisma with a MongoDB, you can observe several slow queries in the mongo logs.
Prisma generates a aggregate query which cannot use the index on _id. Instead mongo falls back to COLLSCAN. This is of course very slow.
How to reproduce
Prisma seems to do the same on include, like loading 500 documents manually like this
const ids = ["61b86bc0a5eb740008ea98c7", "61b9be7ea5eb740008eb22bc", ....]
(await prisma.production.findMany({where:{id:{in:ids}}})).length
This generates the following in mongo:
"msg":"Slow query","attr":{"type":"command","ns":"nucleus.Production","command":{"aggregate":"Production","pipeline":[{"$match":{"$expr":{"$and":[{"$in":["$_id",[{"$oid":"61b86bc0a5eb740008ea98c7"},{"$oid":"61b9be7ea5eb740008eb22bc"},
...
}]]}]}}}]},"planSummary":"COLLSCAN","cursorid":4032979547306890542,"keysExamined":0,"docsExamined":670117,"numYields":670,"nreturned":101,"queryHash":"75F140EB","planCacheKey":"0C6217C0","reslen":358655,"locks":{"Global":{"acquireCount":{"r":671}},"Mutex":{"acquireCount":{"r":1}}},"readConcern":{"level":"local","provenance":"implicitDefault"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"storage":{},"remote":"172.25.0.2:45590","protocol":"op_msg","durationMillis":2009},"truncated":{"command":{"pipeline":{"0":{"$match":{"$expr":{"$and":{"0":{"$in":{"1":{"282":{"type":"objectId","size":12}}}}}}}}}}},"size":{"command":10555}}
Expected behavior
When writing this command to mongo directly, it uses the index and is very fast:
db.getCollection('Production').find({_id: {$in: ids.map(id => ObjectId(id))}}).explain()
Prisma information
Environment & setup
- Database: MongoDB 5.x
and a collection containing > 1 million documents.
Prisma Version
"@prisma/client": "^4.2.1",
"prisma": "^4.2.1",