Slow queries on MongoDB using include for relations

[boenni23]

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",

[paulrostorp]

this is a duplicate of #14871 and #12793

[paulrostorp]

The issue stems from the generate prisma query is too complex to use an index. There is a hack instead of "id_in":

prisma.production.findMany({
  where: { OR: ids.map(id => ({ id: id })) }
});

But I have found many other prisma queries to have performance issues, so I have unfortunately had to migrate away from prisma.
Hope this helps

[rjzheng]

I made similar discoveries here: #17142 (comment)

[offchan42]

The issue stems from the generate prisma query is too complex to use an index. There is a hack instead of "id_in":

prisma.production.findMany({
  where: { OR: ids.map(id => ({ id: id })) }
});

But I have found many other prisma queries to have performance issues, so I have unfortunately had to migrate away from prisma. Hope this helps

I am also suffering hard from this. I use serverless instance on Atlas and suddenly I got very expensive bills simply because of unoptimized Prisma queries.
What tool have you migrated to recently? Is it Mongoose?

[laplab]

This performance problem is fixed in Prisma 5.14.0