I did try 2000MB work_mem and 16 multiplier 😊 It seems to plateau at 2GB no
matter what. This is what the explain had:
HashAggregate (cost=1774568.21..1774579.21 rows=200 width=1260) (actual
time=94618.303..1795311.542 rows=722853 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk
Batches: 1 Memory Usage: 1277985kB
Buffers: shared hit=14 read=169854, temp read=15777 written=27588
-> HashAggregate (cost=1360804.75..1374830.63 rows=1402588 width=56)
(actual time=30753.022..45384.558 rows=13852618 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk,
assessmenticcqa_raw.iccqar_ques_code
Batches: 5 Memory Usage: 2400305kB Disk Usage: 126560kB
Buffers: shared read=169851, temp read=15777 written=27588
-> Seq Scan on assessmenticcqa_raw (cost=0.00..1256856.62
rows=13859750 width=38) (actual time=0.110..14342.258 rows=13852618 loops=1)
Filter: ((iccqar_ques_code)::text = ANY ('{"DEBRIDEMENT
DATE","DEBRIDEMENT THIS VISIT","DEBRIDEMENT TYPE","DEPTH (CM)","DEPTH
DESCRIPTION","DOES PATIENT HAVE PAIN ASSOCIATED WITH THIS WOUND?","DRAIN
PRESENT","DRAIN TYPE","EDGE / SURROUNDING TISSUE -
MACERATION",EDGES,EPITHELIALIZATION,"EXUDATE AMOUNT","EXUDATE
TYPE","GRANULATION TISSUE","INDICATE OTHER TYPE OF WOUND CLOSURE","INDICATE
TYPE","INDICATE WOUND CLOSURE","IS THIS A CLOSED SURGICAL WOUND OR SUSPECTED
DEEP TISSUE INJURY?","LENGTH (CM)","MEASUREMENTS TAKEN","NECROTIC TISSUE
AMOUNT","NECROTIC TISSUE TYPE",ODOR,"OTHER COMMENTS REGARDING DEBRIDEMENT
TYPE","OTHER COMMENTS REGARDING DRAIN TYPE","OTHER COMMENTS REGARDING PAIN
INTERVENTIONS","OTHER COMMENTS REGARDING PAIN QUALITY","OTHER COMMENTS
REGARDING REASON MEASUREMENTS NOT TAKEN","PAIN FREQUENCY","PAIN
INTERVENTIONS","PAIN QUALITY","PERIPHERAL TISSUE EDEMA","PERIPHERAL TISSUE
INDURATION","REASON MEASUREMENTS NOT TAKEN","RESPONSE TO PAIN
INTERVENTIONS",SHAPE,"SIGNS AND SYMPTOMS OF INFECTION","SKIN COLOR SURROUNDING
WOUND",STATE,"SURFACE AREA (SQ CM)","TOTAL NECROTIC TISSUE ESCHAR","TOTAL
NECROTIC TISSUE SLOUGH",TUNNELING,"TUNNELING SIZE(CM)/LOCATION - 12 - 3
O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK","TUNNELING
SIZE(CM)/LOCATION - 6 - 9 O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 9 - 12
O''CLOCK",UNDERMINING,"UNDERMINING SIZE(CM)/LOCATION - 12 - 3
O''CLOCK","UNDERMINING SIZE(CM)/LOCATION - 3 - 6 O''CLOCK","UNDERMINING
SIZE(CM)/LOCATION - 6 - 9 O''CLOCK","UNDERMINING SIZE(CM)/LOCATION - 9 - 12
O''CLOCK","WIDTH (CM)","WOUND PAIN LEVEL, WHERE 0 = \"NO PAIN\" AND 10 =
\"WORST POSSIBLE PAIN\""}'::text[]))
Rows Removed by Filter: 171680
Buffers: shared read=169851
Settings: effective_cache_size = '52GB', from_collapse_limit = '24',
hash_mem_multiplier = '16', jit = 'off', jit_above_cost = '2e+08',
jit_inline_above_cost = '5e+08', jit_optimize_above_cost = '5e+08',
join_collapse_limit = '24', max_parallel_workers = '20',
max_parallel_workers_per_gather = '8', random_page_cost = '1.1', temp_buffers =
'4GB', work_mem = ' 2000MB'
Planning:
Buffers: shared hit=186 read=37
Planning Time: 55.709 ms
Execution Time: 1795921.717 ms
-----Original Message-----
From: Peter Geoghegan <p...@bowt.ie>
Sent: Thursday, July 22, 2021 13:05
To: Tom Lane <t...@sss.pgh.pa.us>
Cc: David Rowley <dgrowle...@gmail.com>; l...@laurent-hasson.com; Justin Pryzby
<pry...@telsasoft.com>; pgsql-performa...@postgresql.org
Subject: Re: Big performance slowdown from 11.2 to 13.3
On Thu, Jul 22, 2021 at 9:53 AM Peter Geoghegan <p...@bowt.ie> wrote:
> I suspect David's theory about hash_agg_set_limits()'s ngroup limit is
> correct. It certainly seems like a good starting point.
I also suspect that if Laurent set work_mem and/or hash_mem_multiplier
*extremely* aggressively, then eventually the hash agg would be in-memory. And
without actually using all that much memory.
I'm not suggesting that that is a sensible resolution to Laurent's complaint.
I'm just pointing out that it's probably not fundamentally impossible to make
the hash agg avoid spilling through tuning these GUCs. At least I see no
evidence of that right now.
--
Peter Geoghegan
