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

> -Original Message- > From: l...@laurent-hasson.com > Sent: Saturday, August 21, 2021 19:02 > To: Justin Pryzby > Cc: Tom Lane ; pgsql-performa...@postgresql.org > Subject: RE: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > >

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

> -Original Message- > From: Justin Pryzby > Sent: Saturday, August 21, 2021 18:17 > To: l...@laurent-hasson.com > Cc: Tom Lane ; pgsql-performa...@postgresql.org > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > C

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

Could you send SELECT * FROM pg_config() and try to find the CPU model ? I think it's possible the hypervisor is trapping and emulating unhandled CPU instructions. Actually, it would be interesting to see if the performance differs between 11.2 and 11.13. It's possible that EDB compiled 11.13 on

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

> -Original Message- > From: Justin Pryzby > Sent: Saturday, August 21, 2021 15:20 > To: Tom Lane > Cc: l...@laurent-hasson.com; pgsql-performa...@postgresql.org > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > >

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

> -Original Message- > From: Tom Lane > Sent: Saturday, August 21, 2021 14:05 > To: Mladen Gogala > Cc: pgsql-performance@lists.postgresql.org > Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 > and 13.4 > > Mladen Gogala write

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

On Sat, Aug 21, 2021 at 02:19:50PM -0500, Justin Pryzby wrote: > As I recall, you're running postgres under a windows VM - I'm not sure if > that's relevant. I tried under a couple hyperv VMs but could not reproduce the issue (only an ~8x difference "with exceptions"). Which hypervisor are you us

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

I happen to have a stock 13.3 and 11.12 on Ubuntu here so I thought I'd contribute numbers in case it's helpful: v13.3: Time: 4368.413 ms (00:04.368) Time: 837.046 ms v11.12: Time: 5178.595 ms (00:05.179) Time: 1027.857 ms (00:01.028) So I'm also seeing a slight improvement in 13, not a degradat

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

On Sat, Aug 21, 2021 at 02:17:26PM -0400, Tom Lane wrote: > "l...@laurent-hasson.com" writes: > > So you mean that on average, the 4x overhead of exceptions is around what > > you'd expect? > > Doesn't surprise me any, no. Exception recovery has to clean up after > a wide variety of possible er

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

"l...@laurent-hasson.com" writes: > So you mean that on average, the 4x overhead of exceptions is around what > you'd expect? Doesn't surprise me any, no. Exception recovery has to clean up after a wide variety of possible errors, with only minimal assumptions about what the system state had be

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

Mladen Gogala writes: > I know that 14 is a beta version but the performance is significantly > worse than v13 (I assume it's 13.4). Head revision is better than v14 > but still worse than v13.  Can you expand a bit on the difference? [ shrug... ] I don't see any meaningful differences between

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

-Original Message- From: Tom Lane Sent: Saturday, August 21, 2021 11:05 To: l...@laurent-hasson.com Cc: pgsql-performa...@postgresql.org Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 "l...@laurent-hasson.com" writes: > OK... I apologize for the long e

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

I know that 14 is a beta version but the performance is significantly worse than v13 (I assume it's 13.4). Head revision is better than v14 but still worse than v13.  Can you expand a bit on the difference? Where does the difference come from? Are there any differences in the execution plan?  I

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

"l...@laurent-hasson.com" writes: > OK... I apologize for the long email before. Right after I sent it, I thought > of a much simpler use-case to illustrate the issue which doesn't depend on > any special data I have access o and complex pivoting. It's as raw as I can > make it. > I create a ta

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

OK... I apologize for the long email before. Right after I sent it, I thought of a much simpler use-case to illustrate the issue which doesn't depend on any special data I have access o and complex pivoting. It's as raw as I can make it. I create a table with 1M rows and 2 columns. Column "a" is

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

Hello all, I think I have identified a major performance issue between V11.2 and 13.4 with respect to exception handling in UDFs. I have the following simplified query that pivots data and makes use of a UDF to convert data to a specific type, in this case, float: select "iccqa_iccassmt_fk"