Re: Increasing GROUP BY CHAR columns speed

On Fri, Nov 28, 2008 at 10:58 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
>
> SET work_mem = 2097151 (this is max allowed value) or SET work_mem = 97151
> decreases query time from 12 seconds to 9 seconds.

Setting work_mem so high that it allocates memory that isn't there
WILL slow down your queries, because the OS will then wind up swapping
out one part of the swap to make room for another part. There are
values between 100M and 2G. Run it with increasing work_mem from 100
to 500 or so Meg and see if that helps. Keep an eye on vmstat 1 or
something to see if your machine starts swapping out while running the
query. If it does you've overallocated memory and things will start
to slow down a LOT.