pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: ldh@laurent-hasson.com <ldh@laurent-hasson.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4
Date: Sat, 21 Aug 2021 11:04:41 -0400
Message-ID: <2092859.1629558281@sss.pgh.pa.us> (raw)
In-Reply-To: <MN2PR15MB2560AE2A878FBBE88DCFF33385C29@MN2PR15MB2560.namprd15.prod.outlook.com>
References: <MN2PR15MB2560D77DFB58BD893E2EAAAD85C29@MN2PR15MB2560.namprd15.prod.outlook.com>
<MN2PR15MB2560AE2A878FBBE88DCFF33385C29@MN2PR15MB2560.namprd15.prod.outlook.com>
"ldh@laurent-hasson.com" <ldh@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
view thread (78+ messages) latest in thread
Message-ID: <2092859.1629558281@sss.pgh.pa.us> Permalink: ../2092859.1629558281@sss.pgh.pa.us/ Also on: postgresql.org/message-id/2092859.1629558281@sss.pgh.pa.us ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-performance@postgresql.org Cc: tgl@sss.pgh.pa.us, ldh@laurent-hasson.com Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 In-Reply-To: <2092859.1629558281@sss.pgh.pa.us> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox