Re: sort_mem sizing (Non-linear Performance)

Curt Sampson <cjs@cynic.net> writes:
> Next I tried to bump the sortmem up to 256 MB, half the physical RAM
> of the machine. I found out the hard way that the back-end doing the
> indexing will grow to something over three times the size of sortmem,
> and proceeded (slowly) to extricate myself from swap hell.

[ scratches head ... ]  The sort code tries very hard to keep track
of how much it's storing.  I wonder why it's so far off?  Perhaps
failing to allow for palloc overhead?  Remind me again of the exact
datatype you were indexing, please.

> But I decided I couldn't spare that much memory for a database doing
> real work, too, and backed down to 32 MB. That got the process down to
> 115 MB (100 MB resident), but oddly enough sped it up even more, shaving
> the sort time by more than 35% from the original.

Looks like the 128Mb case was swapping a lot (note the page faults
count).  That probably explains the difference.

            regards, tom lane