Investigate possibility of returning Pending after each batch inserted into "holding" streams.

### Is your feature request related to a problem or challenge?

In streams like the ExternalSorter stream, and some agg streams, and I'm sure in many others, we only return pending from the await, essentially polling the child stream in a loop until it is consumed, if the child stream comes from a stream which will always yield Ready(such as a parquet reader which already fetched all its data), we will never relinquish control back to the executor.
This may be performant in some cases, but will also be obtrusive to actual async behaviour in the future.

### Describe the solution you'd like

We should investigate the performance impact of polling the child stream, if it returns Pending, return, if it returns Ready(Some()), insert the batch into the sorter, and return Pending, until the child stream returns Ready(None), then we can actually call the sort function, which will actually provide a stream of batches.

This is just a concrete example for SortExec, but I'm sure there are many operators that do the same.
This can prevent thread starvation in a lot of cases, as well as allow more optimal behaviour should we every become fully async.

### Describe alternatives you've considered

_No response_

### Additional context

_No response_

Start by tracing SortExec and its ExternalSorter stream, then compare how aggregation streams poll child streams and how the parquet reader reaches Ready. Measure the performance and scheduling effects of yielding Pending after each inserted batch. Done means the affected operators and trade-offs are identified, with tests or benchmarks covering the proposed behavior.

Written by the indexing model from the issue text.