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

   >  -----Original Message-----
   >  From: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
   >  Sent: Sunday, August 22, 2021 15:29
   >  To: Tom Lane <tgl@sss.pgh.pa.us>
   >  Cc: Justin Pryzby <pryzby@telsasoft.com>; Ranier Vilela
   >  <ranier.vf@gmail.com>; pgsql-performance@postgresql.org
   >  Subject: RE: Big Performance drop of Exceptions in UDFs between V11.2
   >  and 13.4
   >
   >
   >
   >     >  -----Original Message-----
   >     >  From: Tom Lane <tgl@sss.pgh.pa.us>
   >     >  Sent: Sunday, August 22, 2021 15:24
   >     >  To: ldh@laurent-hasson.com
   >     >  Cc: Justin Pryzby <pryzby@telsasoft.com>; Ranier Vilela
   >     >  <ranier.vf@gmail.com>; pgsql-performance@postgresql.org
   >     >  Subject: Re: Big Performance drop of Exceptions in UDFs between
   >  V11.2
   >     >  and 13.4
   >     >
   >     >  "ldh@laurent-hasson.com" <ldh@laurent-hasson.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
inV12. 

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