From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Federico Travaglini <federico(dot)travaglini(at)collaboration(dot)aubay(dot)it> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: R: 14.1 immutable function, bad performance if check number = 'NaN' |
Date: | 2022-04-26 14:11:10 |
Message-ID: | 218918.1650982270@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Federico Travaglini <federico(dot)travaglini(at)collaboration(dot)aubay(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Bossart | 2022-04-26 18:16:29 | Re: Fix primary crash continually with invalid checkpoint after promote |
Previous Message | Merlin Moncure | 2022-04-26 12:42:01 | Re: 14.1 immutable function, bad performance if check number = 'NaN' |