From: Justin Pryzby <pryzby@telsasoft.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: ldh@laurent-hasson.com, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ranier Vilela <ranier.vf@gmail.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 (workarounds)
Date: Sun, 29 Aug 2021 22:16:48 -0500
Message-ID: <20210830031648.GU26465@telsasoft.com> (raw)
In-Reply-To: <CAFj8pRDPew1rg3c-WNxQmUugO0FQRyg3MTy67-qoUtGA=fwZ6Q@mail.gmail.com>
References: <DM6PR15MB2554454557C7612A93F76A9D85C99@DM6PR15MB2554.namprd15.prod.outlook.com>
<1050917.1630180243@sss.pgh.pa.us>
<MN2PR15MB2560A083DA8CD3418BEA1DA085CA9@MN2PR15MB2560.namprd15.prod.outlook.com>
<CAEudQApD2F17Fz7cQNUZbrx7K3P1Y=KFCc4V3UZthPFgp+radQ@mail.gmail.com>
<1124170.1630244105@sss.pgh.pa.us>
<CAEudQAo75GRW3jMsJLXcN1ufXj=BTdkeO5cA_3yqHHJkCWqVag@mail.gmail.com>
<MN2PR15MB25605C2D5D70A6EAF99B5F6085CA9@MN2PR15MB2560.namprd15.prod.outlook.com>
<1177685.1630253948@sss.pgh.pa.us>
<MN2PR15MB2560B122A49C4C06483183D785CB9@MN2PR15MB2560.namprd15.prod.outlook.com>
<CAFj8pRDPew1rg3c-WNxQmUugO0FQRyg3MTy67-qoUtGA=fwZ6Q@mail.gmail.com>
On Mon, Aug 30, 2021 at 04:43:23AM +0200, Pavel Stehule wrote:
> po 30. 8. 2021 v 2:44 odesÃlatel ldh@laurent-hasson.com napsal:
> > At this point, I am not sure how to proceed except to rethink that
> > toFloat() function and many other places where we use exceptions. We get
> > such dirty data that I need a "safe" way to convert a string to float
> > without throwing an exception. BTW, I tried other combinations in case
> > there may have been some weird interactions with the ::REAL conversion
> > operator, but nothing made any change. Could you recommend another approach
> > off the top of your head? I could use regexes for testing etc... Or maybe
> > there is another option like a no-throw conversion that's built in or in
> > some extension that you may know of? Like the "SAFE." Prefix in BigQuery.
>
> CREATE OR REPLACE FUNCTION safe_to_double_precision(t text)
> RETURNS double precision AS $$
> BEGIN
> IF $1 SIMILAR TO '[+-]?([0-9]*[.])?[0-9]+' THEN
> RETURN $1::double precision;
> ELSE
> RETURN NULL;
> END IF;
> END;
> $$ LANGUAGE plpgsql IMMUTABLE STRICT;
This tries to use a regex to determine if something is a "Number" or not.
Which has all the issues enumerated in painful detail by long answers on stack
overflow, and other wiki/blog/forums.
Rather than trying to define Numbers using regex, I'd try to avoid only the
most frequent exceptions and get 90% of the performance back. I don't know
what your data looks like, but you might try things like this:
IF $1 IS NULL THEN RETURN $2
ELSE IF $1 ~ '^$' THEN RETURN $2
ELSE IF $1 ~ '[[:alpha:]]{2}' THEN RETURN $2
ELSE IF $1 !~ '[[:digit:]]' THEN RETURN $2
BEGIN
RETURN $1::float;
EXCEPTION WHEN OTHERS THEN
RETURN $2;
END;
You can check the stackoverflow page for ideas as to what kind of thing to
reject, but it may depend mostly on your data (what is the most common string?
The most common exceptional string?).
I think it's possible that could even be *faster* than the original, since it
avoids the exception block for values which are for sure going to cause an
exception anyway. It might be that using alternation (|) is faster (if less
readable) than using a handful of IF branches.
--
Justin
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: pryzby@telsasoft.com, pavel.stehule@gmail.com, tgl@sss.pgh.pa.us, ranier.vf@gmail.com, andrew@dunslane.net
Subject: Re: Big Performance drop of Exceptions in UDFs between V11.2 and 13.4 (workarounds)
In-Reply-To: <20210830031648.GU26465@telsasoft.com>
* 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