[SERVER-15200] Query planner should move projection stage below sort stage when possible Created: 10/Sep/14 Updated: 06/Dec/19 Resolved: 26/Nov/19 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Querying |
| Affects Version/s: | None |
| Fix Version/s: | 4.3.3 |
| Type: | Improvement | Priority: | Major - P3 |
| Reporter: | J Rassi | Assignee: | David Storch |
| Resolution: | Done | Votes: | 6 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||||||
| Backwards Compatibility: | Fully Compatible | ||||||||||||||||
| Sprint: | Query 2019-11-04, Query 2019-11-18, Query 2019-12-02 | ||||||||||||||||
| Participants: | |||||||||||||||||
| Linked BF Score: | 0 | ||||||||||||||||
| Description |
|
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.
The error displays the following stats tree in the log:
|
| Comments |
| Comment by Githook User [ 26/Nov/19 ] |
|
Author: {'email': 'david.storch@mongodb.com', 'name': 'David Storch', 'username': 'dstorch'}Message: When the projection is statically known to reduce the size |
| Comment by eli jones [ 22/Sep/14 ] |
|
I ran into a similar issue. I have a range query that returns 74,000 documents from an index. If I try to sort by "_id" and .hint() it to use the same index, it blows up with the "Overflow sort stage buffered..." error. This is due to the documents being fairly large and the _memUsage value being calculated based on total document size. In the least, it seems like mongod should be able to sort 74k document _ids, and then return the documents in that order... |
| Comment by J Rassi [ 10/Sep/14 ] |
|
One possible fix for this issue is to register a backup plan for these queries, where the backup plan is generated by switching the order of the projection analysis / sort analysis. This would favor the low-CPU plan (sort below projection) by default, and allow fallback to the low-memory plan (projection below sort) if the sort stage exceeds the memory limit. |