Thread: which work memory parameter is used for what?
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
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
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)