Re: which work memory parameter is used for what?

On 11/8/2016 2:34 PM, Hector Yuen wrote:
> I am confused on which are the parameters for different queries. I am
> trying to run VACUUM on a big table, and it is easier for me to set
> the work memory for the specific session instead of tuning it in
> postgresql.conf.
>
> I noticed that if I do:
>
> set work_mem='1GB';
>
> it doesn't help VACUUM, I have to do:
>
> set maintenance_work_mem='1GB';
>
> to accelerate the operation. I could notice that by running VACUUM
> VERBOSE and see that the table was scanned less times an the operation
> finished a lot faster.
>
> My question is, for which operations does work_mem matter and for
> which ones does maintenance_work_mem do? I am specially interested in
> operations like ANALYZE and VACUUM, I believe ANALYZE depends on
> work_mem and VACUUM on maintenance_work_mem.
>
> Can you confirm my understanding?
>

https://www.postgresql.org/docs/current/static/runtime-config-resource.html

maintenance_work_mem is used by vacuum and create index operations
(including implicit index creation such as add foreign key).

work_mem is used by client queries, and can be allocated several times
per query (for things like complex queries involving nested sorts), and
if you hve 100s of connections, could be used a lot of times
simulataneously, so 1GB kind of values are generally NOT a good idea.

--
john r pierce, recycling bits in santa cruz