agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: Federico Travaglini <federico.travaglini@aubay.it>
To: pgsql-bugs@lists.postgresql.org
Subject: 14.1 immutable function, bad performance if check number = 'NaN'
Date: Mon, 25 Apr 2022 16:57:41 +0200
Message-ID: <a883c3fd5675d6a514d310388f4098de@mail.gmail.com> (raw)
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.
Attachments:
[image/png] image001.png (13.5K, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/3-image001.png)
download | view image
[image/png] image002.png (263B, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/4-image002.png)
download | view image
[image/png] image003.png (1.6K, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/5-image003.png)
download | view image
[image/png] image004.png (1.5K, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/6-image004.png)
download | view image
[image/png] image005.png (1.5K, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/7-image005.png)
download | view image
[image/png] image006.png (2.6K, ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/8-image006.png)
download | view image
view thread (5+ messages) latest in thread
Message-ID: <a883c3fd5675d6a514d310388f4098de@mail.gmail.com>
Permalink: ../a883c3fd5675d6a514d310388f4098de@mail.gmail.com/
Also on: postgresql.org/message-id/a883c3fd5675d6a514d310388f4098de@mail.gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: federico.travaglini@aubay.it, pgsql-bugs@lists.postgresql.org
Subject: Re: 14.1 immutable function, bad performance if check number = 'NaN'
In-Reply-To: <a883c3fd5675d6a514d310388f4098de@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox