Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8f388f6

Browse files
Increase hash_mem_multiplier default to 2.0.
Double the default setting for hash_mem_multiplier, from 1.0 to 2.0.This setting makes hash-based executor nodes use twice the usualwork_mem limit.The PostgreSQL 15 release notes should have a compatibility note aboutthis change.Author: Peter Geoghegan <pg@bowt.ie>Discussion:https://postgr.es/m/CAH2-Wzndc_ROk6CY-bC6p9O53q974Y0Ey4WX8jcPbuTZYM4Q3A@mail.gmail.com
1 parent74388a1 commit8f388f6

File tree

10 files changed

+52
-9
lines changed

10 files changed

+52
-9
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1849,17 +1849,16 @@ include_dir 'conf.d'
18491849
operations can use. The final limit is determined by
18501850
multiplying <varname>work_mem</varname> by
18511851
<varname>hash_mem_multiplier</varname>. The default value is
1852-
1.0, which makes hash-based operations subject to the same
1853-
simple <varname>work_mem</varname> maximum as sort-based
1854-
operations.
1852+
2.0, which makes hash-based operations use twice the usual
1853+
<varname>work_mem</varname> base amount.
18551854
</para>
18561855
<para>
18571856
Consider increasing <varname>hash_mem_multiplier</varname> in
18581857
environments where spilling by query operations is a regular
18591858
occurrence, especially when simply increasing
18601859
<varname>work_mem</varname> results in memory pressure (memory
18611860
pressure typically takes the form of intermittent out of
1862-
memory errors).Asetting of1.5 or2.0may be effective with
1861+
memory errors).The defaultsetting of 2.0is often effective with
18631862
mixed workloads. Higher settings in the range of 2.0 - 8.0 or
18641863
more may be effective in environments where
18651864
<varname>work_mem</varname> has already been increased to 40MB

‎src/backend/utils/init/globals.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -122,7 +122,7 @@ intIntervalStyle = INTSTYLE_POSTGRES;
122122
boolenableFsync= true;
123123
boolallowSystemTableMods= false;
124124
intwork_mem=4096;
125-
doublehash_mem_multiplier=1.0;
125+
doublehash_mem_multiplier=2.0;
126126
intmaintenance_work_mem=65536;
127127
intmax_parallel_maintenance_workers=2;
128128

‎src/backend/utils/misc/guc.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3762,7 +3762,7 @@ static struct config_real ConfigureNamesReal[] =
37623762
GUC_EXPLAIN
37633763
},
37643764
&hash_mem_multiplier,
3765-
1.0,1.0,1000.0,
3765+
2.0,1.0,1000.0,
37663766
NULL,NULL,NULL
37673767
},
37683768

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -136,7 +136,7 @@
136136
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
137137
# you actively intend to use prepared transactions.
138138
#work_mem = 4MB# min 64kB
139-
#hash_mem_multiplier =1.0# 1-1000.0 multiplier on hash table work_mem
139+
#hash_mem_multiplier =2.0# 1-1000.0 multiplier on hash table work_mem
140140
#maintenance_work_mem = 64MB# min 1MB
141141
#autovacuum_work_mem = -1# min 1MB, or -1 to use maintenance_work_mem
142142
#logical_decoding_work_mem = 64MB# min 64kB

‎src/test/regress/expected/groupingsets.out

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1574,6 +1574,7 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou
15741574

15751575
-- test the knapsack
15761576
set enable_indexscan = false;
1577+
set hash_mem_multiplier = 1.0;
15771578
set work_mem = '64kB';
15781579
explain (costs off)
15791580
select unique1,
@@ -1919,6 +1920,7 @@ select g100, g10, sum(g::numeric), count(*), max(g::text)
19191920
from gs_data_1 group by cube (g1000, g100,g10);
19201921
set enable_sort = true;
19211922
set work_mem to default;
1923+
set hash_mem_multiplier to default;
19221924
-- Compare results
19231925
(select * from gs_hash_1 except select * from gs_group_1)
19241926
union all

