Re: which work memory parameter is used for what?

På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce <
pierce(at)hogranch(dot)com <mailto:pierce(at)hogranch(dot)com>>:
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).
 
There is no such thing in PG.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>