Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 3:31 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Splitting out vacuum_work_mem from maintenance_work_mem is a separate
> issue.  I assume they were combined because the memory used for vacuum
> index scans is similar to creating an index.

Is it similar?  Doesn't maintenace_work_mem just bound the size of the
array of tids to kill there? So you'd expect it to be just a fraction
of the amount of memory used by initial index creation.

> I am not sure if having
> two settings makes something more likely to be set --- I would think the
> opposite.

Well, if a person does not use vacuum_work_mem, then the cost to that
person is low. If they do, the benefits could be immense. At the
Heroku office, I've had people wonder why creating an index took what
seemed like way too long. I told them to increase
maintenance_work_mem, and then the index creation was almost
instantaneous. Now, you can attribute some of that to the I/O of temp
files on EC2's ephemeral storage, and you'd probably have a point, but
that certainly isn't the whole story there.

-- 
Peter Geoghegan