Postgres Pro
Downloads
Home   >   mailing lists

Thread: which work memory parameter is used for what?

which work memory parameter is used for what?

From
Hector Yuen
Date:
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

Re: which work memory parameter is used for what?

From
John R Pierce
Date:
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

Re: which work memory parameter is used for what?

From
Andreas Joseph Krogh
Date:
På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce <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-config-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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: which work memory parameter is used for what?

From
Adrian Klaver
Date:
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-config-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




>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: which work memory parameter is used for what?

From
Andreas Joseph Krogh
Date:
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-config-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
 
Attachment

Re: which work memory parameter is used for what?

From
"David G. Johnston"
Date:
On Wed, Nov 9, 2016 at 7:54 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/08/2016 06:45 PM, Andreas Joseph Krogh wrote:
> På onsdag 09. november 2016 kl. 03:02:54, skrev John R Pierce
> 
>
>     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).
>
>
> 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


​I suppose that "
ALTER TABLE ADD FOREIGN KEY
​" uses maintenance_work_mem while executing the query necessary to confirm that for every distinct value in the new foreign key there exists a corresponding primary key.  The PK already has an associated index because of an implementation detail enforcing uniqueness.  The foreign key has no need for such an artifact.

David J.

Re: which work memory parameter is used for what?

From
Melvin Davidson
Date:

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-config-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
 

> 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.
Attachment