Re: R: 14.1 immutable function, bad performance if check number = 'NaN' - Mailing list pgsql-bugs

From Tom Lane
Subject Re: R: 14.1 immutable function, bad performance if check number = 'NaN'
Date
Msg-id 218918.1650982270@sss.pgh.pa.us
Whole thread Raw
In response to R: 14.1 immutable function, bad performance if check number = 'NaN'  (Federico Travaglini <federico.travaglini@collaboration.aubay.it>)
List pgsql-bugs
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



pgsql-bugs by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: 14.1 immutable function, bad performance if check number = 'NaN'
Next
From: Loïc Revest
Date:
Subject: lag() default value ignored for some window partition depending on table records count?