Re: Automatically setting work_mem

On Fri, 2006-03-17 at 13:29 +0800, Qingqing Zhou wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> wrote
> >
> Interesting, I understand that shared_work_mem is process-wise,
> allocate-when-use, request-may-or-may-not-get-it (as you have pointed out,
> this may make planner in a hard situation if we are sensitive to work_mem).
> But I still have something unclear. Let's say we have a sort operation need
> 1024 memory. So the DBA may have the following two options:
>
> (1) SET work_mem = 1024; SET shared_work_mem = 0; do sort;
> (2) SET work_mem = 512; SET shared_work_mem = 512; do sort;
>
> So what's the difference between these two strategy?
> (1) Running time: do they use the same amount of memory? Why option 2 is
> better than 1?
> (2) Idle time: after sort done, option 1 will return all 1024 to the OS and
> 2 will still keep 512?

The differences are
(1) no performance difference - all memory would be allocated and
deallocated at same time in either case
(2) shared_work_mem is SUSET rather than USERSET as work_mem is...
(3) The value is set for the whole server rather than by individual
tuning, so it would not make sense to use it as you have shown, even
though you could if you were the superuser

The goal is to do this:

do sort; /* no work_mem settings at all */

with shared_work_mem set once for the whole server in postgresql.conf

Best Regards, Simon Riggs