Re: which work memory parameter is used for what?

On Wed, Nov 9, 2016 at 10:05 AM, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:

> På onsdag 09. november 2016 kl. 15:54:13, skrev Adrian Klaver <
> adrian(dot)klaver(at)aklaver(dot)com>:
>
> On 11/08/2016 06:45 PM, Andreas Joseph Krogh wrote:
> > På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce
> > <pierce(at)hogranch(dot)com <mailto:pierce(at)hogranch(dot)com>>:
> >
> > 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-conf
> ig-resource.html
> >
> >
> > maintenance_work_mem is used by vacuum and create index operations
> > (including implicit index creation such as add foreign key).
> >
> >
> > There is no such thing in PG.
>
> Can you be more specific as;
>
> https://www.postgresql.org/docs/9.5/static/runtime-config-
> resource.html#GUC-MAINTENANCE-WORK-MEM
>
>
> "implicit index creation such as add foreign key"
> No implicit index will be created.
>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>

*> There is no such thing in PG.>No implicit index will be createI believe
he is referring to: >(including implicit index creation such as add foreign
key).To clarify, indexes are NOT implicitly created for foriegn
keys.Indexes ARE created for PRIMARY KEYs when specified in CREATE
TABLE...or ALTER TABLE ADD CONSTRAINT PRIMARY KEY ...I believe the
confusion is caused by the wording "ALTER TABLE ADD FOREIGN KEY".In that
case, maintenance_work_mem is used to create the FK _CONSTRAINT_, butan
associated index is not created implicitly.*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.