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

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Duplicate
    • Icon: Major - P3 Major - P3
    • None
    • 4.2.16, 4.0.27
    • 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.

    Description

      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.

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved: