which work memory parameter is used for what?

From: Hector Yuen <hector(at)infer(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: which work memory parameter is used for what?
Date: 2016-11-08 18:53:29
Message-ID: CAKBegfXNN2JSFsgK0BTGtfaBev9r3UeU7rpzRAgNiT1DzBwv5Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Oleg Bartunov 2016-11-08 18:56:45 Re: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists
Previous Message Melvin Davidson 2016-11-08 18:51:13 Re: Must I create an index for child table if master table have one?