agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico Travaglini <federico.travaglini@collaboration.aubay.it>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: R: 14.1 immutable function, bad performance if check number = 'NaN'
Date: Tue, 26 Apr 2022 10:11:10 -0400
Message-ID: <218918.1650982270@sss.pgh.pa.us> (raw)
In-Reply-To: <43e497193ed4de4bfc503e5221b843d3@mail.gmail.com>
References: <a883c3fd5675d6a514d310388f4098de@mail.gmail.com>
<CAHyXU0ye7LvS0W+MOvV40TVMaw5fF0OrogF2TGq4jHBHvVo+LA@mail.gmail.com>
<43e497193ed4de4bfc503e5221b843d3@mail.gmail.com>
Federico Travaglini <federico.travaglini@collaboration.aubay.it> writes:
> Here it is what I tested. I’s a code fragment from a bigger procedure. The
> strings in green are passed as parameters, as well as the thresholds
> 1,2,3,4,5. To test just this fragment of code I replaced them with fixed
> values
Is that different from what you do normally?
In this example, the function clearly is getting inlined, which means that
the parameter values are potentially evaluated multiple times:
> antsgeo_get_severity_thr((e.measure_list #> ('{' ||
> 'cluster_comuni_italiani' || ',o}')::*text*[])::*numeric*, 1, 2, 3, 4, 5)
> *AS* severity_1,
expands to
> CASE WHEN
> ((((e.measure_list #>
> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double
> precision >= '4'::double precision) THEN '1 Clear'::text WHEN
> ((((e.measure_list #>
> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double
> precision >= '3'::double precision) THEN '2 Warning'::text WHEN
> ((((e.measure_list #>
> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double
> precision >= '2'::double precision) THEN '3 Minor'::text WHEN
> ((((e.measure_list #>
> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double
> precision >= '1'::double precision) THEN '4 Major'::text WHEN
> ((((e.measure_list #>
> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double
> precision < '1'::double precision) THEN '5 Critical'::text ELSE '6
> Unk'::text END,
That seems pretty inefficient, becase #> isn't the fastest thing
in the world. Maybe the speed differential you're seeing is just
from adding one more evaluation of the #> for the NaN test.
So my advice is to fix things so that #> isn't evaluated multiple
times. There are ways to prevent the inlining from happening but
they're all underdocumented hacks. A more reliable fix would be to
convert the function to plpgsql language.
regards, tom lane
view thread (4+ messages) latest in thread
Message-ID: <218918.1650982270@sss.pgh.pa.us>
Permalink: ../218918.1650982270@sss.pgh.pa.us/
Also on: postgresql.org/message-id/218918.1650982270@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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, federico.travaglini@collaboration.aubay.it, mmoncure@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: R: 14.1 immutable function, bad performance if check number = 'NaN'
In-Reply-To: <218918.1650982270@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox