which work memory parameter is used for what?

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.
 
-- 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&gt;
<https://www.visena.com&gt;

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&gt;
<https://www.visena.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

På onsdag 09. november 2016 kl. 15:54:13, skrev Adrian Klaver <
adrian.klaver@aklaver.com <mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

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.

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.

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

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