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