Re: tuplesort memory usage: grow_memtuples

On 14 October 2012 09:19, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> This is a very useful optimisation, for both the low and the high end.

I suppose it's possible to not think of it as an optimisation at all.
Rather, it could be considered a way of making tuplesort really use
the work_mem allotted to it, or at least use it more effectively, so
that DBAs don't have to oversize work_mem. You're getting the same
behaviour as when you oversized work_mem, except that you don't run
the risk of thrashing due to excessive paging if ever there is a sort
big enough to have that effect.

> The current coding allows full use of memory iff the memory usage is
> an exact power of 2, so this patch will allow an average of 50% and as
> much as 100% gain in memory for sorts. We need to be careful to note
> this as a change on the release notes, since users may well have set
> work_mem to x2 what was actually needed to get it to work - that means
> most users will see a sudden leap in actual memory usage, which could
> lead to swapping in edge cases.

Yes, that's probably true.

> I notice that cost_sort doesn't take into account the space used for
> sorting other than tuple space so apparently requires no changes with
> this patch. ISTM that cost_sort should be less optimistic about memory
> efficiency than it is.

Perhaps. I don't have an intuitive sense of what is and is not worth
modelling in the optimiser, so I can't really comment here.

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services