Slow queries with payload api, fast with mongoose (x 50 slower?)

[nexflo]

Link to reproduction

No response

Payload Version

3.62

Node Version

18.17.2

Next.js Version

latest

Describe the Bug

We have the following query:
const res = await payload.find({
collection: "testitems",
where: { and: [{ listId: { equals: listId } }, { isUsed: { equals: false } }] },
limit: 1,
});

Via payload api above:
01 find payload api 5734ms
If we use mongoose directly its fast as expected.
02 find mongoose 23ms

Quite critical I believe.

Reproduction Steps

Write same query in mongoose and payload find api

Adapters and Plugins

db-mongodb

[jmikrut]

Hey @nexflo this could be due to many things but it's definitely something to do with your configuration. 5734ms is definitely not something that should happen with Payload.

If you use Mongoose directly, you're not going through any Payload hooks, access control, or relationship population. So there's lots more that Payload has to do vs. Mongoose. If at any point you have a misconfigured hook, or access control, or slow query from a population / similar, things could slow down significantly.

You should start by checking if you have any queries that are filtering on fields that you do not have indexed. For example, listId or isUsed. Do you have those indexed?

How many documents do you have in the collection that you're querying? Does the speed improve if you add depth=0 to your payload.find query?

I will convert this into a discussion so we can continue to help but I don't think this is related to Payload. If you can give us a reproduction and it does turn out to be something to do with Payload I would be happy to re-open the issue though.

[carlcs]

@thijssmudde this was a special case with a very large collection with multiple millions of records, and we did stick to using mongoose directly for our custom endpoints. With more normal size collections this isn’t noticeable that much and we usually stick with just using Payload API.

[AlessioGr]

Try upgrading to the latest v3 stable. We've made some huge performance improvements here

[r1tsuu]

Also, you should use pagination: false (in combination with limit) Local API parameter where you don't need count / pages. It removes needing to do count which can improve perf.

[geminigeek]

Also, you should use pagination: false (in combination with limit) Local API parameter where you don't need count / pages. It removes needing to do count which can improve perf.

why not expose &pagination=false in rest api , will get huge performance for any query where expected output is just one , eg (matching unique slug , limit 1 )

payload/packages/next/src/routes/rest/collections/find.ts

Line 15 in 306b5d2

export const find: CollectionRouteHandler = async ({ collection, req }) => {

[Danil0v3s]

I'm also experiencing something similar with the latest v3 stable, empty database just hopping between collections is slowing everything down

[vignesh-gupta]

Hey @jmikrut, I'm sort of agreeing this.

I'm using version - 3.0.0-beta.70 with nextjs. for me all queries takes quiet some time, not that much but still a bad UX