MongoDB Connector generates queries which do not take advantage of indices.

[mm-zacharydavison]

Bug description

Overview

We noticed while developing a new feature using Prisma & MongoDB Connector, that simple ID $in queries were taking 450ms+ to query a single ID.

The _id field is of course indexed in MongoDB, so you'd expect the query Prisma generates to be one which will take advantage of indexing, however...

The docs for $expr state:

Indexes are not used for comparisons where the operand is an array or the operand type is undefined.

So here, our $expr takes an array to match $in, and results in a COLLSCAN through our entire collection:

  "planSummary": [
    {
      "COLLSCAN": {}
    }
  ],
  "keysExamined": 0,
  "docsExamined": 44855,
  "cursorExhausted": 1,
  "numYields": 350

If we rewrite our $match query to be much simpler and not use $expr, it will successfully make use of the indices:

{
  "_id": { "$in": [ ObjectId("5ffe656eed39c24704277f66") ] }
}

Questions / Observations

1. We're not managing the Mongo DB in prisma, it's already setup and configured, with the necessary indices. This all works fine in mongoose.
2. I'm assuming it's not necessary to explicitly declare that an @id prisma property is also an @@index.
3. Is there anything we can do to force prisma to not use $expr? Why is the generated query using $expr?

How to reproduce

1. Generate the prisma client using the supplied example and mongodb connector.
2. Run the provided typescript query.
3. Observe the generated mongo query results in a COLLSCAN.

Expected behavior

prisma.user.findMany(
{ where: {
     id: {
       in: ['id1','id2'],
      }, 
})

Should take advantage of _id indices so that query performance is fast.

Prisma information

Prisma Collection

model User {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  email        String    @unique
  deactivate   Boolean   @default(false)
  bounce       Boolean   @default(false)
  notification Json?
  bannedAt     DateTime?
  lineId       String?

  pushToken PushToken[]
  Lead      Lead[]

  @@index([lineId])
  @@map("users") // mongodb collection name.
}

Calling Code

  async searchUsers(
    searchUsersBodyDTO: SearchUsersBodyDTO
  ): Promise<GetUserDTO[]> {
    // Setup conditions
    const conditions: Prisma.UserFindManyArgs = {}
    if (searchUsersBodyDTO.ids?.length > 0) {
      conditions.where = {
        id: {
          in: searchUsersBodyDTO.ids,
        },
      }
    }
    // Execute
    return (await this.prisma.user.findMany(
      conditions
    )) as unknown[] as GetUserDTO[]
  }

Prisma Generated Aggregate Pipeline Stage

{
  "$expr": {
    "$and": [
      { 
        "$in": [
          "$_id",
          {
            "$literal": [
              {
                "$oid": "5ffe656eed39c24704277f66"
              }  
            ]
          }
        ]  
      }
    ]
  }
}

Environment & setup

• prisma version: 4.4.0
• mongo version: 4.2.23

Prisma Version

4.4.0

[mm-zacharydavison]

This is the same as #12793 (comment)

[laplab]

This performance problem is fixed in Prisma 5.14.0