Re: external sort performance

Jon Nelson <jnelson+pgsql@jamponi.net> writes:
> This is on PG 8.4.8 on Linux, 16GB of "real" RAM.
> Most recently, I enabled trace_sort, disabled hash aggregation[1], and
> set a large work_mem (normally very small, in this case I tried
> anything from 8MB to 256MB. I even tried 1GB and 2GB).

FWIW, I think hash aggregation is your best shot at getting reasonable
performance.  Sorting 175GB of data is going to hurt no matter what.

If the grouped table amounts to 5GB, I wouldn't have expected the hash
table to be more than maybe 2-3X that size (although this does depend on
what aggregates you're running...).  Letting the hash aggregation have
all your RAM might be the best answer.

            regards, tom lane