From: Tom Lane <tgl@sss.pgh.pa.us>
To: Federico Travaglini <federico.travaglini@collaboration.aubay.it>
cc: Merlin Moncure <mmoncure@gmail.com>,
        pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: R: 14.1 immutable function,
 bad performance if check number = 'NaN'
In-reply-to: <43e497193ed4de4bfc503e5221b843d3@mail.gmail.com>
References: <a883c3fd5675d6a514d310388f4098de@mail.gmail.com>
 <CAHyXU0ye7LvS0W+MOvV40TVMaw5fF0OrogF2TGq4jHBHvVo+LA@mail.gmail.com>
 <43e497193ed4de4bfc503e5221b843d3@mail.gmail.com>
Comments: In-reply-to Federico Travaglini
 <federico.travaglini@collaboration.aubay.it>
	message dated "Tue, 26 Apr 2022 09:45:40 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <218917.1650982270.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 26 Apr 2022 10:11:10 -0400
Message-ID: <218918.1650982270@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/218918.1650982270%40sss.pgh.pa.us>
Precedence: bulk

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