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 22:34:44
Message-ID: CAKBegfUOH7rBJeTp47p8oVVcT2vyVo7tCJW5uT3ZRq4MoO9haA@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 Karl Czajkowski 2016-11-08 22:41:19 Re: resolution order for foreign key actions?
Previous Message Raymond O'Donnell 2016-11-08 21:16:59 Re: PHP-Shop with PostgreSQL