Skip to content

Commit 8f388f6

Browse files
petergeogheganpetergeoghegan
committed
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 usual work_mem limit. The PostgreSQL 15 release notes should have a compatibility note about this change. Author: Peter Geoghegan <pg@bowt.ie> Discussion: https://postgr.es/m/CAH2-Wzndc_ROk6CY-bC6p9O53q974Y0Ey4WX8jcPbuTZYM4Q3A@mail.gmail.com
1 parent 74388a1 commit 8f388f6

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). A setting of 1.5 or 2.0 may be effective with
1861+
memory errors). The default setting of 2.0 is 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 @@ int IntervalStyle = INTSTYLE_POSTGRES;
122122
bool enableFsync = true;
123123
bool allowSystemTableMods = false;
124124
int work_mem = 4096;
125-
double hash_mem_multiplier = 1.0;
125+
double hash_mem_multiplier = 2.0;
126126
int maintenance_work_mem = 65536;
127127
int max_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_mem and hash_mem_multiplier so 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
select count(*) from simple r join simple s using (id);
100101
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from simple r join simple s using (id);
148152
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) FROM simple r JOIN bigger_than_it_looks s USING (id);
197204
select count(*) FROM simple r JOIN 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
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from simple r join extremely_skewed s using (id);
247257
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from simple r join 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
select count(*) from join_foo
316330
left join (select b1.id, b1.t from join_bar b1 join 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
select count(*) from join_foo
343358
left join (select b1.id, b1.t from join_bar b1 join 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
select count(*) from join_foo
370386
left join (select b1.id, b1.t from join_bar b1 join 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
select count(*) from join_foo
397414
left join (select b1.id, b1.t from join_bar b1 join 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 wide left join (select id, coalesce(t, '') || '' as t from wide) s using (id);

0 commit comments

Comments
 (0)