Re: sort_mem sizing (Non-linear Performance)

Curt Sampson <cjs(at)cynic(dot)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