Re: changing sort_mem on the fly?

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:
> Since the planner knows how many rows will
> be going into the sort and how wide they are, ISTM it should be able to
> estimate how much memory will be needed.

... which is different from how much will be available. See cost_sort():

* If the total volume of data to sort is less than work_mem, we will do
* an in-memory sort, which requires no I/O and about t*log2(t) tuple
* comparisons for t tuples.
*
* If the total volume exceeds work_mem, we switch to a tape-style merge
* algorithm. There will still be about t*log2(t) tuple comparisons in
* total, but we will also need to write and read each tuple once per
* merge pass. We expect about ceil(log6(r)) merge passes where r is the
* number of initial runs formed (log6 because tuplesort.c uses six-tape
* merging). Since the average initial run should be about twice work_mem,
* we have
* disk traffic = 2 * relsize * ceil(log6(p / (2*work_mem)))
* cpu = comparison_cost * t * log2(t)

The actual cost of a sort is therefore *highly* sensitive to how much
memory it is allowed to use. Blowing off any ability to estimate this
in advance is not going to improve matters...

regards, tom lane