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

Good evening, and thanks to your excellent Postgres.



This funcion in used as a column in a select on about 400k records

If I leave the highlighted row it takes 27 seconds, otherwise 14 seconds!
Such behaviour looks not to be reasonable.



By the way, in my case I can remove that line, because the function
behaviour is the same, but I wanted to provide my very little contribution.



Bye

  Federico



*CREATE* *OR* *REPLACE* *FUNCTION*
geo_ants.antsgeo_get_severity_thr(v_measure_value *double* *precision*,
thr_value_1 *double* *precision*, thr_value_2 *double* *precision*,
thr_value_3 *double* *precision*, thr_value_4 *double* *precision*,
thr_value_5 *double* *precision*)

*RETURNS* *text*

*LANGUAGE* *sql*

*immutable*

--IMMUTABLE PARALLEL SAFE

*AS* *$function$*

----------------------------------------------------------------------------------------------------------------------

-- Author: Federico Travaglini

-- Date: 2020

-- Description:

-- Change Hist: please mark changes in code as "yyyy-mm-dd, Author, change
request id in Merant, brief description"

----------------------------------------------------------------------------------------------------------------------

    *select*

        *case*

            *WHEN* v_measure_value= 'NaN' *THEN* '6 Unk'::*text*

            *when* thr_value_1 = thr_value_4 *then* -- colorazione
disabilitata, ad esempio per lat, long...

                '6 none'::*text*

            *when* thr_value_1 > thr_value_4 *then* -- valori critical >
clear

                 -- SIAMO NEL CASO: valori critical > clear ( thr_5 clear
thr_4  warning thr_3 minor thr_2 major thr_1 critical)

                *CASE*

                    *WHEN* v_measure_value >= thr_value_1 *THEN* '5
Critical'::*text* --critical

                    *WHEN* v_measure_value < thr_value_1 *AND*
v_measure_value >= thr_value_2 *THEN* '4 Major'::*text* --major

                    *WHEN* v_measure_value < thr_value_2 *AND*
v_measure_value >= thr_value_3 *THEN* '3 Minor'::*text* --minor

                    *WHEN* v_measure_value < thr_value_3 *AND*
v_measure_value >= thr_value_4 *THEN* '2 Warning'::*text* --warning

                    *WHEN* v_measure_value < thr_value_4 *THEN* '1 Clear'::
*text* --clear

                    *ELSE* '6 Unk'::*text* -- null values

                *end*

            *else*

                 -- SIAMO NEL CASO: valori critical < clear (critical thr_1
maj thr_2  minor thr_3 war thr_4 clear thr_5)

                *CASE*

                    *WHEN* v_measure_value < thr_value_1 *THEN* '5 Critical'
::*text* --critical

                    *WHEN* v_measure_value >= thr_value_1 *AND*
v_measure_value < thr_value_2 *THEN* '4 Major'::*text* --major

                    *WHEN* v_measure_value >= thr_value_2 *AND*
v_measure_value < thr_value_3 *THEN* '3 Minor'::*text* --minor

                    *WHEN* v_measure_value >= thr_value_3 *AND*
v_measure_value < thr_value_4 *THEN* '2 Warning'::*text* --warning

                    *WHEN* v_measure_value >= thr_value_4 *THEN* '1 Clear'::
*text* --clear

                    *ELSE* '6 Unk'::*text* -- null values

                *end*

            *end*::*text*

*$function$*

;



*Federico TRAVAGLINI*

*Project Manager*

<https://www.aubay.it/>

*AUBAY ITALIA*

Via Cesare Giulio Viola 19 (Torre C) - 00197 Roma

*Office :*  (+39) 06 83780225
*Mobile :*  (+39) 339 7521520

<https://www.linkedin.com/company/aubay-italy/>

<https://twitter.com/Aubay_Italia>

<https://www.facebook.com/aubayit/>

<https://www.instagram.com/aubayitalia/>

-- 









This message is confidential and solely for the intended 
address(es). If
you are not the intended recipient of this message, please 
notify the sender
immediately and delete it from your system. Unauthorised 
reproduction,
disclosure, modification and or distribution of this e-mail 
is strictly
prohibited. The contents of this e-mail do not constitute a 
commitment by Aubay
S.p.A., except where expressly provided for in a 
written agreement between you
and Aubay.