Expand PartialSort usage in more queries to reduce memory usage #9153
Description
Is your feature request related to a problem or challenge?
PartialSort proposed in #9125 can also be used on bounded input which will help reducing memory usage for the sorts when the input is already partially sorted (input plan ordering and plan required ordering has a common prefix)
Describe the solution you'd like
We might need to consider incorporating ExternalSorter in PartialSortExec plan as it is used in SortExec. Then it should be completely safe to replace SortExec with PartialSortExec in EnforceSorting without checking for unboundedness of input.
Describe alternatives you've considered
Implementing partial sort logic inside SortExec and making SortExec choose which sorting logic to run might be an option.
It can also be argued that it is easier to understand the intention when PartialSortExec and SortExec are kept as separate plans.