"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> 3) Sort mem is a tricky affair. AFAIU, it is used only when you create
> index or sort results of a query. If do these things seldomly, you can
> set this very low or default.
I think this is bad advice. Sort memory is only consumed when needed,
so there's no advantage in decreasing the setting just because you think
a particular client process isn't going to need to sort. All you will
accomplish is to pessimize your performance if a sort does happen to be
needed.
You do need to set the installation default on the basis of thinking
about what will happen if all backends are trying to sort at once.
But having done that, you should be able to increase the setting in
individual sessions that you know are going to do large sorts.
The default setting (1024K) is, like most of the default settings
in PG, on the small side IMHO.
I don't care for advice that leads to allocating half of physical RAM to
PG's shared buffers, either. This ignores the fact that the kernel's
disk caches are nearly as effective as PG's internal buffers, and much
more flexible (because the kernel can decrease the size of its caches
when there's heavy memory pressure from processes). I'd start with a
few thousand shared buffers and let the kernel consume the bulk of RAM
with its buffering. That approach lets you use a higher sort_mem
setting, too.
regards, tom lane
