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

Query planner should move projection stage below sort stage when possible

    • Type: Icon: Improvement Improvement
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • 4.3.3
    • Affects Version/s: None
    • Component/s: Querying
    • None
    • Fully Compatible
    • Query 2019-11-04, Query 2019-11-18, Query 2019-12-02
    • 0

      The query planner currently performs sort analysis before projection analysis; consequently, sort stages are placed below projection stages in the query tree. This is often desirable for queries in which the user does not request all results, since projections don't have to be computed for the documents that are not returned.

      However, this is not desirable for collections with large documents, since the sort stage will buffer the entire document instead of the projected document. As a result, the sort stage can have an unnecessarily large memory footprint for these queries, and the queries will fail if the stage memory usage exceeds 32MB.

      Reproduce with the following shell snippet. The snippet attempts to issue a sort+project query which technically only needs to buffer <1kB of results, but instead >32MB of results are buffered and an error is returned.

      > var longString = ''; for (var i = 0; i < 1024*1024; i++) { longString += 'x'; } 0
      0
      > for (var i = 0; i < 50; i++) { db.foo.insert({a: 1, b: 1, longString: longString}) }
      WriteResult({ "nInserted" : 1 })
      > db.foo.ensureIndex({a: 1})
      {
      	"createdCollectionAutomatically" : false,
      	"numIndexesBefore" : 1,
      	"numIndexesAfter" : 2,
      	"ok" : 1
      }
      > db.foo.find({a: 1}, {longString: 0}).sort({b: 1})
      error: {
      	"$err" : "Runner error: Overflow sort stage buffered data usage of 33556640 bytes exceeds internal limit of 33554432 bytes",
      	"code" : 17144
      }
      

      The error displays the following stats tree in the log:

      2014-09-10T13:23:38.922-0400 [conn1] ERROR: Runner error, stats:
      { "type" : "PROJECTION",
        "works" : 35,
        "yields" : 0,
        "unyields" : 0,
        "invalidates" : 0,
        "advanced" : 0,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 0,
        "children" : [
          { "type" : "SORT",
            "works" : 35,
            "yields" : 0,
            "unyields" : 0,
            "invalidates" : 0,
            "advanced" : 0,
            "needTime" : 33,
            "needFetch" : 0,
            "isEOF" : 0,
            "forcedFetches" : 0,
            "memUsage" : 33556640,
            "memLimit" : 33554432,
            "children" : [
              { "type" : "KEEP_MUTATIONS",
                "works" : 33,
                "yields" : 0,
                "unyields" : 0,
                "invalidates" : 0,
                "advanced" : 32,
                "needTime" : 1,
                "needFetch" : 0,
                "isEOF" : 0,
                "children" : [
                  { "type" : "FETCH",
                    "works" : 33,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 32,
                    "needTime" : 1,
                    "needFetch" : 0,
                    "isEOF" : 0,
                    "alreadyHasObj" : 0,
                    "forcedFetches" : 0,
                    "matchTested" : 0,
                    "children" : [
                      { "type" : "IXSCAN",
                        "works" : 33,
                        "yields" : 0,
                        "unyields" : 0,
                        "invalidates" : 0,
                        "advanced" : 32,
                        "needTime" : 1,
                        "needFetch" : 0,
                        "isEOF" : 0,
                        "keyPattern" : "{ a: 1.0 }",
                        "isMultiKey" : 0,
                        "boundsVerbose" : "field #0['a']: [1.0, 1.0]",
                        "yieldMovedCursor" : 0,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0,
                        "matchTested" : 0,
                        "keysExamined" : 33,
                        "children" : [] } ] } ] } ] } ] }
      2014-09-10T13:23:38.923-0400 [conn1] assertion 17144 Runner error: Overflow sort stage buffered data usage of 33556640 bytes exceeds internal limit of 33554432 bytes ns:test2.foo query:{ query: { a: 1.0 }, orderby: { b: 1.0 } }
      

            Assignee:
            david.storch@mongodb.com David Storch
            Reporter:
            rassi J Rassi
            Votes:
            6 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: