Slow query with many-to-one relationship on MongoDB

[cateyelow]

Problem

Server environment:
OS : Ubuntu 22
Hardware : vCpu 4core, 8GB RAM
Prisma version : ^5.0.0

I have 8million documents on VideoChatting collection.

Following code always call COLLSCAN, so the cpu usage reached 100%. and the solution code has provided

export async function GET(req: Request, { params }: { params: { videoId: string }}) {
    try {
        const url = new URL(req.url);

        const videoId = params.videoId;
        const take = url.searchParams.get("take");
        const page = url.searchParams.get("page");
        

        // Use prisma aggregation to get replies
        const chats = await prisma.videoChatting.findMany({
            where: {
                videoId: videoId as string,
            },
            take: Number((take) || 10),
            skip: Number((take) || 10) * Math.max((Number(page) - 1), 0) ,
            include:{
                replies: {
                    select: {
                        id: true,
                        message: true,
                        author: true,
                        thumbnail: true,
                        timestampSeconds: true,
                        timestampText: true,
                    }
                },
            },
            orderBy: {
                timestampSeconds: "asc",
            },
        });

        if (chats.length == 0) {
            return new Response(JSON.stringify({
                data: [],
                metaData: {
                    lastCursor: null,
                    hasNextPage: false,
                },
            }), { status: 200 });
        }

        const lastChat = chats[chats.length - 1];
        const cursor = lastChat.id;

        const data = {
            data: chats,
            metaData: {
                lastCursor: cursor,
                hasNextPage: true,
            },
        };

        return new Response(JSON.stringify(data), { status: 200 });
    } catch (e: any) {
        console.error(e);
        return new Response(JSON.stringify({
            error: e.message,
        }), { status: 500 });
    }
}

[Logs]

{"t":{"$date":"2023-08-08T11:11:32.263+00:00"},"s":"W",  "c":"COMMAND",  "id":20525,   "ctx":"conn166","msg":"Failed to gather storage statistics for slow operation","attr":{"opId":180698,"error":"lock acquire timeout"}}
{"t":{"$date":"2023-08-08T11:11:32.264+00:00"},"s":"I",  "c":"COMMAND",  "id":51803,   "ctx":"conn166","msg":"Slow query","attr":{"type":"command","ns":"nadoochat.VideoChatting","command":{"aggregate":"VideoChatting","pipeline":[{"$match":{"$expr":{"$and":[{"$in":["$replyChatId",[{"$literal":{"$oid":"64bd4a9527561f44ee95d8af"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b0"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b1"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b2"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b3"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b4"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b5"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b6"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b7"}},{"$literal":{"$oid":"64bd4a9527561f44ee95d8b8"}}]]},{"$ne":["$replyChatId","$$REMOVE"]}]}}},{"$project":{"_id":1,"videoId":1,"author":1,"thumbnail":1,"message":1,"timestampText":1,"timestampUsec":1,"timestampSeconds":1,"isChatonSite":1,"replyChatId":1}}],"cursor":{},"allowDiskUse":true,"$db":"nadoochat","lsid":{"id":{"$uuid":"171f49a2-c7aa-4a59-885a-19769d571c11"}},"$clusterTime":{"clusterTime":{"$timestamp":{"t":1691493082,"i":1}},"signature":{"hash":{"$binary":{"base64":"1Up5iL/Jp+2CyeD4H8ScZtSXtGk=","subType":"0"}},"keyId":7259368416782319622}},"$readPreference":{"mode":"primaryPreferred"},"readConcern":{"afterClusterTime":{"$timestamp":{"t":1691493082,"i":1}}}},"planSummary":"COLLSCAN","numYields":4411,"queryHash":"74EF3DA0","planCacheKey":"74EF3DA0","queryFramework":"classic","ok":0,"errMsg":"PlanExecutor error during aggregation :: caused by :: operation was interrupted because a client disconnected","errName":"ClientDisconnect","errCode":279,"reslen":326,"locks":{"FeatureCompatibilityVersion":{"acquireCount":{"r":4413}},"ReplicationStateTransition":{"acquireCount":{"w":1}},"Global":{"acquireCount":{"r":4413}},"Mutex":{"acquireCount":{"r":1}}},"readConcern":{"afterClusterTime":{"$timestamp":{"t":1691493082,"i":1}},"provenance":"clientSupplied"},"writeConcern":{"w":"majority","wtimeout":0,"provenance":"implicitDefault"},"remote":"64.176.52.99:58286","protocol":"op_msg","durationMillis":3006}}

Suggested solution

Use aggregateRaw. but it maybe critical on your DX

import prisma from "@/lib/prisma";

type ObjectId = {
    $oid: string;
};

type ChatData = {
    _id: { $oid: string };
    author: string;
    message: string;
    timestampText: string;
    timestampSeconds: number;
    thumbnail: string;
    replies: any[];
    id: { $oid: string };
};

type TransformedChatData = Omit<ChatData, '_id' | 'id'> & { id: string };

function transformChatData(inputData: ChatData): TransformedChatData {
    console.log(inputData);
  return {
    ...inputData,
    id: inputData._id.$oid,
    replies: inputData.replies // Recursive transformation for replies
  };
}

type ChatArray = ChatData[];

export async function GET(req: Request, { params }: { params: { videoId: string }}) {
    try {
        const url = new URL(req.url);

        const videoId = params.videoId;
        const take = parseInt(url.searchParams.get("take") || "10");
        const page = parseInt(url.searchParams.get("page") || "1");

        const skip = take * (page - 1);

        // Setup for MongoDB aggregation pipeline
        const aggregatePipeline = [
            { $match: { videoId } },
            { $sort: { timestampSeconds: 1 } },
            {
                $lookup: {
                    from: "VideoChatting",
                    localField: "_id",
                    foreignField: "replyChatId",
                    as: "replies"
                }
            },
            {
                $project: {
                    id: "$_id",
                    message: 1,
                    author: 1,
                    thumbnail: 1,
                    replyChatId: 1,
                    replies: {
                        $map: {
                            input: "$replies",
                            as: "reply",
                            in: {
                                _id: "$$reply._id",
                                id: "$$reply._id",
                                message: "$$reply.message",
                                author: "$$reply.author",
                                thumbnail: "$$reply.thumbnail",
                                timestampSeconds: "$$reply.timestampSeconds",
                                timestampText: "$$reply.timestampText"
                            }
                        }
                    },
                    timestampSeconds: 1,
                    timestampText: 1
                }
            },
            { $skip: skip },
            { $limit: take }
        ];

        // Actual raw aggregation query
        const chats = await prisma.videoChatting.aggregateRaw({
            pipeline: aggregatePipeline,
        }); // Use appropriate Prisma raw query function

        if (chats.length === 0) {
            return new Response(JSON.stringify({
                data: [],
                metaData: {
                    lastCursor: null,
                    hasNextPage: false,
                },
            }), { status: 200 });
        }

        const chatArrayData = chats as unknown as ChatArray;
        const finalChatArrayData = chatArrayData.map((chat:ChatData) => {
            return transformChatData(chat);
        });

        const lastChat = finalChatArrayData[finalChatArrayData.length - 1];
        const cursor = lastChat?.id;

        const data = {
            data: finalChatArrayData,
            metaData: {
                lastCursor: cursor,
                hasNextPage: true, 
            },
        };

        return new Response(JSON.stringify(data), { status: 200 });
    } catch (e: any) {
        console.error(e);
        return new Response(JSON.stringify({
            error: e.message,
        }), { status: 500 });
    }
}

[Jolg42]

Note: this looks like a duplicate of #15156

[laplab]

This performance problem is fixed in Prisma 5.14.0