Improve MongoDB join performance: move $sort and $limit before $lookup in generated aggregation

[timurvafin]

When using a "join" field type in Payload CMS (for example, referencing "documents" from "persons"), the generated MongoDB aggregation pipeline places $lookup before $sort, $skip, and $limit. This causes a full collection scan (COLLSCAN) in MongoDB, making queries very slow for large collections.

Current Behavior

Payload CMS generates a pipeline like:

[
  {
    $lookup: {
      as: "documents.docs",
      from: "documents",
      localField: "_id",
      foreignField: "persons",
      pipeline: [
        { $sort: { createdAt: NumberInt(-1) } },
        { $limit: NumberInt(11) }
      ]
    }
  },
  {
    $addFields: {
      "documents.docs": {
        $map: {
          as: "doc",
          in: "$$doc._id",
          input: "$documents.docs"
        }
      },
      "documents.hasNextPage": {
        $gt: [
          { $size: "$documents.docs" },
          NumberInt(10)
        ]
      }
    }
  },

  { $sort: { createdAt: 1 } },
  { $skip: NumberInt(0) },
  { $limit: NumberInt(10) },
  
]

Because $lookup occurs first, MongoDB has to scan the entire collection before sorting and limiting, which often results in a COLLSCAN.

Why It Is a Problem:

• The entire "persons" collection is scanned, even if only 10 documents are ultimately needed.
• MongoDB indexes for sorting (e.g., on createdAt) are underutilized, leading to high resource usage and poor performance.
• For large datasets, response times can become prohibitively long.

Expected/Desired Behavior

The pipeline should apply $sort, $skip, and $limit before $lookup. For example:

[
  { $sort: { createdAt: 1 } },
  { $skip: NumberInt(0) },
  { $limit: NumberInt(10) },

  
  {
    $lookup: {
      as: "documents.docs",
      from: "documents",
      localField: "_id",
      foreignField: "persons",
      pipeline: [
        { $sort: { createdAt: NumberInt(-1) } },
        { $limit: NumberInt(11) }
      ]
    }
  },
  {
    $addFields: {
      "documents.docs": {
        $map: {
          as: "doc",
          in: "$$doc._id",
          input: "$documents.docs"
        }
      },
      "documents.hasNextPage": {
        $gt: [
          { $size: "$documents.docs" },
          NumberInt(10)
        ]
      }
    }
  },  
]

In this scenario, only the required 10 "persons" documents are fetched (utilizing an index on createdAt, if present), and then we do $lookup to retrieve related "documents." This avoids a full collection scan and dramatically improves performance.

Steps to Reproduce

1. Create two collections in Payload CMS, e.g. “persons” and “documents”.
2. In “documents”, store references to “persons” in a field (array or single).
3. In “persons”, define a “join” field referencing “documents.”
4. Insert many documents into “persons” (e.g., tens of thousands).
5. Query “persons” with the join field; observe slow response times and a COLLSCAN in the MongoDB query plan.

Proposed Solution

• Change the order of stages in the generated aggregation. Place $sort, $skip, and $limit at the start of the pipeline, then perform $lookup.
• Alternatively, offer an option/flag in Payload to control the order of $lookup vs. $sort/$limit stages in the pipeline.

Additional Context

• Payload CMS Version: 3.11.0
• MongoDB Version: docker mongo:latest

Thank you!

I appreciate your work on Payload CMS. If there is anything else I can provide (logs, a reproducible test repo, etc.), please let me know. I’m also open to creating a PR if that would help!