Re: optimization ideas for frequent, large(ish) updates

On Feb 14, 2004, at 9:02 PM, Marinos J. Yannikos wrote:

> Josh Berkus wrote:
>
>> 800MB for sort mem? Are you sure you typed that correctly? You
>> must be counting on not having a lot of concurrent queries. It sure
>> will speed up index updating, though!
>
> 800MB is correct, yes... There are usually only 10-30 postgres
> processes active (imagine 5-10 people working on the web front-end
> while cron jobs access the db occasionally). Very few queries can use
> such large amounts of memory for sorting, but they do exist.
>

Remember that it is going to allocate 800MB per sort. It is not "you
can allocate up to 800MB, so if you need 1 meg, use one meg". Some
queries may end up having a few sort steps.

In terms of sort mem it is best to set a system default to a nice good
value for most queries. and then in your reporting queries or other
ones set sort_mem for that session (set sort_mem = 800000) then only
that session will use the looney sort_mem

It would be interesting to know if your machine is swapping.
--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/