RE: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4

> -----Original Message-----
> From: ldh(at)laurent-hasson(dot)com <ldh(at)laurent-hasson(dot)com>
> Sent: Sunday, August 22, 2021 15:29
> To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>; Ranier Vilela
> <ranier(dot)vf(at)gmail(dot)com>; pgsql-performance(at)postgresql(dot)org
> Subject: RE: Big Performance drop of Exceptions in UDFs between V11.2
> and 13.4
>
>
>
> > -----Original Message-----
> > From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> > Sent: Sunday, August 22, 2021 15:24
> > To: ldh(at)laurent-hasson(dot)com
> > Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>; Ranier Vilela
> > <ranier(dot)vf(at)gmail(dot)com>; pgsql-performance(at)postgresql(dot)org
> > Subject: Re: Big Performance drop of Exceptions in UDFs between
> V11.2
> > and 13.4
> >
> > "ldh(at)laurent-hasson(dot)com" <ldh(at)laurent-hasson(dot)com> writes:
> > > So, now, in summary:
> >
> > > - I have tried V13.4, V12.3, 11.13, 11.2, 11.1 on several Windows
> VMs
> > and my personal laptop (no VM).
> > > - All V11.x seem to behave uniformly.
> > > - Starting with 12.3, I am experiencing the major slowdown in the
> > "with exceptions" scenario.
> >
> > Interesting. There's no meaningful difference between v11 and v12
> as
> > far as timeout handling goes, so I'm starting to think that that's a red
> > herring.
> >
> > (Although, after having done some web-searching, I do wonder why
> > timer.c is using a manual-reset event. It looks like auto-reset would
> > work just as well with less code, and I found some suggestions that it
> > might perform better.)
> >
> > > So, I was thinking about stuff and a lot of your intuitions seem to
> drive
> > towards an issue with the compiler used to compile the Winx64
> > version... But is it possible that the JIT is getting in there and making
> > things weird? Given that it's a major change in V12 and this is when I
> am
> > starting to see the issue popup, I figured it might be another avenue
> to
> > look into?
> >
> > Hm, is JIT even enabled in your build? If so, does setting jit = 0
> change
> > anything?
> >
> > regards, tom lane
>
> Hello Tom,
>
> I just ran the test with jit=off in the config and restated the server. No
> change on 13.4. I'd think that the query cost as per the planner would be
> too small to kick in the JIT but thought to check anyways. Doesn't seem
> to be the cause.
>
> Thanks.,
> Laurent.
>
>
>
>

Also Tom,

I do have a Linux install of 13.3, and things work beautifully, so this is definitely a Windows thing here that started in V12.

No exceptions
-----------------------------
Aggregate (cost=21370.00..21370.01 rows=1 width=4) (actual time=1796.311..1796.313 rows=1 loops=1)
Buffers: shared hit=6370
-> Seq Scan on sampletest (cost=0.00..16370.00 rows=1000000 width=8) (actual time=0.006..113.720 rows=1000000 loops=1)
Buffers: shared hit=6370
Planning:
Buffers: shared hit=5
Planning Time: 0.121 ms
Execution Time: 1796.346 ms

With Exceptions
------------------------------
Aggregate (cost=14778.40..14778.41 rows=1 width=4) (actual time=6355.051..6355.052 rows=1 loops=1)
Buffers: shared hit=6373
-> Seq Scan on sampletest (cost=0.00..11975.60 rows=560560 width=32) (actual time=0.011..163.499 rows=1000000 loops=1)
Buffers: shared hit=6370
Planning Time: 0.064 ms
Execution Time: 6355.077 ms