-----Original Message-----
From: Tom Lane <tgl@xxxxxxxxxxxxx>
Sent: Thursday, July 22, 2021 12:42
To: Peter Geoghegan <pg@xxxxxxx>
Cc: David Rowley <dgrowleyml@xxxxxxxxx>; ldh@xxxxxxxxxxxxxxxxxx; Justin Pryzby <pryzby@xxxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Big performance slowdown from 11.2 to 13.3
Peter Geoghegan <pg@xxxxxxx> writes:
> On Thu, Jul 22, 2021 at 9:21 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
>> Yeah, I should have said "2GB plus palloc slop". It doesn't surprise
>> me a bit that we seem to be eating another 20% on top of the nominal
>> limit.
> MAX_KILOBYTES is the max_val for the work_mem GUC itself, and has been
> for many years.
Right. The point here is that before v13, hash aggregation was not subject to the work_mem limit, nor any related limit. If you did an aggregation requiring more than 2GB-plus-slop, it would work just fine as long as your machine had enough RAM. Now, the performance sucks and there is no knob you can turn to fix it. That's unacceptable in my book.
regards, tom lane
-------------------------------------------------------
Hello all,
As a user of PG, we have taken pride in the last few years in tuning the heck out of the system and getting great performance compared to alternatives like SQLServer. The customers we work with typically have data centers and are overwhelmingly Windows shops: we won the battle to deploy a complex operational system on PG vs SQLServer, but Linux vs Windows was still a bridge too far for many. I am surprised that this limitation introduced after V11 hasn't caused issues elsewhere though. Are we doing things that are such out of the normal? Are we early in pushing V13 to full production? 😊 Doing analytics with pivoted tables with hundreds of columns is not uncommon in our world.
As for the three other requests from the team:
Clustering:
==========================
I re-clustered the table on the index that drives the pivot logic but I didn't see any change:
cluster verbose assessmenticcqa_raw using assessmenticcqa_raw_idx_iccqar_assmt_ques;
HashAggregate (cost=1774465.36..1774476.36 rows=200 width=1260) (actual time=80848.591..1763443.586 rows=722853 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk
Batches: 1 Memory Usage: 1277985kB
Buffers: shared hit=369 read=169577, temp read=15780 written=27584
-> HashAggregate (cost=1360748.50..1374772.80 rows=1402430 width=56) (actual time=25475.554..38256.923 rows=13852618 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk, assessmenticcqa_raw.iccqar_ques_code
Batches: 5 Memory Usage: 2400305kB Disk Usage: 126552kB
Buffers: shared hit=352 read=169577, temp read=15780 written=27584
-> Seq Scan on assessmenticcqa_raw (cost=0.00..1256812.09 rows=13858188 width=38) (actual time=0.085..11914.135 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 hit=352 read=169577
Settings: effective_cache_size = '52GB', from_collapse_limit = '24', hash_mem_multiplier = '4', 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 = '1GB'
Planning:
Buffers: shared hit=100 read=1
Planning Time: 1.663 ms
Execution Time: 1763967.567 ms
More debugging on V11:
==========================
LOG: EXECUTOR STATISTICS
DETAIL: ! system usage stats:
! 169.625000 s user, 5.843750 s system, 175.490088 s elapsed
! [494.640625 s user, 19.171875 s system total]
HashAggregate (cost=1764285.18..1764296.18 rows=200 width=1260) (actual time=86323.813..174737.442 rows=723659 loops=1)
Group Key: t.iccqa_iccassmt_fk
Buffers: shared hit=364 read=170293, temp written=83229
CTE t
-> HashAggregate (cost=1343178.39..1356985.17 rows=1380678 width=56) (actual time=22594.053..32519.573 rows=13865785 loops=1)
Group Key: assessmenticcqa_raw.iccqar_iccassmt_fk, assessmenticcqa_raw.iccqar_ques_code
Buffers: shared hit=364 read=170293
-> Seq Scan on assessmenticcqa_raw (cost=0.00..1240682.76 rows=13666084 width=38) (actual time=0.170..10714.598 rows=13865785 loops=1)
Filter: ((iccqar_ques_code)::text = ANY ('{"DEBRIDEMENT DATE","DEBRIDEMENT THIS VISIT","DEBRIDEMENT TYPE","DEPTH (CM)","DEPTH DESCRIPTION","DOE
S 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 SUSPECTE
D DEEP TISSUE INJURY?","LENGTH (CM)","MEASUREMENTS TAKEN","NECROTIC TISSUE AMOUNT","NECROTIC TISSUE TYPE",ODOR,"OTHER COMMENTS REGARDING DEBRIDEMENT TYPE","OTHE
R COMMENTS REGARDING DRAIN TYPE","OTHER COMMENTS REGARDING PAIN INTERVENTIONS","OTHER COMMENTS REGARDING PAIN QUALITY","OTHER COMMENTS REGARDING REASON MEASUREM
ENTS NOT TAKEN","PAIN FREQUENCY","PAIN INTERVENTIONS","PAIN QUALITY","PERIPHERAL TISSUE EDEMA","PERIPHERAL TISSUE INDURATION","REASON MEASUREMENTS NOT TAKEN","R
ESPONSE 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)/LO
CATION - 6 - 9 O''CLOCK","TUNNELING SIZE(CM)/LOCATION - 9 - 12 O''CLOCK",UNDERMINING,"UNDERMINING SIZE(CM)/LOCATION - 12 - 3 O''CLOCK","UNDERMINING SIZE(CM)/LOC
ATION - 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: 172390
Buffers: shared hit=364 read=170293
-> CTE Scan on t (cost=0.00..27613.56 rows=1380678 width=552) (actual time=22594.062..40248.874 rows=13865785 loops=1)
Buffers: shared hit=364 read=170293, temp written=83229
Planning Time: 0.728 ms
Execution Time: 175482.904 ms
(15 rows)
Patch on V13?
==========================
Maybe there can be a patch on V13 and then a longer-term effort afterwards? As it is, I have no way to deploy V13 as this is a hard regression for us.
Thank you,
Laurent.
