Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-59910

Queries with sort+projection+limit fail unnecessarily because the sort happens before projection

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 4.2.16, 4.0.27
    • Component/s: None
    • None
    • ALL
    • Hide

      I haven't yet created a minimized testcase script, but I hope to have one later today.

      Show
      I haven't yet created a minimized testcase script, but I hope to have one later today.

      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.

            Assignee:
            kelsey.schubert@mongodb.com Kelsey Schubert
            Reporter:
            bsmedberg@xometry.com Benjamin Smedberg
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: