-----Original Message-----
From: Tom Lane <t...@sss.pgh.pa.us>
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" <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 table with 1M rows and 2 columns. Column "a" is a random string,
> while column "b" is a random integer as a string. Then I use a UDF that
> converts strings to floats and handles an exception if the incoming string is
> not parsable as a float. Then I do a simple select of each column. In the "a"
> case, the UDF throws and catches lots of exceptions. In the "b" case, the
> conversion is clean and exceptions are not thrown.
I tried this script on a few different versions and got these psql-measured
timings for the test queries:
HEAD:
Time: 12234.297 ms (00:12.234)
Time: 3029.643 ms (00:03.030)
v14:
Time: 12519.038 ms (00:12.519)
Time: 3211.315 ms (00:03.211)
v13:
Time: 12132.026 ms (00:12.132)
Time: 3114.582 ms (00:03.115)
v12:
Time: 11787.554 ms (00:11.788)
Time: 3520.875 ms (00:03.521)
v11:
Time: 13066.495 ms (00:13.066)
Time: 3503.790 ms (00:03.504)
v10:
Time: 15890.844 ms (00:15.891)
Time: 4999.843 ms (00:05.000)
(Caveats: these are assert-enabled debug builds, so they're all slower than
production builds, but the overhead should be pretty uniform across branches I
think. Also, I wasn't trying hard to eliminate noise, e.g. I didn't do
multiple runs. So I wouldn't trust these results to be reproducible to better
than 10% or so.)
The overhead of an EXCEPTION block is definitely high, and more so when an
exception actually occurs, but these are known facts and my results are not out
of line with my expectations. Yours are though, so something is drastically
slowing the exception- recovery path in your installation. Do you have any
extensions loaded?
regards, tom lane
------------------------------------------------------------------------------------------------------
So you mean that on average, the 4x overhead of exceptions is around what you'd
expect?
As for results in general, yes, your numbers look pretty uniform across
versions. On my end, comparing V11.2 vs V13.4 shows a much different picture!
I have a few extensions installed: plpgsql, fuzzystrmatch, pg_trgm and
tablefunc. Same on either versions of the db installs I have, and same
extension versions.
V11.2:
extname
|extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
-------------|--------|------------|--------------|----------|---------|------------|
plpgsql | 10| 11|false |1.0 |NULL |NULL
|
fuzzystrmatch| 10| 2200|true |1.1 |NULL |NULL
|
pg_trgm | 10| 2200|true |1.3 |NULL |NULL
|
tablefunc | 10| 2200|true |1.0 |NULL |NULL
|
V13.4
oid |extname
|extowner|extnamespace|extrelocatable|extversion|extconfig|extcondition|
-----|-------------|--------|------------|--------------|----------|---------|------------|
13428|plpgsql | 10| 11|false |1.0 |NULL
|NULL |
16676|fuzzystrmatch| 10| 2200|true |1.1 |NULL
|NULL |
16677|pg_trgm | 10| 2200|true |1.4 |NULL
|NULL |
16678|tablefunc | 10| 2200|true |1.0 |NULL
|NULL |
Thank you,
Laurent.
