MongoDB: Performance issue with nested take on many-to-one relationship

[ItsArnob]

Bug description

It seems like a nested take (inside a include) query doesn't actually include a $limit operator on the mongodb aggregation pipeline which i believe causes mongodb to return all document from a collection, this results in a really slow query time when the collection has lots of documents. So this is how it goes from my understanding:

1. query is executed
2. mongodb returns all documents matching the filter
3. prisma client takes 3 documents from what mongodb returned

this means a large chunk of data has to be downloaded only to return 3 documents...

for example, the query included in the prisma information section takes around 1:33 minutes on my internet (db on mongodb atlas)
whereas an equivalent query using findUnique and aggregateRaw took around 700ms:

const chat = await prisma.chat.findUnique({
    where: {
        id: CHAT_ID
    }
});

const messages = await prisma.message.aggregateRaw({
    pipeline: [
        { $match: { chatId: CHAT_ID }, },
        { $sort: { _id: 1, }, },
        { $limit: 3 },
        { $project: { _id: 1, chatId: 1, authorId: 1, content: 1, deleted: 1, createdAt: 1, }, },
    ]
});
chat.messages = messages;

How to reproduce

1. git clone https://github.com/itsarnob/prisma-nested-take-repro
2. set db url in the .env file
3. Run yarn seed to seed the database with a chat and 100k messages
4. Run yarn start to see the time differences between nested take and the equivalent query using findUnique & aggregateRaw

Expected behavior

• the query should include $limit operator in the aggregation pipeline so mongodb returns n documents instead of all documents in the collection.
• should be quick to return results.

Prisma information

Schema

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model Chat {

  id            String          @id @default(auto()) @map("_id") @db.ObjectId
  name          String?
  chatType      ChatType
  createdAt     DateTime        @default(now())
  recipients    chatRecipient[]
  messages      Message[]       @relation("messages")
  lastMessageId String?         @unique

  @@map("chats")
}

model Message {
  id        String   @id @default(auto()) @map("_id") @db.ObjectId
  chat      Chat     @relation("messages", fields: [chatId], references: [id])
  chatId    String
  authorId  String
  content   String?
  deleted   Boolean?
  createdAt DateTime @default(now())

  @@map("messages")
}

type chatRecipient {
  userId   String  @db.ObjectId
  nickname String?

}

enum ChatType {
  Direct
  Group
}

Query

const result1 = await prisma.chat.findUnique({
    where: {
        id: CHAT_ID
    },
    include: {
        messages: {
            take: 3
        }
    }
})

aggregation query logs

 prisma:query db.chats.aggregate([ { $match: { $expr: { $and: [ { $and: [ { $eq: [ "$_id", ObjectId("62ac11f944f9818e45e3889e"), ], }, { $or: [ { $ne: [ { $ifNull: [ "$_id", null, ], }, null, ], }, { $eq: [ "$_id", null, ], }, ], }, ], }, ], }, }, }, { $project: { _id: 1, name: 1, chatType: 1, createdAt: 1, recipients.userId: 1, recipients.nickname: 1, lastMessageId: 1, }, }, ])

 prisma:query db.messages.aggregate([ { $match: { $expr: { $and: [ { $in: [ "$chatId", [ "62ac11f944f9818e45e3889e", ], ], }, { $or: [ { $ne: [ { $ifNull: [ "$chatId", null, ], }, null, ], }, { $eq: [ "$chatId", null, ], }, ], }, ], }, }, }, { $sort: { _id: 1, }, }, { $project: { _id: 1, chatId: 1, authorId: 1, content: 1, deleted: 1, createdAt: 1, }, }, ])

Environment & setup

• OS: Pop!_OS
• Database: MongoDB
• Node.js version: v16.15.0

Prisma Version

prisma                  : 3.15.2
@prisma/client          : 3.15.2
Current platform        : debian-openssl-3.0.x
Query Engine (Node-API) : libquery-engine 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/libquery_engine-debian-openssl-3.0.x.so.node)
Migration Engine        : migration-engine-cli 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/migration-engine-debian-openssl-3.0.x)
Introspection Engine    : introspection-core 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/introspection-engine-debian-openssl-3.0.x)
Format Binary           : prisma-fmt 461d6a05159055555eb7dfb337c9fb271cbd4d7e (at node_modules/@prisma/engines/prisma-fmt-debian-openssl-3.0.x)
Default Engines Hash    : 461d6a05159055555eb7dfb337c9fb271cbd4d7e
Studio                  : 0.462.0

[ItsArnob]

This might be related to #10949 with SQL databases.

[laplab]

This performance problem is fixed in Prisma 5.14.0