MongoDB: Simple findFirst() query for primary key is slower than expected

[janpio]

Here it is very clear that the prism creates a bad query for a query that is actually simple
i hope this can fixing

// from prisma query
const message = await prisma.message.findFirst({
    where: {
        id: data.messageId
    },
})

// I changed the match object in the query from Prisma with a simple match, the results were fast
// 41ms execute from Navicate
db.messages.aggregate([
    {
        "$match": {
            "_id": ObjectId("65c0aa61d2b6db513c66be4e")
        }
    },
    {
        "$project": {
            "_id": 1,
            "deviceId": 1,
            "priority": 1,
            "status": 1,
            "payload": 1,
            "maxRetry": 1,
            "expiredSendAt": 1,
            "updatedAt": 1,
            "createdAt": 1,
            "type": 1,
            "callbackUrl": 1,
            "callbackType": 1,
            "proccessId": 1,
            "delay.min": 1,
            "delay.max": 1
        }
    }
])



// The query results from Prisma are taken from the Mongo Compass performance log
// 429ms execute from Navicate
db.messages.aggregate([
    {
        "$match": {
            "$expr": {
                "$and": [
                    {
                        "$in": [
                            "$_id",
                            [
                                {
                                    "$literal": "65c0aa61d2b6db513c66be4e"
                                }
                            ]
                        ]
                    },
                    {
                        "$ne": [
                            "$_id",
                            "$$REMOVE"
                        ]
                    }
                ]
            }
        }
    },
    {
        "$project": {
            "_id": 1,
            "deviceId": 1,
            "priority": 1,
            "status": 1,
            "payload": 1,
            "maxRetry": 1,
            "expiredSendAt": 1,
            "updatedAt": 1,
            "createdAt": 1,
            "type": 1,
            "callbackUrl": 1,
            "callbackType": 1,
            "proccessId": 1,
            "delay.min": 1,
            "delay.max": 1
        }
    }
])

Originally posted by @Davidaprilio in #16916 (comment)

[macrozone]

@janpio are you sure this is a findFirst call?

this looks more like this issue #22812

i am pretty confident that findFirst does work properly and does not do a $in query, since I analyize this to death for my app. I also suspected findFirst to sometimes lead to collscan #23775 but in my case it was a "hidden" findUnique that i overlooked because it was caused by a library that i used. Maybe this user has a similar problem

[janpio]

are you sure this is a findFirst call?

Yes, I am pretty sure that is what was reported:

// from prisma query
const message = await prisma.message.findFirst({
    where: {
        id: data.messageId
    },
})

If what was reported was wrong, we will figure this out when trying to reproduce and fixing. We have to go from what our users tell us. If it turns out to be incorrect, great, one less issue and this can be closed.

@Davidaprilio Can you double check what I quote above from your comment message?

[Davidaprilio]

I just tried it again, and sorry it turns out it really does use $eq

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

const prisma = new PrismaClient({
    errorFormat: 'pretty',
    log: ['query', 'info', 'warn', 'error'],
})

async function run() {
    const res = await prisma.message.findFirst({
        where: {
            id: '65c75988a71554b2fcd0c48d'
        },
    })
}
run()

and in console

(base) david@dell-office:~/Projects/***$ ts-node test/query.ts 
prisma:query db.messages.aggregate([ { $match: { $expr: { $and: [ { $eq: [ "$_id", { $literal: ObjectId("65c75988a71554b2fcd0c48d"), }, ], }, { $ne: [ "$_id", "$$REMOVE", ], }, ], }, }, }, { $limit: 1, }, { $project: { _id: 1, delay.min: 1, delay.max: 1, deviceId: 1, priority: 1, status: 1, payload: 1, maxRetry: 1, expiredSendAt: 1, updatedAt: 1, createdAt: 1, type: 1, callbackUrl: 1, callbackType: 1, proccessId: 1, testing: 1, }, }, ])
(base) david@dell-office:~/Projects/***$ 

Maybe I used to take the query log wrong, but the query was really very heavy.
but I still feel findFirst is heavier than a regular query
when I changed findFirst to aggregateRaw it was much lighter in the performance log, and I also replaced findFirst with the relation Messages: true to aggregateRaw and it was also lighter in terms of the performance log
I looked at the performance log in Compass

This is from the query above

Simple query

[janpio]

Ok, does that mean that the original message was a mistake? If so, I think it would be good to close this issue to make sure that is properly tracked.

I am not sure I am following with the further performance problem you describe. Do you think it is worth creating a new issue, with more information so we can fully understand and reproduce this? We would be very happy to do that. Thanks!

[Davidaprilio]

Yes, you can close this issue

and if you want to create a new issue about performance to improve I would be happy, because actually the problem from the start was performance and I was wrong in the query $in

[janpio]

Sure, go ahead! Thanks.