‎src/test/regress/expected/join_hash.out

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,7 @@ alter table wide set (parallel_workers = 2);
8686
savepoint settings;
8787
set local max_parallel_workers_per_gather = 0;
8888
set local work_mem = '4MB';
89+
set local hash_mem_multiplier = 1.0;
8990
explain (costs off)
9091
select count(*) from simple r join simple s using (id);
9192
QUERY PLAN
@@ -119,6 +120,7 @@ rollback to settings;
119120
savepoint settings;
120121
set local max_parallel_workers_per_gather = 2;
121122
set local work_mem = '4MB';
123+
set local hash_mem_multiplier = 1.0;
122124
set local enable_parallel_hash = off;
123125
explain (costs off)
124126
select count(*) from simple r join simple s using (id);
@@ -156,6 +158,7 @@ rollback to settings;
156158
savepoint settings;
157159
set local max_parallel_workers_per_gather = 2;
158160
set local work_mem = '4MB';
161+
set local hash_mem_multiplier = 1.0;
159162
set local enable_parallel_hash = on;
160163
explain (costs off)
161164
select count(*) from simple r join simple s using (id);
@@ -196,6 +199,7 @@ rollback to settings;
196199
savepoint settings;
197200
set local max_parallel_workers_per_gather = 0;
198201
set local work_mem = '128kB';
202+
set local hash_mem_multiplier = 1.0;
199203
explain (costs off)
200204
select count(*) from simple r join simple s using (id);
201205
QUERY PLAN
@@ -229,6 +233,7 @@ rollback to settings;
229233
savepoint settings;
230234
set local max_parallel_workers_per_gather = 2;
231235
set local work_mem = '128kB';
236+
set local hash_mem_multiplier = 1.0;
232237
set local enable_parallel_hash = off;
233238
explain (costs off)
234239
select count(*) from simple r join simple s using (id);
@@ -266,6 +271,7 @@ rollback to settings;
266271
savepoint settings;
267272
set local max_parallel_workers_per_gather = 2;
268273
set local work_mem = '192kB';
274+
set local hash_mem_multiplier = 1.0;
269275
set local enable_parallel_hash = on;
270276
explain (costs off)
271277
select count(*) from simple r join simple s using (id);
@@ -307,6 +313,7 @@ rollback to settings;
307313
savepoint settings;
308314
set local max_parallel_workers_per_gather = 0;
309315
set local work_mem = '128kB';
316+
set local hash_mem_multiplier = 1.0;
310317
explain (costs off)
311318
select count(*) FROM simple r JOIN bigger_than_it_looks s USING (id);
312319
QUERY PLAN
@@ -340,6 +347,7 @@ rollback to settings;
340347
savepoint settings;
341348
set local max_parallel_workers_per_gather = 2;
342349
set local work_mem = '128kB';
350+
set local hash_mem_multiplier = 1.0;
343351
set local enable_parallel_hash = off;
344352
explain (costs off)
345353
select count(*) from simple r join bigger_than_it_looks s using (id);
@@ -377,6 +385,7 @@ rollback to settings;
377385
savepoint settings;
378386
set local max_parallel_workers_per_gather = 1;
379387
set local work_mem = '192kB';
388+
set local hash_mem_multiplier = 1.0;
380389
set local enable_parallel_hash = on;
381390
explain (costs off)
382391
select count(*) from simple r join bigger_than_it_looks s using (id);
@@ -419,6 +428,7 @@ rollback to settings;
419428
savepoint settings;
420429
set local max_parallel_workers_per_gather = 0;
421430
set local work_mem = '128kB';
431+
set local hash_mem_multiplier = 1.0;
422432
explain (costs off)
423433
select count(*) from simple r join extremely_skewed s using (id);
424434
QUERY PLAN
@@ -451,6 +461,7 @@ rollback to settings;
451461
savepoint settings;
452462
set local max_parallel_workers_per_gather = 2;
453463
set local work_mem = '128kB';
464+
set local hash_mem_multiplier = 1.0;
454465
set local enable_parallel_hash = off;
455466
explain (costs off)
456467
select count(*) from simple r join extremely_skewed s using (id);
@@ -486,6 +497,7 @@ rollback to settings;
486497
savepoint settings;
487498
set local max_parallel_workers_per_gather = 1;
488499
set local work_mem = '128kB';
500+
set local hash_mem_multiplier = 1.0;
489501
set local enable_parallel_hash = on;
490502
explain (costs off)
491503
select count(*) from simple r join extremely_skewed s using (id);
@@ -523,6 +535,7 @@ rollback to settings;
523535
savepoint settings;
524536
set local max_parallel_workers_per_gather = 2;
525537
set local work_mem = '4MB';
538+
set local hash_mem_multiplier = 1.0;
526539
set local parallel_leader_participation = off;
527540
select * from hash_join_batches(
528541
$$
@@ -551,6 +564,7 @@ set max_parallel_workers_per_gather = 2;
551564
set enable_material = off;
552565
set enable_mergejoin = off;
553566
set work_mem = '64kB';
567+
set hash_mem_multiplier = 1.0;
554568
explain (costs off)
555569
select count(*) from join_foo
556570
left join (select b1.id, b1.t from join_bar b1 join join_bar b2 using (id)) ss
@@ -602,6 +616,7 @@ set max_parallel_workers_per_gather = 2;
602616
set enable_material = off;
603617
set enable_mergejoin = off;
604618
set work_mem = '4MB';
619+
set hash_mem_multiplier = 1.0;
605620
explain (costs off)
606621
select count(*) from join_foo
607622
left join (select b1.id, b1.t from join_bar b1 join join_bar b2 using (id)) ss
@@ -653,6 +668,7 @@ set max_parallel_workers_per_gather = 2;
653668
set enable_material = off;
654669
set enable_mergejoin = off;
655670
set work_mem = '64kB';
671+
set hash_mem_multiplier = 1.0;
656672
explain (costs off)
657673
select count(*) from join_foo
658674
left join (select b1.id, b1.t from join_bar b1 join join_bar b2 using (id)) ss
@@ -704,6 +720,7 @@ set max_parallel_workers_per_gather = 2;
704720
set enable_material = off;
705721
set enable_mergejoin = off;
706722
set work_mem = '4MB';
723+
set hash_mem_multiplier = 1.0;
707724
explain (costs off)
708725
select count(*) from join_foo
709726
left join (select b1.id, b1.t from join_bar b1 join join_bar b2 using (id)) ss
@@ -843,6 +860,7 @@ savepoint settings;
843860
set max_parallel_workers_per_gather = 2;
844861
set enable_parallel_hash = on;
845862
set work_mem = '128kB';
863+
set hash_mem_multiplier = 1.0;
846864
explain (costs off)
847865
select length(max(s.t))
848866
from wide left join (select id, coalesce(t, '') || '' as t from wide) s using (id);

‎src/test/regress/expected/memoize.out

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -90,8 +90,9 @@ WHERE t1.unique1 < 1000;
9090
1000 | 9.5000000000000000
9191
(1 row)
9292

93-
-- Reduce work_mem so that we see some cache evictions
93+
-- Reduce work_memand hash_mem_multiplierso that we see some cache evictions
9494
SET work_mem TO '64kB';
95+
SET hash_mem_multiplier TO 1.0;
9596
SET enable_mergejoin TO off;
9697
-- Ensure we get some evictions. We're unable to validate the hits and misses
9798
-- here as the number of entries that fit in the cache at once will vary
@@ -238,6 +239,7 @@ WHERE unique1 < 3
238239
RESET enable_seqscan;
239240
RESET enable_mergejoin;
240241
RESET work_mem;
242+
RESET hash_mem_multiplier;
241243
RESET enable_bitmapscan;
242244
RESET enable_hashjoin;
243245
-- Test parallel plans with Memoize

‎src/test/regress/sql/groupingsets.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -424,6 +424,7 @@ select array(select row(v.a,s1.*) from (select two,four, count(*) from onek grou
424424
-- test the knapsack
425425

426426
set enable_indexscan= false;
427+
set hash_mem_multiplier=1.0;
427428
set work_mem='64kB';
428429
explain (costs off)
429430
select unique1,
@@ -519,6 +520,7 @@ from gs_data_1 group by cube (g1000, g100,g10);
519520

520521
set enable_sort= true;
521522
set work_mem to default;
523+
set hash_mem_multiplier to default;
522524

523525
-- Compare results
524526

‎src/test/regress/sql/join_hash.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,7 @@ alter table wide set (parallel_workers = 2);
9595
savepoint settings;
9696
set local max_parallel_workers_per_gather=0;
9797
set local work_mem='4MB';
98+
set local hash_mem_multiplier=1.0;
9899
explain (costs off)
99100
selectcount(*)from simple rjoin simple s using (id);
100101
selectcount(*)from simple rjoin simple s using (id);
@@ -109,6 +110,7 @@ rollback to settings;
109110
savepoint settings;
110111
set local max_parallel_workers_per_gather=2;
111112
set local work_mem='4MB';
113+
set local hash_mem_multiplier=1.0;
112114
set local enable_parallel_hash= off;
113115
explain (costs off)
114116
selectcount(*)from simple rjoin simple s using (id);
@@ -124,6 +126,7 @@ rollback to settings;
124126
savepoint settings;
125127
set local max_parallel_workers_per_gather=2;
126128
set local work_mem='4MB';
129+
set local hash_mem_multiplier=1.0;
127130
set local enable_parallel_hash=on;
128131
explain (costs off)
129132
selectcount(*)from simple rjoin simple s using (id);
@@ -143,6 +146,7 @@ rollback to settings;
143146
savepoint settings;
144147
set local max_parallel_workers_per_gather=0;
145148
set local work_mem='128kB';
149+
set local hash_mem_multiplier=1.0;
146150
explain (costs off)
147151
selectcount(*)from simple rjoin simple s using (id);
148152
selectcount(*)from simple rjoin simple s using (id);
@@ -157,6 +161,7 @@ rollback to settings;
157161
savepoint settings;
158162
set local max_parallel_workers_per_gather=2;
159163
set local work_mem='128kB';
164+
set local hash_mem_multiplier=1.0;
160165
set local enable_parallel_hash= off;
161166
explain (costs off)
162167
selectcount(*)from simple rjoin simple s using (id);
@@ -172,6 +177,7 @@ rollback to settings;
172177
savepoint settings;
173178
set local max_parallel_workers_per_gather=2;
174179
set local work_mem='192kB';
180+
set local hash_mem_multiplier=1.0;
175181
set local enable_parallel_hash=on;
176182
explain (costs off)
177183
selectcount(*)from simple rjoin simple s using (id);
@@ -192,6 +198,7 @@ rollback to settings;
192198
savepoint settings;
193199
set local max_parallel_workers_per_gather=0;
194200
set local work_mem='128kB';
201+
set local hash_mem_multiplier=1.0;
195202
explain (costs off)
196203
selectcount(*)FROM simple rJOIN bigger_than_it_looks s USING (id);
197204
selectcount(*)FROM simple rJOIN bigger_than_it_looks s USING (id);
@@ -206,6 +213,7 @@ rollback to settings;
206213
savepoint settings;
207214
set local max_parallel_workers_per_gather=2;
208215
set local work_mem='128kB';
216+
set local hash_mem_multiplier=1.0;
209217
set local enable_parallel_hash= off;
210218
explain (costs off)
211219
selectcount(*)from simple rjoin bigger_than_it_looks s using (id);
@@ -221,6 +229,7 @@ rollback to settings;
221229
savepoint settings;
222230
set local max_parallel_workers_per_gather=1;
223231
set local work_mem='192kB';
232+
set local hash_mem_multiplier=1.0;
224233
set local enable_parallel_hash=on;
225234
explain (costs off)
226235
selectcount(*)from simple rjoin bigger_than_it_looks s using (id);
@@ -242,6 +251,7 @@ rollback to settings;
242251
savepoint settings;
243252
set local max_parallel_workers_per_gather=0;
244253
set local work_mem='128kB';
254+
set local hash_mem_multiplier=1.0;
245255
explain (costs off)
246256
selectcount(*)from simple rjoin extremely_skewed s using (id);
247257
selectcount(*)from simple rjoin extremely_skewed s using (id);
@@ -255,6 +265,7 @@ rollback to settings;
255265
savepoint settings;
256266
set local max_parallel_workers_per_gather=2;
257267
set local work_mem='128kB';
268+
set local hash_mem_multiplier=1.0;
258269
set local enable_parallel_hash= off;
259270
explain (costs off)
260271
selectcount(*)from simple rjoin extremely_skewed s using (id);
@@ -269,6 +280,7 @@ rollback to settings;
269280
savepoint settings;
270281
set local max_parallel_workers_per_gather=1;
271282
set local work_mem='128kB';
283+
set local hash_mem_multiplier=1.0;
272284
set local enable_parallel_hash=on;
273285
explain (costs off)
274286
selectcount(*)from simple rjoin extremely_skewed s using (id);
@@ -285,6 +297,7 @@ rollback to settings;
285297
savepoint settings;
286298
set local max_parallel_workers_per_gather=2;
287299
set local work_mem='4MB';
300+
set local hash_mem_multiplier=1.0;
288301
set local parallel_leader_participation= off;
289302
select*from hash_join_batches(
290303
$$
@@ -311,6 +324,7 @@ set max_parallel_workers_per_gather = 2;
311324
set enable_material= off;
312325
set enable_mergejoin= off;
313326
set work_mem='64kB';
327+
set hash_mem_multiplier=1.0;
314328
explain (costs off)
315329
selectcount(*)from join_foo
316330
left join (selectb1.id,b1.tfrom join_bar b1join join_bar b2 using (id)) ss
@@ -338,6 +352,7 @@ set max_parallel_workers_per_gather = 2;
338352
set enable_material= off;
339353
set enable_mergejoin= off;
340354
set work_mem='4MB';
355+
set hash_mem_multiplier=1.0;
341356
explain (costs off)
342357
selectcount(*)from join_foo
343358
left join (selectb1.id,b1.tfrom join_bar b1join join_bar b2 using (id)) ss
@@ -365,6 +380,7 @@ set max_parallel_workers_per_gather = 2;
365380
set enable_material= off;
366381
set enable_mergejoin= off;
367382
set work_mem='64kB';
383+
set hash_mem_multiplier=1.0;
368384
explain (costs off)
369385
selectcount(*)from join_foo
370386
left join (selectb1.id,b1.tfrom join_bar b1join join_bar b2 using (id)) ss
@@ -392,6 +408,7 @@ set max_parallel_workers_per_gather = 2;
392408
set enable_material= off;
393409
set enable_mergejoin= off;
394410
set work_mem='4MB';
411+
set hash_mem_multiplier=1.0;
395412
explain (costs off)
396413
selectcount(*)from join_foo
397414
left join (selectb1.id,b1.tfrom join_bar b1join join_bar b2 using (id)) ss
@@ -454,6 +471,7 @@ savepoint settings;
454471
set max_parallel_workers_per_gather=2;
455472
set enable_parallel_hash=on;
456473
set work_mem='128kB';
474+
set hash_mem_multiplier=1.0;
457475
explain (costs off)
458476
select length(max(s.t))
459477
from wideleft join (select id, coalesce(t,'')||''as tfrom wide) s using (id);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp