which work memory parameter is used for what?

On Tue, Nov 8, 2016 at 1:53 PM, Hector Yuen <hector@infer.com> wrote:

Hello,

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?

Thanks

--
-h

*>I believe ANALYZE depends on work_mem and VACUUM on maintenance_work_mem.*
*No, ANALYZE is part of VACUUM. They both use maintenance_work_mem.*

*https://www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY
<https://www.postgresql.org/docs/9.4/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY&gt;*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.