Mongodb Nested Queries Not Using Indexes

[peymil]

Bug description

In my app, querying orders and their subtitle works takes around 22 secs (33908 orders, 25534 work). I reproduced my issue with a small script and schema file.

Also index usage count is not increasing in second query.

I'm currently using a local single node mongoldb replica set for reproducing, also tried in atlas replica set instance.

How to reproduce

import { PrismaClient } from '@prisma/client';

const prismaClient = new PrismaClient({
  datasources: {
    db: { url: 'mongourl' },
  },
  log: ['query', 'info', 'warn', 'error'],
});

console.time('work findmany');

await prismaClient.subtitleWork.findFirst({
  where: {
    orderId: '60b3e0be7a8dc3424dfd1afc',
  },
});

console.timeEnd('work findmany');

console.time('order findunique');

const order = await prismaClient.order.findUniqueOrThrow({
  where: {
    id: '5edf8699f51916565a5eac8a',
  },
  include: {
    subtitleWork: true,
  },
});
console.timeEnd('order findunique');

Expected behavior

Including subtitleWork must use index of orderId.

Prisma information

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

model SubtitleWork {
  id                   String        @id @default(auto()) @map("_id") @db.ObjectId
  createdAt            DateTime      @default(now())
  updatedAt            DateTime      @default(now()) @updatedAt
  ...
  order                Order         @relation(fields: [orderId], references: [id])
  orderId              String        @unique @db.ObjectId
}

model Order {
  id                           String            @id @default(auto()) @map("_id") @db.ObjectId
  createdAt                    DateTime          @default(now())
  updatedAt                    DateTime          @default(now()) @updatedAt
  createdBy                    String            @db.ObjectId
  updatedBy                    String            @db.ObjectId
  ...
  subtitleWork                 SubtitleWork?
}

Environment & setup

• OS: macOS
• Database: MongoDB
• Node.js version: v18.12.1

Prisma Version

 4.10.1

[peymil]

Duplicate of #17396

[laplab]

This performance problem is fixed in Prisma 5.14.0