I have a collection which contains large documents (often 10k or more), but I'm trying to make a query and return a very small subset of this data.

Imagine a collection with documents shaped like this:

{

  _id: ObjectId(<random>),

  "lineItems": [

    { quantity: 10, partId: 12345, unitPrice: 1034, productionDocuments: [], ...},

    ... potentially hundreds of lines here

  ],

  totalPrice: 113470,

  acceptedPartner: "P11780",

  forwardedToPartner: "P1234",

  acceptedDate: Date("2021-08-15"),

}

When I run a query like this:

db.jobs.find(

  { $or: [

      { acceptedPartner: "P11780" },

      { forwardedToPartner: "P11780" },

  ] },

  {

    _id: true,

    totalPrice: true,

    acceptedPartner: true,

    forwardedToPartner: true,

    acceptedDate: true,

  }

).sort({ acceptedDate: -1 }).limit(20)

I get an error message about running out of memory for sort and having to use disk sort. However, this query should not run out of memory if properly projected (I do have separate indexes on both acceptedPartner and forwardedToPartner). The explain plan produced by the planner indicates that the database is selecting using the proper indexes, sorting, and then projecting. The correct query plan would be to filter using the indexes, project, combine, and then sort, which would not run out of memory space.

I have verified that the incorrect query plan is present in both 4.0 and 4.2. I have yet to try 4.4 but I didn't see anything in the 4.4 release notes that would indicate a fix for this issue.