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).
work_mem is used by client queries, and can be allocated several times
per query (for things like complex queries involving nested sorts), and
if you hve 100s of connections, could be used a lot of times
simulataneously, so 1GB kind of values are generally NOT a good idea.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
