Re: Releasing memory during External sorting?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: Releasing memory during External sorting?
Date: 2005-09-23 15:31:28
Message-ID: 4202.1127489488@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> Since we know the predicted size of the sort set prior to starting the
> sort node, could we not use that information to allocate memory
> appropriately? i.e. if sort size is predicted to be more than twice the
> size of work_mem, then just move straight to the external sort algorithm
> and set the work_mem down at the lower limit?

Have you actually read the sort code?

During the run-forming phase it's definitely useful to eat all the
memory you can: that translates directly to longer initial runs and
hence fewer merge passes. During the run-merging phase it's possible
that using less memory would not hurt performance any, but as already
stated, I don't think it will actually end up cutting the backend's
memory footprint --- the sbrk point will be established during the run
forming phase and it's unlikely to move back much until transaction end.

Also, if I recall the development of that code correctly, the reason for
using more than minimum memory during the merge phase is that writing or
reading lots of tuples at once improves sequentiality of access to the
temp files. So I'm not sure that cutting down the memory wouldn't hurt
performance.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bricklen Anderson 2005-09-23 16:35:14 Re: R: Table Partitioning is in 8.1
Previous Message Simon Riggs 2005-09-23 15:06:18 Re: Releasing memory during External sorting?

Browse pgsql-performance by date

  From Date Subject
Next Message Anjan Dave 2005-09-23 16:02:29 slow database, queries accumulating
Previous Message Michael Stone 2005-09-23 15:31:18 Re: How to determine cause of performance problem?