prisma: MongoDB: `upsert()` fails on `update()` with relation when `notablescan: 1`

This (using in) has just caused us an enormous ($$$) bill on mongodb atlas (serverless bills by usage), due to prisma running full table scans and ignoring indexes. I was shocked and disappointed to learn that this is known issue without a workaround. There should be a warning for everyone to not use prisma with mongodb currently.

As written, the way the query above is generated internally by Prisma it is really hard / impossible for an index to be used.

The same might be true for the other one you just posted. Optimally enable query logging and post the prisma:query output for that Prisma Client query as well.

Is performance problem mentioned in Prisma documentation? Because this is a huge factor while choosing an ORM.

any updates on this issue ? seems like indexes are not used either with include parameter.

We’re encountering the same issue with ‘include’ after going to production. We were able to hack around it for now. However, we will likely migrate off of Prisma if a fix doesn’t come sometime soon which stinks since the DX has been amazing outside of the performance issues w/ mongodb.

This is extremely disappointing and a bit deceiving to hide from Prisma Mongo users. the fact a simple where _id in [] won’t use an index is shocking. We will be migrating off Prisma. and to think we were considering accelerate 😞

This (using in) has just caused us an enormous ($$$) bill on mongodb atlas (serverless bills by usage), due to prisma running full table scans and ignoring indexes. I was shocked and disappointed to learn that this is known issue without a workaround. There should be a warning for everyone to not use prisma with mongodb currently.

Yep, if I know before hand that bad performance means not using index and costing me 100x more then I wouldn’t have gone with Prisma. I would have used something like Mongoose instead.

I did some more investigating and the issue seems broader, as a lot of operations seem to cause a collection scan instead of using an index.

Specifically, I have found that using the in operator always causes an aggregation command that fails to use an index. For example

prisma.project.findMany({
  where: {
    id: { in: [uuid()] },
  },
});

causes a collection scan. I have confirmed this by running explain on the aggregate command prisma ends up running (using query logging).

I added a couple million objects to the collection, and running the findMany command takes over 300ms locally vs <5ms when using mongoDB driver

something like Mongoose instead

My recommendation is https://mikro-orm.io/. Haven’t used it with MongoDB, but with PG it was amazing. And of course migrating off MongoDB mid-term in most use-cases.

Is there an ETA on when the performance issues outlined in this issue and https://github.com/prisma/prisma/issues/14871 will be addressed ?

I was hoping for some improvement before I had to go to production, but that is looking less and less likely, so I am planning to replace prisma with mongoose in all our mongo-backed services.

Is there any chance for performance improvements within the next 1-2 months ?

This prevents to query to fields without index. If you enable TableScan, you can shoot your performance into nirvana if you have a large database.

@AndreasKarz the intention isn’t to run with notablescan in production, but to run with it in development to catch potential performance issues in development and add appropriate indexes before moving to production to ensure the fast running of queries.

Simplified schema for further reproduction:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

model User {
  id       String    @id @map("_id") @db.String
  name String
  projects Project[]
}

model Project {
  id        String   @id @map("_id") @db.String
  owner     User     @relation(fields: [ownerId], references: [id])
  ownerId   String   @db.String
}

and script with query logging:

const { PrismaClient } = require('@prisma/client')

const prisma = new PrismaClient({ log: ["query"]})

// A `main` function so that we can use async/await
async function main() {
    let userId = "3"
    await prisma.user.upsert({
        where: { id: userId },
        create: { id: userId, name: "foo" },
        update: { name: "foo2" },
      });
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

@janpio I am able to reproduce locally with mongo v5.0.6 and prisma v3.13.0

Should I enable and try again?