MongoDB Query with 'in' condition will cause COLLSCAN, without leveraging indexes

[yufangOPUS]

Bug description

how to reproduce this issue:
const result = await this.prisma.ANY_COLLECTION.updateMany({ where: { id: { in: idsToUpdate, }, }, })

And it will cause a COLLSCAN, even the field already has index. It's getting really bad performance on our production database and no other way to fix this.
I have seen a lot of similar issue for several months just like #14871 and #12793
Why is this fatal bug still not fixed after months? If prisma is abandoning support for mongodb, please let us know. We'll migrate to other ORM as soon as we can.

How to reproduce

Expected behavior

No response

Prisma information

// Add your schema.prisma

// Add your code using Prisma Client

Environment & setup

• OS: MacOS & Ubuntu 2004
• Database: Mongodb 6.0.6
• Node.js version: 18.15.0
• Prisma version: 4.15.0

Prisma Version

4.15.0

[jkomyno]

Hi @yufangOPUS, thank you for your report. Could you please show us an example of a mongodb collection with indexes that don't seem to be picked up by Prisma? Thank you.

[yufangOPUS]

Hi @yufangOPUS, thank you for your report. Could you please show us an example of a mongodb collection with indexes that don't seem to be picked up by Prisma? Thank you.

Hi @jkomyno , just a simple findMany with in condition will cause this issue. Please try it out , more details in #12793 (comment)

[janpio]

Is this a duplicate of #12793 then? Is something about your issue here different?

[laplab]

This performance problem is fixed in Prisma 5.14.0