Re: R: 14.1 immutable function, bad performance if check number = 'NaN'

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