Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
yufangOPUS opened this issue Jun 25, 2023 · 4 comments
Labels
bug/1-unconfirmed Bug should have enough information for reproduction, but confirmation has not happened yet. domain/schema Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc. kind/bug A reported bug. topic: in topic: indexes topic: mongodb topic: performance/queries
Milestone

Comments

@yufangOPUS
Copy link

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
@yufangOPUS yufangOPUS added the kind/bug A reported bug. label Jun 25, 2023
@jkomyno jkomyno added domain/schema Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc. topic: mongodb topic: performance/queries labels Jun 26, 2023
@jkomyno jkomyno changed the title MongoDB Query with 'in' condition will cause COLLSCAN MongoDB Query with 'in' condition will cause COLLSCAN, without leveraging indexes Jun 26, 2023
@jkomyno
Copy link
Contributor

jkomyno commented Jun 26, 2023

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.

@Jolg42 Jolg42 added bug/1-unconfirmed Bug should have enough information for reproduction, but confirmation has not happened yet. topic: indexes labels Jun 26, 2023
@yufangOPUS
Copy link
Author

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
Copy link
Contributor

janpio commented Jul 4, 2023

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

@laplab
Copy link
Contributor

laplab commented May 13, 2024

This performance problem is fixed in Prisma 5.14.0

@laplab laplab closed this as completed May 13, 2024
@laplab laplab added this to the 5.14.0 milestone May 13, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug/1-unconfirmed Bug should have enough information for reproduction, but confirmation has not happened yet. domain/schema Issue in the "Schema" domain: Prisma Schema, Introspection, Migrations etc. kind/bug A reported bug. topic: in topic: indexes topic: mongodb topic: performance/queries
Projects
None yet
Development

No branches or pull requests

5 participants