Re: which work memory parameter is used for what? - Mailing list pgsql-general
On Wed, Nov 9, 2016 at 10:05 AM, Andreas Joseph Krogh <andreas@visena.com> wrote:
På onsdag 09. november 2016 kl. 15:54:13, skrev Adrian Klaver <adrian.klaver@aklaver.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@hogranch.com <mailto:pierce@hogranch.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.
> There is no such thing in PG.
>No implicit index will be create
I 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_, but
an 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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment
pgsql-general by date: