agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feed14.1 immutable function, bad performance if check number = 'NaN'
5+ messages / 4 participants
[nested] [flat]
* 14.1 immutable function, bad performance if check number = 'NaN'
@ 2022-04-25 14:57 Federico Travaglini <federico.travaglini@aubay.it>
0 siblings, 2 replies; 5+ messages in thread
From: Federico Travaglini @ 2022-04-25 14:57 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 14.1 immutable function, bad performance if check number = 'NaN'
@ 2022-04-25 19:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Federico Travaglini <federico.travaglini@aubay.it>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2022-04-25 19:03 UTC (permalink / raw)
To: Federico Travaglini <federico.travaglini@aubay.it>; +Cc: pgsql-bugs@lists.postgresql.org
Federico Travaglini <federico.travaglini@aubay.it> writes:
> 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.
It's not at all clear which line you think is the "highlighted" one.
However, I'm guessing that this SQL function is a candidate for
inlining, so you might try comparing EXPLAIN VERBOSE output for
the query with both forms of the function. Perhaps that will
yield some insight into what's expensive.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 14.1 immutable function, bad performance if check number = 'NaN'
@ 2022-04-25 19:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2022-04-25 19:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Federico Travaglini <federico.travaglini@aubay.it>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, April 25, 2022, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Federico Travaglini <federico.travaglini@aubay.it> writes:
> > 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.
>
> It's not at all clear which line you think is the "highlighted" one.
>
>
Its the comparison of the double input value to the untyped literal ‘NaN’
(the first case test).
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 14.1 immutable function, bad performance if check number = 'NaN'
@ 2022-04-25 19:24 Merlin Moncure <mmoncure@gmail.com>
parent: Federico Travaglini <federico.travaglini@aubay.it>
1 sibling, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2022-04-25 19:24 UTC (permalink / raw)
To: Federico Travaglini <federico.travaglini@aubay.it>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Mon, Apr 25, 2022 at 11:58 AM Federico Travaglini <
federico.travaglini@aubay.it> wrote:
> 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.
>
>
lightly testing this, I got 10million iterations in about two seconds,
about the same after commenting the NaN test. Given that, problem is
probably failure to inline query. Careful examination of explain of
wrapping query should prove that.
merlin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 14.1 immutable function, bad performance if check number = 'NaN'
@ 2022-04-26 12:42 Merlin Moncure <mmoncure@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Merlin Moncure @ 2022-04-26 12:42 UTC (permalink / raw)
To: Federico Travaglini <federico.travaglini@collaboration.aubay.it>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
On Tue, Apr 26, 2022 at 2:45 AM Federico Travaglini
<federico.travaglini@collaboration.aubay.it> wrote:
>
> Good morning, thank you very much for the time you spent for my question.
>
> Buffers: shared hit=365255
>
> -> Seq Scan on geo_ants.file_hist fh (cost=0.00..443.28 rows=311 width=8) (actual time=0.698..1.434 rows=315 loops=1)
>
> Output: fh.file_id, fh.file_name, fh.rtu, fh.port, fh.act_code, fh.file_size, fh.file_tms, fh.loaded_tms, fh.update_tms, fh.status, fh.data_min_tms, fh.data_max_tms, fh.enh_tms, fh.file_type, fh.partial_output_flag, fh.record_count, fh.status_description, fh.act_lenght, fh.act_id, fh.file_act_done, fh.enh_start_tms, fh.agn_code, fh.agn_group_id, fh.ts_sched_id, fh.ts_sched_ver, fh.enh_attempt, fh.act_done_list, fh.data_max_proc_tms, fh.data_max_loaded_tms, fh.error_count, fh.dbg_mode
>
> Filter: ((fh.data_min_tms <= '2022-04-25 00:00:00'::timestamp without time zone) AND (fh.data_max_tms >= '2022-02-28 00:00:00'::timestamp without time zone) AND (fh.agn_group_id = 21))
>
> Rows Removed by Filter: 3358
>
> Buffers: shared hit=379
>
> -> Append (cost=0.43..4609.77 rows=57257 width=1552) (actual time=0.012..9.971 rows=1319 loops=315)
>
> Buffers: shared hit=106416
>
> -> Index Scan using geo_measr_sample_2022_02_act_id_tms_idx on geo_ants.geo_measr_sample_2022_02 e_1 (cost=0.43..14.42 rows=166 width=1362) (actual time=0.003..0.003 rows=0 loops=315)
>
> Output: e_1.tms, e_1.measure_list, e_1.act_id
>
> Index Cond: ((e_1.act_id = fh.act_id) AND (e_1.tms >= '2022-02-28 00:00:00'::timestamp without time zone) AND (e_1.tms <= '2022-04-25 00:00:00'::timestamp without time zone))
>
> Filter: (((e_1.measure_list #>> '{act_edit,s}'::text[]) <> 'excld'::text) OR ((e_1.measure_list #>> '{act_edit,s}'::text[]) IS NULL))
>
> Buffers: shared hit=946
>
> -> Index Scan using geo_measr_sample_2022_03_act_id_tms_idx on geo_ants.geo_measr_sample_2022_03 e_2 (cost=0.56..2333.98 rows=30845 width=1552) (actual time=0.006..7.586 rows=1061 loops=315)
>
> Output: e_2.tms, e_2.measure_list, e_2.act_id
>
> Index Cond: ((e_2.act_id = fh.act_id) AND (e_2.tms >= '2022-02-28 00:00:00'::timestamp without time zone) AND (e_2.tms <= '2022-04-25 00:00:00'::timestamp without time zone))
>
> Filter: (((e_2.measure_list #>> '{act_edit,s}'::text[]) <> 'excld'::text) OR ((e_2.measure_list #>> '{act_edit,s}'::text[]) IS NULL))
>
> Rows Removed by Filter: 3
>
> Buffers: shared hit=75873
>
> -> Index Scan using geo_measr_sample_2022_04_act_id_tms_idx on geo_ants.geo_measr_sample_2022_04 e_3 (cost=0.43..1975.08 rows=26246 width=1557) (actual time=0.005..2.232 rows=258 loops=315)
>
> Output: e_3.tms, e_3.measure_list, e_3.act_id
>
> Index Cond: ((e_3.act_id = fh.act_id) AND (e_3.tms >= '2022-02-28 00:00:00'::timestamp without time zone) AND (e_3.tms <= '2022-04-25 00:00:00'::timestamp without time zone))
>
> Filter: (((e_3.measure_list #>> '{act_edit,s}'::text[]) <> 'excld'::text) OR ((e_3.measure_list #>> '{act_edit,s}'::text[]) IS NULL))
>
> Buffers: shared hit=29597
>
> Query Identifier: -6803725219970975357
>
> Planning:
>
> Buffers: shared hit=933
>
> Planning Time: 2.057 ms
>
> Execution Time: 33677.292 ms
can you paste query plan for 'fast' case, thank you
merlin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-04-26 12:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-04-25 14:57 14.1 immutable function, bad performance if check number = 'NaN' Federico Travaglini <federico.travaglini@aubay.it>
2022-04-25 19:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-04-25 19:06 ` David G. Johnston <david.g.johnston@gmail.com>
2022-04-25 19:24 ` Merlin Moncure <mmoncure@gmail.com>
2022-04-26 12:42 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox