[Performance] Regression since 3.39.0 on Large Collection 10M+ mongoDb

[geminigeek]

Describe the Bug

hi,

i just upgraded from 3.33 to latest 3.40.0, i found performance regression on large mongodb collection with a relationship, its just not loading in UI, its taking 120+ seconds to show the records sometime not at all.

i tested downgrading payload version i found the issue started occurring since 3.39.0.

issue with a large collection with 38M records , the collection have a single relationship with another collection ,

{
      name: 'categories',
      type: 'relationship',
      relationTo: 'product-categories',
      hasMany: true,
     
    },

i have another large collection with 229M records it loads fine as it doesn't have any relationship, it seems how relationships are pulled has changed.

i am using payload since v1 never had any issue like this ever.

Link to the code that reproduces this issue

kindly suggest how to make a reproduction for this

Reproduction Steps

to reproduce make a collection with about 10M records having a relationship like this (see below) , product-categories collection just have about 3000 records. try to load the collection in backend it will just not load or take a lot of time , compare same with payload 3.38.0 it works perfectly .

kindly suggest how to make a reproduction for this?

relationship field

{
      name: 'categories',
      type: 'relationship',
      relationTo: 'product-categories',
      hasMany: true,
     
   }

Which area(s) are affected? (Select all that apply)

db-mongodb, area: ui

Environment Info

Binaries:
  Node: 22.11.0
  npm: 11.2.0
  Yarn: 1.22.22
  pnpm: 10.6.5
Relevant Packages:
  payload: 3.39.0
  next: 15.3.0
  @payloadcms/db-mongodb: 3.39.0
  @payloadcms/email-nodemailer: 3.39.0
  @payloadcms/graphql: 3.39.0
  @payloadcms/next/utilities: 3.39.0
  @payloadcms/payload-cloud: 3.39.0
  @payloadcms/plugin-seo: 3.39.0
  @payloadcms/richtext-lexical: 3.39.0
  @payloadcms/translations: 3.39.0
  @payloadcms/ui/shared: 3.39.0
  react: 19.0.0
  react-dom: 19.0.0
Operating System:
  Platform: linux
  Arch: x64
  Version: #59-Ubuntu SMP PREEMPT_DYNAMIC Sat Mar 15 17:40:59 UTC 2025
  Available memory (MB): 112478
  Available CPU cores: 12

[github-actions]

Please add a reproduction in order for us to be able to investigate.

Depending on the quality of reproduction steps, this issue may be closed if no reproduction is provided.

Why was this issue marked with the invalid-reproduction label?

To be able to investigate, we need access to a reproduction to identify what triggered the issue. We prefer a link to a public GitHub repository created with create-payload-app@latest -t blank or a forked/branched version of this repository with tests added (more info in the reproduction-guide).

To make sure the issue is resolved as quickly as possible, please make sure that the reproduction is as minimal as possible. This means that you should remove unnecessary code, files, and dependencies that do not contribute to the issue. Ensure your reproduction does not depend on secrets, 3rd party registries, private dependencies, or any other data that cannot be made public. Avoid a reproduction including a whole monorepo (unless relevant to the issue). The easier it is to reproduce the issue, the quicker we can help.

Please test your reproduction against the latest version of Payload to make sure your issue has not already been fixed.

I added a link, why was it still marked?

Ensure the link is pointing to a codebase that is accessible (e.g. not a private repository). "example.com", "n/a", "will add later", etc. are not acceptable links -- we need to see a public codebase. See the above section for accepted links.

Useful Resources

• Reproduction Guide
• Contributing to Payload

[geminigeek]

it seems related to this #12464 or this #12447

[paulpopus]

Hi @geminigeek we need to test this further, can you create a reproduction either in our _community test suite with a fork or make a new blank template with a seed script and exact steps to reproduce.

We want to make sure this isn't also caused by hooks or access control functions

[geminigeek]

hi,

i will make a reproduction, i do not have any hooks in these collections, here is my further investigation

this works fine -> /api/products?limit=30&page=1&sort=-createdAt
this works fine -> /api/products?limit=300&page=1&sort=-createdAt

this do not work takes 2 min /timeout -> /api/products?limit=30&page=1&sort=-id
this do not work either takes 2 min / timeout -> /api/products?limit=30&page=1&sort=-myOtherDateField

ps. myOtherDateField have index

v 3.39.0
GET /api/products?limit=300&page=1&sort=-id 200              - - 203378.311 ms
GET /api/products?limit=300&page=1&sort=-createdAt 200 - - 1597.997 ms

v 3.38.0
GET /api/products?limit=300&page=1&sort=-id 200 - - 4357.361 ms
GET /api/products?limit=300&page=1&sort=-createdAt 200 - - 1598.942 ms

[geminigeek]

closing this, as the issue seems to be hardware related i have 128GB ram, i think i need more ram/disk for huge dataset may be 256GB

[geminigeek]

hi,

i found the issue, its coming since this PR #12447 , i have sent a Pr to fix this , this line is culprit it adds -createdAt , even if user sent a sort order

To test/reproduce the issue
Enable mongoose debug, set a sort condition in a query on any field which is not id or createdAt , the final sort array will always have -createdAt along with your selected sort field , which is wrong and causes performance issue

it might not be visible in small collection , but in a collection above 1M records its killing disk io and performance , making it unusable

payload/packages/db-mongodb/src/queries/buildSortParam.ts

Line 161 in 6c4dfe4

if (!(sort.includes(fallbackSort) || sort.includes(fallbackSort.replace('-', '')))) {

#12888