Laszlo Nagy <gandalf@shopzeus.com> wrote:
>> In addition to the good advice from Ken, I suggest that you set
>> effective_cache_size (if you haven't already). Add whatever the
>> OS shows as RAM used for cache to the shared_mem setting.
> It was 1GB. Now I changed to 2GB. Although the OS shows 9GB
> inactive memory, we have many concurrent connections to the
> database server. I hope it is okay to use 2GB.
effective_cache_size doesn't cause any RAM to be allocated, it's
just a hint to the costing routines. Higher values tend to favor
index use, while lower values tend to favor sequential scans. I
suppose that if you regularly have many large queries running at the
same moment you might not want to set it to the full amount of cache
space available, but I've usually had good luck setting to the sum
of shared_buffers space and OS cache.
Since it only affects plan choice, not memory allocations, changing
it won't help if good plans are already being chosen.
-Kevin