Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1njGlC-0002Bi-8c for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Apr 2022 08:41:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1njGl9-00081Y-Qp for pgsql-bugs@arkaria.postgresql.org; Tue, 26 Apr 2022 08:41:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1njFtN-000685-6f for pgsql-bugs@lists.postgresql.org; Tue, 26 Apr 2022 07:45:49 +0000 Received: from mail-ed1-x536.google.com ([2a00:1450:4864:20::536]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1njFtJ-0000o5-GG for pgsql-bugs@lists.postgresql.org; Tue, 26 Apr 2022 07:45:48 +0000 Received: by mail-ed1-x536.google.com with SMTP id d6so16153394ede.8 for ; Tue, 26 Apr 2022 00:45:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=collaboration.aubay.it; s=collaboration; h=from:references:in-reply-to:mime-version:thread-index:date :message-id:subject:to:cc; bh=mt7AF+iNOUu6fsan0l0eaknhmg4YovVRW3T9DEzTKu0=; b=FzkivAb07IpA3pT8FggKcwwifcYRWG1gMO4O6n9wFtogvrt2dIIQObKgP93UbmY6g3 pgyKrm9BMve0NBaFcR/8BbUT2wLWieh8iJuv+iNDmlWZi72c+yzKOXj7dJO5uEQ0aW0o iB51DbtxiDQhQjeS4yKSapQDJYJVSQTDJWOr6gI1k8jp234GVsF7zgH1uFWxOf2V+G6l hqJO87QFltMXR06T8+0YMOVA/YJii1u5b2iNd8BhIZCwRak56EuIHbavG9g9aHiZ3Cwa wQk3KxEIc8Dnrn66+3RwXSPvNET6qMsOOnB+TVFriv7yHbg8FgrXSR2PoPmBeWOcnQSv x1bQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:from:references:in-reply-to:mime-version :thread-index:date:message-id:subject:to:cc; bh=mt7AF+iNOUu6fsan0l0eaknhmg4YovVRW3T9DEzTKu0=; b=TEu0GCBRk5Ih3nx1gSSRAU3JG9EYdtp+qYGNNZZEgwL/N299BJc8FTR7Xqslre2sMz kd1vrFQOH3Us583n4TtjKrvxTzpM2O1nwRcZyt/4zZxpOyYQIfAWneanEA1SVwLJ7jXl gRP25vKYuj5m5OUrai5UHV+iu7PYGoi5L3Zbf8rVTMXQPyK5xi730gYLoDXI0rmBvEvW dxFhoqPaXqZEZMwO/8bMDVZgjEPFpH79gXhQ0HYD5ZNXQK8henaex7PPSG+i7eEkKcMQ g3cQDJCrmV4Cyo+9ozaXv+//7Lh7IUjrVHa4Thaz+esnUmlKpS26ACdkGnI554FvOyet 5Ruw== X-Gm-Message-State: AOAM533cOXXJ16B6bcyrngtlREdv+iR1CErC/Ud2w8If55Q3OKi3eral os3HTAjfoRYuV/8ra5pCELr+l0s3QJp63/0chLh79AVFo0H/QhMdSIqjKB9hbOulV78gvfBPN5D gl+AOXkvU5awcq/vzVsqwl++v99LF4FJjQlxTzFKx8A== X-Google-Smtp-Source: ABdhPJzSwKoT16GS82P+E0/nLeEvZu4ycRggxML0Qs+DbjdlqlDmtQlR4fydJcMy1jGYNHtTbbW8HAr5QenBV1E80x8= X-Received: by 2002:a05:6402:27d1:b0:425:f92f:aac0 with SMTP id c17-20020a05640227d100b00425f92faac0mr2760371ede.409.1650959144182; Tue, 26 Apr 2022 00:45:44 -0700 (PDT) From: Federico Travaglini References: In-Reply-To: MIME-Version: 1.0 X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQG0ZqePEeKHdRJwtzuogQ5nAow6hAI0o6ZSrTfx7tA= Date: Tue, 26 Apr 2022 09:45:40 +0200 Message-ID: <43e497193ed4de4bfc503e5221b843d3@mail.gmail.com> Subject: R: 14.1 immutable function, bad performance if check number = 'NaN' To: Merlin Moncure Cc: pgsql-bugs Content-Type: multipart/alternative; boundary="00000000000053f41405dd89e1cc" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000053f41405dd89e1cc Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Good morning, thank you very much for the time you spent for my question. Yes inlining could be the problem, because maybe does not allow to use the IMMUTABLE feature? The context of the query is quite complex, therefore I avoided to provide it in previous email Here it is what I tested. I=E2=80=99s a code fragment from a bigger procedu= re. 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 *SET* random_page_cost =3D 0.1; (otherwise it takes more than 4 minutes in place of 33 sec) *EXPLAIN* (*ANALYZE*, BUFFERS, *verbose*) *select* * *from* ( *select* tms, fh.file_id, (e.measure_list #>> ('{' || 'cluster_comuni_italiani' || ',s}')::*text*[]) *as* value_s_1, (e.measure_list #> ('{' || 'cluster_comuni_italiani' || ',n}')::*text*[])::*numeric* *as* value_n_1, (e.measure_list #> ('{' || 'cluster_comuni_italiani' || ',o}')::*text*[])::*numeric* *as* value_o_1, antsgeo_get_severity_thr((e.measure_list #> ('{' || 'cluster_comuni_italiani' || ',o}')::*text*[])::*numeric*, 1, 2, 3, 4, 5) *AS* severity_1, (e.measure_list #>> ('{' || 'act_geoposition_pers_act_confidence' || ',s}')::*text*[]) *as* value_s_2= , (e.measure_list #> ('{' || 'act_geoposition_pers_act_confidence' || ',n}')::*text*[])::*numeric* *as= * value_n_2, (e.measure_list #> ('{' || 'act_geoposition_pers_act_confidence' || ',o}')::*text*[])::*numeric* *as= * value_o_2, antsgeo_get_severity_thr((e.measure_list #> ('{' || 'act_geoposition_pers_act_confidence' || ',o}')::*text*[])::*numeric*, 1, = 2, 3, 4, 5) *AS* severity_2, (e.measure_list #>> ('{' || 'act_coverage_band_pcell' || ',s}')::*text*[]) *as* value_s_3, (e.measure_list #> ('{' || 'act_coverage_band_pcell' || ',n}')::*text*[])::*numeric* *as* value_n_3, (e.measure_list #> ('{' || 'act_coverage_band_pcell' || ',o}')::*text*[])::*numeric* *as* value_o_3, antsgeo_get_severity_thr((e.measure_list #> ('{' || 'act_coverage_band_pcell' || ',o}')::*text*[])::*numeric*, 1, 2, 3, 4, 5) *AS* severity_3, (e.measure_list #>> ('{' || *null*::*text* || ',s}')::*text*[]) *as* value_s_4, (e.measure_list #> ('{' || *null*::*text* || ',n}')::*text* [])::*numeric* *as* value_n_4, (e.measure_list #> ('{' || *null*::*text* || ',o}')::*text* [])::*numeric* *as* value_o_4, antsgeo_get_severity_thr((e.measure_list #> ('{' || *null*:= : *text* || ',o}')::*text*[])::*numeric*, 1, 2, 3, 4, 5) *AS* severity_4 *from* file_hist fh, geo_measr_sample e *where* ( (fh.agn_group_id =3D 21) *and* fh.data_min_tms <=3D '2022-04-25 00:00:00' *and* fh.data_max_tms >=3D '2022-02-28 00:00:00' --lo usa ) *and* fh.act_id =3D e.act_id *and* (e.tms >=3D '2022-02-28 00:00:00' *and* e.tms <=3D '2022-04-25 00:00:00') *and* (e.measure_list #>> ('{act_edit,s}')::*text*[] *not* *in* ('excld') *or* e.measure_list #>> ('{act_edit,s}')::*text*[] *is* *null*) )t1 e.measure_list is a jsonb, with a variable structure { "act_plmn": { "s": "222/1" }, "struct_day": { "s": "2022-04-22" }, "struct_week": { "s": "2022-04-18" }, "act_plmn_name": { "s": "Tim.Ita (222-01)" }, "struct_act_id": { "s": "1809464" }, "struct_tc_name": { "s": "VoiceCall_MO" }, "struct_yyyy_mm": { "s": "2022-04" }, "act_coverage_ci": { "s": "63" }, "act_coverage_ta": { "n": 4, "o": 4 }, "act_environment": { "s": "in-door" }, "cell_code_pcell": { "s": "FE23E3" }, "struct_act_code": { "s": "20220422_164238_SDTU100010.01" }, "struct_act_name": { "s": "20220422_164238_SDTU100010.01. Copy of Voice MO 0687201815" },=E2=80=A6 Nested Loop (cost=3D0.43..2055500.00 rows=3D1441783 width=3D524) (actual time=3D0.761..33647.744 rows=3D415401 loops=3D1) Output: e.tms, fh.file_id, (e.measure_list #>> ('{cluster_comuni_italiani,s}'::cstring)::text[]), ((e.measure_list #> ('{cluster_comuni_italiani,n}'::cstring)::text[]))::numeric, ((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric, CASE WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double precision >=3D '4'::double precision) THEN '1 Clear'::text WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double precision >=3D '3'::double precision) THEN '2 Warning'::text WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double precision >=3D '2'::double precision) THEN '3 Minor'::text WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double precision >=3D '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, (e.measure_list #>> ('{act_geoposition_pers_act_confidence,s}'::cstring)::text[]), ((e.measure_list #> ('{act_geoposition_pers_act_confidence,n}'::cstring)::text[]))::numeric, ((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric, CASE WHEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric)::d= ouble precision >=3D '4'::double precision) THEN '1 Clear'::text WHEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric)::d= ouble precision >=3D '3'::double precision) THEN '2 Warning'::text WHEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric)::d= ouble precision >=3D '2'::double precision) THEN '3 Minor'::text WHEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric)::d= ouble precision >=3D '1'::double precision) THEN '4 Major'::text WHEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric)::d= ouble precision < '1'::double precision) THEN '5 Critical'::text ELSE '6 Unk'::text END, (e.measure_list #>> ('{act_coverage_band_pcell,s}'::cstring)::text[]), ((e.measure_list #> ('{act_coverage_band_pcell,n}'::cstring)::text[]))::numeric, ((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric, CASE WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::double precision >=3D '4'::double precision) THEN '1 Clear'::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::double precision >=3D '3'::double precision) THEN '2 Warning'::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::double precision >=3D '2'::double precision) THEN '3 Minor'::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::double precision >=3D '1'::double precision) THEN '4 Major'::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::double precision < '1'::double precision) THEN '5 Critical'::text ELSE '6 Unk'::text END, NULL::text, NULL::numeric, NULL::numeric, '6 Unk'::text Buffers: shared hit=3D365255 -> Seq Scan on geo_ants.file_hist fh (cost=3D0.00..443.28 rows=3D311 width=3D8) (actual time=3D0.698..1.434 rows=3D315 loops=3D1) 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 <=3D '2022-04-25 00:00:00'::timestamp without time zone) AND (fh.data_max_tms >=3D '2022-02-28 00:00:00'::timesta= mp without time zone) AND (fh.agn_group_id =3D 21)) Rows Removed by Filter: 3358 Buffers: shared hit=3D379 -> Append (cost=3D0.43..4609.77 rows=3D57257 width=3D1552) (actual time=3D0.012..9.971 rows=3D1319 loops=3D315) Buffers: shared hit=3D106416 -> Index Scan using geo_measr_sample_2022_02_act_id_tms_idx on geo_ants.geo_measr_sample_2022_02 e_1 (cost=3D0.43..14.42 rows=3D166 width=3D1362) (actual time=3D0.003..0.003 rows=3D0 loops=3D315) Output: e_1.tms, e_1.measure_list, e_1.act_id Index Cond: ((e_1.act_id =3D fh.act_id) AND (e_1.tms >=3D '2022-02-28 00:00:00'::timestamp without time zone) AND (e_1.tms <=3D '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=3D946 -> Index Scan using geo_measr_sample_2022_03_act_id_tms_idx on geo_ants.geo_measr_sample_2022_03 e_2 (cost=3D0.56..2333.98 rows=3D30845 width=3D1552) (actual time=3D0.006..7.586 rows=3D1061 loops=3D315) Output: e_2.tms, e_2.measure_list, e_2.act_id Index Cond: ((e_2.act_id =3D fh.act_id) AND (e_2.tms >=3D '2022-02-28 00:00:00'::timestamp without time zone) AND (e_2.tms <=3D '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=3D75873 -> Index Scan using geo_measr_sample_2022_04_act_id_tms_idx on geo_ants.geo_measr_sample_2022_04 e_3 (cost=3D0.43..1975.08 rows=3D26246 width=3D1557) (actual time=3D0.005..2.232 rows=3D258 loops=3D315) Output: e_3.tms, e_3.measure_list, e_3.act_id Index Cond: ((e_3.act_id =3D fh.act_id) AND (e_3.tms >=3D '2022-02-28 00:00:00'::timestamp without time zone) AND (e_3.tms <=3D '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=3D29597 Query Identifier: -6803725219970975357 Planning: Buffers: shared hit=3D933 Planning Time: 2.057 ms Execution Time: 33677.292 ms *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* *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" ---------------------------------------------------------------------------= ------------------------------------------- -- 20220426 non so perch=C3=A8 ma in questa versione non =C3=A8 efifciente *select* *case* --WHEN v_measure_value=3D 'NaN' THEN '6 Unk'::text non scommentare o le performance per qualche motivo iragionevole degradano di molto. *when* thr_value_1 =3D 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 >=3D thr_value_1 *THEN* '5 Critical'::*text* --critical *WHEN* v_measure_value < thr_value_1 *AND* v_measure_value >=3D thr_value_2 *THEN* '4 Major'::*text* --major *WHEN* v_measure_value < thr_value_2 *AND* v_measure_value >=3D thr_value_3 *THEN* '3 Minor'::*text* --minor *WHEN* v_measure_value < thr_value_3 *AND* v_measure_value >=3D 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 >=3D thr_value_1 *AND* v_measure_value < thr_value_2 *THEN* '4 Major'::*text* --major *WHEN* v_measure_value >=3D thr_value_2 *AND* v_measure_value < thr_value_3 *THEN* '3 Minor'::*text* --minor *WHEN* v_measure_value >=3D thr_value_3 *AND* v_measure_value < thr_value_4 *THEN* '2 Warning'::*text* --warning *WHEN* v_measure_value >=3D thr_value_4 *THEN* '1 Clear= ':: *text* --clear *ELSE* '6 Unk'::*text* -- null values *end* *end*::*text* *$function$* ; By the way, if I call the overall function where it is this code fragment, I get much better performance (22 sec in place of 41) re-writing function case without nesting sub-cases, unfortunately I=E2=80=99m not so cleaver to= get the query plan for a query executed inside a function *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* *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=3D 'NaN' THEN '6 Unk'::text this must be commented, it is not a problem because the semantic does not change (same case of the ELSE), but I don=E2=80=99t understand why it changes performanc= e. *when* thr_value_1 =3D thr_value_4 *then* '6 Unk'::*text* -- colorazione disabilitata, ad esempio per lat, long... -- SIAMO NEL CASO: valori critical > clear ( thr_5 clear thr_4 warning thr_3 minor thr_2 major thr_1 critical) *WHEN* thr_value_1 > thr_value_4 *and* v_measure_value < thr_value_4 *THEN* '1 Clear'::*text* --clear *WHEN* thr_value_1 > thr_value_4 *and* v_measure_value < thr_value_3 *THEN* '2 Warning'::*text* --warning *WHEN* thr_value_1 > thr_value_4 *and* v_measure_value < thr_value_2 *THEN* '3 Minor'::*text* --minor *WHEN* thr_value_1 > thr_value_4 *and* v_measure_value < thr_value_1 *THEN* '4 Major'::*text* --major *WHEN* thr_value_1 > thr_value_4 *and* v_measure_value >=3D thr_value_1 *THEN* '5 Critical'::*text* --major -- SIAMO NEL CASO: valori critical < clear (critical thr_1 maj thr_2 minor thr_3 war thr_4 clear thr_5) *WHEN* thr_value_1 < thr_value_4 *and* v_measure_value >=3D thr_value_4 *THEN* '1 Clear'::*text* --clear *WHEN* thr_value_1 < thr_value_4 *and* v_measure_value >=3D thr_value_3 *THEN* '2 Warning'::*text* --warning *WHEN* thr_value_1 < thr_value_4 *and* v_measure_value >=3D thr_value_2 *THEN* '3 Minor'::*text* --minor *WHEN* thr_value_1 < thr_value_4 *and* v_measure_value >=3D thr_value_1 *THEN* '4 Major'::*text* --major *WHEN* thr_value_1 < thr_value_4 *and* v_measure_value < thr_value_1 *THEN* '5 Critical'::*text* --critical *ELSE* '6 Unk'::*text* -- null values *end*::*text* *$function$* ; *Da:* Merlin Moncure *Inviato:* luned=C3=AC 25 aprile 2022 21:24 *A:* Federico Travaglini *Cc:* pgsql-bugs *Oggetto:* Re: 14.1 immutable function, bad performance if check number =3D 'NaN' 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 --=20 This message is confidential and solely for the intended=20 address(es). If you are not the intended recipient of this message, please=20 notify the sender immediately and delete it from your system. Unauthorised=20 reproduction, disclosure, modification and or distribution of this e-mail=20 is strictly prohibited. The contents of this e-mail do not constitute a=20 commitment by Aubay S.p.A., except where expressly provided for in a=20 written agreement between you and Aubay.=20 --00000000000053f41405dd89e1cc Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Good morning, thank you very much for = the time you spent for my question.

=C2=A0

Yes inlining could be the problem, because ma= ybe does not allow to use the IMMUTABLE feature?

=C2=A0

The context of the query is quite= complex, therefore I avoided to provide it in previous email

=C2=A0

=C2=A0

Here it is what I te= sted. I=E2=80=99s a code fragment from a bigger procedure. The strings in g= reen 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

=C2=A0

SET random_page_cost =3D 0.1; (otherwise it takes mor= e than 4 minutes in place of 33 sec)

=C2=A0

EXPLAIN (ANALYZE, BUFFERS, verbose)

select

= *=C2=A0=C2=A0=C2=A0 from

=C2=A0=C2=A0=C2=A0 (

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 tms,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 fh.file_id,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_list #>= ;> ('{' || 'cluster_comuni_italiani' || ',s}= 9;)::text[])=C2=A0=C2=A0 as value_s_1,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 (e.measure_list #> ('{&#= 39; || 'c= luster_comuni_italiani' || ',n}')::= text[])::<= b>numeric=C2=A0= =C2=A0 as value_n_1,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_list #> ('{' || 'cluster_comuni_italiani' || ',o}')::text[])::numeric=C2=A0=C2=A0 as= value_o_1,

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= antsgeo_get_severity_thr((e.measu= re_list #> ('{' || 'cluster_comuni_italian= i' || = 9;,o}')::[])::numeric, = 1, 2, 3, 4, 5) AS severity_1,

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0(e.measure_list #>> ('{' || 'act_geoposition_pers_act_confidence' || ',s}'= ;)::text[])=C2=A0=C2=A0 as value_s_2,

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 (e.measure_list #> ('{&#= 39; || 'a= ct_geoposition_pers_act_confidence' || <= /span>',n}')= ::text[= ])::numeric=C2=A0=C2=A0 as value_n_2,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_list #>= ; ('{' || 'act_geoposition_pers_act_confidenc= e' || = 9;,o}')::[])::numeric=C2=A0=C2=A0 as value_o_2,<= span style=3D"font-size:8.0pt;font-family:"Courier New""><= /p>

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 antsgeo_get_severit= y_thr((e.measure_list #> ('{&#= 39; || 'a= ct_geoposition_pers_act_confidence' || <= /span>',o}')= ::text[= ])::numeric,=C2=A0 1,= 2, 3, 4, 5<= /span>) AS severity_2,

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0(e.measure_list #>> ('{' || 'act_coverage_band_pcell' || ',s}')::text[])=C2=A0=C2=A0 as value_s_3,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_lis= t #> ('{' || 'act_coverage_band_pcell'= || ',n}&= #39;)::text= [])::nu= meric=C2=A0=C2=A0 as value_n_3,

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 (e.measure_list #> ('= {' || = 9;act_coverage_band_pcell' || ',o}')::= text[])::numeric=C2= =A0=C2=A0 as value_o_3,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 antsgeo_get_severity_thr((e.measure_list #> ('{' || 'act_coverage_band_pcell' || ',o}')::text[])::numeric, 1, 2, <= /span>3, 4, 5) AS severity_3,

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0(e.measure_list #>> ('{' || = null::text || ',s}')::text[])= =C2=A0=C2=A0 as value_s_4,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_list #> (= '{' |= | null::text= || ',n}')::text[])::numeric=C2=A0=C2=A0 as value_n_4,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (e.measure_lis= t #> ('{' || null::text || ',o}')::text[])::numeric=C2=A0=C2=A0 as= value_o_4,

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 antsgeo_get_severity_thr((e.measure_lis= t #> ('{' || null::text || ',o}')::text[])::numeric,=C2=A0 1, 23, = 4, 5) AS severity_4

=C2=A0

=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 from

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0file_hist fh,

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0geo_measr_sample e= =

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 where

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 (

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 (fh.agn_group_= id =3D 21)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and fh.data_min_tms <=3D '2022-04-25 00:00:00' and fh.data_max_tms >=3D '2= 022-02-28 00:00:00' --lo usa

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 )

=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 and

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0and= (e.tms >=3D = '2022-02-28 00:00:00' and e.tms <=3D <= /span>'2022-04-25 00:00:00')

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 and (e.measure_list #>> ('{act_edit,s}')::text[] not in ('excld') or e.measure_list #>> ('{act= _edit,s}')::text[] is null)

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= )t1

=C2=A0

e.measure_list is a jsonb, with a variable structure

{

=C2=A0 "act= _plmn": {

=C2=A0=C2=A0=C2=A0 "s": "222/1"

=C2=A0 },

=C2=A0 "struct_day": {

= =C2=A0=C2=A0=C2=A0 "s": = "2022-04-22"

=C2=A0 },

=C2=A0 "struct_week": = {

=C2= =A0=C2=A0=C2=A0 "s": "= 2022-04-18"

=C2=A0 },

=C2=A0 "act_plmn_name": {

=C2=A0=C2= =A0=C2=A0 "s": "Tim.Ita = (222-01)"

=C2=A0 },

=C2=A0 "struct_act_id": {

=C2=A0=C2= =A0=C2=A0 "s": "1809464&= quot;

= =C2=A0 },

=C2=A0 "struct_tc_name": {

<= p class=3D"MsoNormal" style=3D"text-autospace:none">=C2=A0=C2=A0=C2=A0= "s": "VoiceCall_MO"= ;

=C2= =A0 },

= =C2=A0 "struct_yyyy_mm": {

=C2=A0=C2=A0=C2=A0 "s": "2022-04"= =

=C2=A0 },

=C2=A0 "act_coverage_ci": {

=C2=A0=C2=A0=C2=A0 "s": "63"

=C2=A0 },

=C2=A0 &qu= ot;act_coverage_ta": {

=C2=A0=C2=A0=C2=A0 "n&quo= t;: 4,

=C2=A0=C2=A0=C2=A0 "o&q= uot;: 4

=C2=A0 },

=C2=A0 "act_environment": {

=C2=A0=C2=A0=C2=A0 "s":

=C2=A0 },

=C2=A0 "cell_code_pcell": {

= =C2=A0=C2=A0=C2=A0 "s": "= ;FE23E3"

=C2=A0 },

=C2=A0 "struct_act_code": {

=C2=A0=C2= =A0=C2=A0 "s": "20220422= _164238_SDTU100010.01"

=C2=A0 },

=C2=A0 "struct_act_name": {

<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck">=C2=A0=C2=A0=C2=A0 "s": &= quot;20220422_164238_SDTU100010.01. Copy of Voice MO 0687201815"

=C2=A0 },=E2= =80=A6

=C2=A0

Nested Loop=C2=A0 (cost=3D0.43..2055500.00 rows=3D1441783 w= idth=3D524) (actual time=3D0.761..33647.744 rows=3D415401 loops=3D1)=

=C2= =A0 Output: e.tms, fh.file_id, (e.measure_list #>> ('{cluster_com= uni_italiani,s}'::cstring)::text[]), ((e.measure_list #> ('{clus= ter_comuni_italiani,n}'::cstring)::text[]))::numeric, ((e.measure_list = #> ('{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric, = CASE WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::= cstring)::text[]))::numeric)::double precision >=3D '4'::double = precision) THEN '1 Clear'::text WHEN ((((e.measure_list #> ('= ;{cluster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double pre= cision >=3D '3'::double precision) THEN '2 Warning'::tex= t WHEN ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cst= ring)::text[]))::numeric)::double precision >=3D '2'::double pre= cision) THEN '3 Minor'::text WHEN ((((e.measure_list #> ('{c= luster_comuni_italiani,o}'::cstring)::text[]))::numeric)::double precis= ion >=3D '1'::double precision) THEN '4 Major'::text WHE= N ((((e.measure_list #> ('{cluster_comuni_italiani,o}'::cstring)= ::text[]))::numeric)::double precision < '1'::double precision) = THEN '5 Critical'::text ELSE '6 Unk'::text END, (e.measure_= list #>> ('{act_geoposition_pers_act_confidence,s}'::cstring)= ::text[]), ((e.measure_list #> ('{act_geoposition_pers_act_confidenc= e,n}'::cstring)::text[]))::numeric, ((e.measure_list #> ('{act_g= eoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric, CASE W= HEN ((((e.measure_list #> ('{act_geoposition_pers_act_confidence,o}&= #39;::cstring)::text[]))::numeric)::double precision >=3D '4'::d= ouble precision) THEN '1 Clear'::text WHEN ((((e.measure_list #>= ('{act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::nu= meric)::double precision >=3D '3'::double precision) THEN '2= Warning'::text WHEN ((((e.measure_list #> ('{act_geoposition_pe= rs_act_confidence,o}'::cstring)::text[]))::numeric)::double precision &= gt;=3D '2'::double precision) THEN '3 Minor'::text WHEN (((= (e.measure_list #> ('{act_geoposition_pers_act_confidence,o}'::c= string)::text[]))::numeric)::double precision >=3D '1'::double p= recision) THEN '4 Major'::text WHEN ((((e.measure_list #> ('= {act_geoposition_pers_act_confidence,o}'::cstring)::text[]))::numeric):= :double precision < '1'::double precision) THEN '5 Critical&= #39;::text ELSE '6 Unk'::text END, (e.measure_list #>> ('= {act_coverage_band_pcell,s}'::cstring)::text[]), ((e.measure_list #>= ('{act_coverage_band_pcell,n}'::cstring)::text[]))::numeric, ((e.m= easure_list #> ('{act_coverage_band_pcell,o}'::cstring)::text[])= )::numeric, CASE WHEN ((((e.measure_list #> ('{act_coverage_band_pce= ll,o}'::cstring)::text[]))::numeric)::double precision >=3D '4&#= 39;::double precision) THEN '1 Clear'::text WHEN ((((e.measure_list= #> ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)= ::double precision >=3D '3'::double precision) THEN '2 Warni= ng'::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,= o}'::cstring)::text[]))::numeric)::double precision >=3D '2'= ::double precision) THEN '3 Minor'::text WHEN ((((e.measure_list #&= gt; ('{act_coverage_band_pcell,o}'::cstring)::text[]))::numeric)::d= ouble precision >=3D '1'::double precision) THEN '4 Major= 9;::text WHEN ((((e.measure_list #> ('{act_coverage_band_pcell,o}= 9;::cstring)::text[]))::numeric)::double precision < '1'::double= precision) THEN '5 Critical'::text ELSE '6 Unk'::text END,= NULL::text, NULL::numeric, NULL::numeric, '6 Unk'::text

=

=C2=A0 Bu= ffers: shared hit=3D365255

=C2=A0 ->=C2=A0 Seq Scan on geo_ants.file_hi= st fh=C2=A0 (cost=3D0.00..443.28 rows=3D311 width=3D8) (actual time=3D0.698= ..1.434 rows=3D315 loops=3D1)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = Output: fh.file_id, fh.file_name, fh.rtu, fh.port, fh.act_code, fh.file_siz= e, fh.file_tms, fh.loaded_tms, fh.update_tms, fh.status, fh.data_min_tms, f= h.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_l= oaded_tms, fh.error_count, fh.dbg_mode

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 Filter: ((fh.data_min_tms <=3D '2022-04-25 00:00:00'::= timestamp without time zone) AND (fh.data_max_tms >=3D '2022-02-28 0= 0:00:00'::timestamp without time zone) AND (fh.agn_group_id =3D 21))

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Rows Removed by Filter: 3358

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D379=

=C2= =A0 ->=C2=A0 Append=C2=A0 (cost=3D0.43..4609.77 rows=3D57257 width=3D155= 2) (actual time=3D0.012..9.971 rows=3D1319 loops=3D315)

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D106416

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan using geo_measr_sample_2= 022_02_act_id_tms_idx on geo_ants.geo_measr_sample_2022_02 e_1=C2=A0 (cost= =3D0.43..14.42 rows=3D166 width=3D1362) (actual time=3D0.003..0.003 rows=3D= 0 loops=3D315)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 Output: e_1.tms, e_1.measure_list, e_1.act_id<= /p>

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 In= dex Cond: ((e_1.act_id =3D fh.act_id) AND (e_1.tms >=3D '2022-02-28 = 00:00:00'::timestamp without time zone) AND (e_1.tms <=3D '2022-= 04-25 00:00:00'::timestamp without time zone))

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: (((e_1.me= asure_list #>> '{act_edit,s}'::text[]) <> 'excld= 9;::text) OR ((e_1.measure_list #>> '{act_edit,s}'::text[]) I= S NULL))

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 Buffers: shared hit=3D946

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 ->=C2=A0 Index Scan using geo_measr_sample_2022_03_act_id_t= ms_idx on geo_ants.geo_measr_sample_2022_03 e_2=C2=A0 (cost=3D0.56..2333.98= rows=3D30845 width=3D1552) (actual time=3D0.006..7.586 rows=3D1061 loops= =3D315)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Output: e_2.tms, e_2.measure_list, e_2.act_id

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Cond:= ((e_2.act_id =3D fh.act_id) AND (e_2.tms >=3D '2022-02-28 00:00:00&= #39;::timestamp without time zone) AND (e_2.tms <=3D '2022-04-25 00:= 00:00'::timestamp without time zone))

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Filter: (((e_2.measure_lis= t #>> '{act_edit,s}'::text[]) <> 'excld'::text)= OR ((e_2.measure_list #>> '{act_edit,s}'::text[]) IS NULL))<= /span>

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 Rows Removed by Filter: 3

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D75873<= /p>

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ->=C2=A0 Index Scan using geo_measr= _sample_2022_04_act_id_tms_idx on geo_ants.geo_measr_sample_2022_04 e_3=C2= =A0 (cost=3D0.43..1975.08 rows=3D26246 width=3D1557) (actual time=3D0.005..= 2.232 rows=3D258 loops=3D315)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Output: e_3.tms, e_3.measure_list, e_3= .act_id

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 Index Cond: ((e_3.act_id =3D fh.act_id) AND (e_3.tms >=3D &= #39;2022-02-28 00:00:00'::timestamp without time zone) AND (e_3.tms <= ;=3D '2022-04-25 00:00:00'::timestamp without time zone))

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Fi= lter: (((e_3.measure_list #>> '{act_edit,s}'::text[]) <>= ; 'excld'::text) OR ((e_3.measure_list #>> '{act_edit,s}&= #39;::text[]) IS NULL))

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D29597

Query Identifie= r: -6803725219970975357

Planning:

=C2=A0 Buffers: shared hit=3D933=

Plann= ing Time: 2.057 ms

Execution Time: 33677.292 ms

=C2=A0

CREATE OR REPLACE FUNCTION geo_ants.antsgeo_get_severity_th= r(v_measure_value double pr= ecision, thr_value_1 double= precision, thr_value_2 dou= ble precision, thr_value_3 = double precision= , thr_value_4 double p= recision, thr_value_5 double precision)

RETURNS text

LANGUAGE sql

<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck"> IMMUTABLE

AS $functio= n$

-= ---------------------------------------------------------------------------= ------------------------------------------

-- Author: Federico Travaglini

-- Date: 2020

-- Descript= ion:

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

----------------------= ---------------------------------------------------------------------------= ---------------------

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0

-- 20220426 non so perch=C3=A8 ma in questa version= e non =C3=A8 efifciente

=C2=A0=C2=A0=C2=A0 select

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0 case

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0--WHEN v_measure_value=3D 'NaN' = THEN '6 Unk'::text non scommentare o le performance per qualche mot= ivo iragionevole degradano di molto.

=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 when thr_value_1 =3D thr_value_4 then -- colorazione disabilitata, ad esempio per lat, long...

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0'6 none'::text

=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 w= hen thr_value_1 > thr_value_4 then -- valori critical > clear

=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= -- SIAMO NEL CASO: valori critical > clear ( thr_5 clear thr_= 4=C2=A0 warning thr_3 minor thr_2 major thr_1 critical)

=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0<= b>CASE

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHEN<= /span> v_measure_value >=3D thr_value_1 THEN<= /span> '5 Critical'::text --critical

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0 WHEN v_measure_value < thr_value_1 <= /span>AND v_measure_value >=3D thr_value_2 THEN '4 Major':= :text --major

=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0 WHEN v_measure_value < t= hr_value_2 AND v_measure_value >=3D thr_= value_3 THEN '3 Minor'<= /span>::text --minor

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 WHEN v_measure_val= ue < thr_value_3 AND v_measure_value >= ;=3D thr_value_4 THEN '2 Wa= rning'::text --war= ning

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHEN v_measure_value < thr_value_4 THEN '1 Clear'::text<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck"> --clear

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0ELSE '6 Unk'::text -- null values

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 else

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- SIAMO NEL CASO: valori c= ritical < clear (critical thr_1 maj thr_2=C2=A0 minor thr_3 war thr_4 cl= ear thr_5)

<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck">=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 CASE

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 WHEN v_measure_value < thr_value_= 1 THEN '5 Critical'::text --critical

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 WHEN v_measure_val= ue >=3D thr_value_1 AND v_measure_value = < thr_value_2 THEN '4 Ma= jor'::text --major= =

=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHEN v_me= asure_value >=3D thr_value_2 AND v_mea= sure_value < thr_value_3 THEN ::text --minor

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHEN v_measure_value >=3D thr_value_3 AND v_measure_value < thr_value_4 THEN'2 Warning'::text --warning

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHEN v_measure_value >=3D thr_value_4 <= b>THEN '1 Clear'::text --clear

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0ELSE '6 Unk&= #39;::text -- null val= ues

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 end=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

=

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0en= d::text=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0

$function$

;

=C2=A0

=C2=A0

=C2=A0<= /p>

By = the way, if I call the overall function where it is this code fragment, I g= et much better performance (22 sec in place of 41) re-writing function case= without nesting sub-cases, unfortunately I=E2=80=99m not so cleaver to get= the query plan for a query executed inside a function

CREATE OR REPLACE FUNCTION geo_ants.antsgeo_get_severity_th= r(v_measure_value double pr= ecision, thr_value_1 double= precision, thr_value_2 dou= ble precision, thr_value_3 = double precision= , thr_value_4 double p= recision, thr_value_5 double precision)

RETURNS text

LANGUAGE sql

<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck"> IMMUTABLE

AS $functio= n$

-= ---------------------------------------------------------------------------= ------------------------------------------

-- Author: Federico Travaglini

-- Date: 2020

-- Descript= ion:

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

----------------------= ---------------------------------------------------------------------------= ---------------------

=C2=A0=C2=A0=C2=A0 select

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 case

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0--WHEN v_measure_value=3D 'NaN' THEN '6 Unk'::text= this must be commented, it is not a prob= lem because the semantic does not change (same case of the ELSE), but I don= =E2=80=99t understand why it changes performance.

=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 when<= span style=3D"font-size:8.0pt;font-family:"Courier New";color:bla= ck"> thr_value_1 =3D thr_value_4 then '6 Unk'::text -- colorazione disabilitata, ad esempio per lat, long...

=

=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0-- SIAM= O NEL CASO: valori critical > clear ( thr_5 clear thr_4=C2=A0 warning th= r_3 minor thr_2 major thr_1 critical)

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHEN thr_valu= e_1 > thr_value_4 and v_measure_value &l= t;=C2=A0 thr_value_4=C2=A0 THEN '1 Clear'::text <= span style=3D"font-size:8.0pt;font-family:"Courier New";color:gra= y">--clear

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0WHEN thr_value_1 > thr_value_4 and v_measure_value <=C2=A0 thr_value_3=C2=A0 = THEN '2 Warning'= ::text --warning

<= p class=3D"MsoNormal" style=3D"text-autospace:none">=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHEN<= /span> thr_value_1 > thr_value_4 and= v_measure_value <=C2=A0 thr_value_2=C2=A0 THEN '3 Minor'::text<= /span> --minor

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 WHEN thr_value_1 >= thr_value_4 and v_measure_value <=C2=A0= thr_value_1=C2=A0 THEN '4= Major'::text --ma= jor

=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <= span style=3D"font-size:8.0pt;font-family:"Courier New";color:mar= oon">WHEN thr_value_1 > thr_value_4 and<= /span> v_measure_value >=3D thr_value_1=C2=A0 T= HEN '5 Critical'::text --major

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 -- SIAMO NEL CASO: valori critica= l < clear (critical thr_1 maj thr_2=C2=A0 minor thr_3 war thr_4 clear th= r_5)

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <= b>WHEN thr_value_1 < thr_value_4 a= nd v_measure_value >=3D thr_value_4 THEN= '1 Clear'::t= ext --clear

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0WHEN thr_valu= e_1 < thr_value_4 and v_measure_value &g= t;=3D thr_value_3 THEN '2= Warning'::text --= warning

WHEN thr_value_1 < thr_value_4 = and v_measure_value >=3D thr_value_2 THEN '3 Minor'::<= b>text --minor

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 WHEN thr_v= alue_1 < thr_value_4 and v_measure_value= >=3D thr_value_1 THEN '= 4 Major'::text --m= ajor

= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 <= b>WHEN thr_value_1 < thr_value_4 a= nd v_measure_value <=C2=A0 thr_value_1 T= HEN '5 Critical'::text --critical

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 ELSE '6 Unk'::text -- null values

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 end::text= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0

$function$

;

=C2=A0

Da: Merlin Moncure <m= moncure@gmail.com>
Inviato: luned=C3=AC 25 aprile 2022 21= :24
A: Federico Travaglini <federico.travaglini@aubay.it>
Cc: pgsql-bugs= <pgsql-bugs@lists.po= stgresql.org>
Oggetto: Re: 14.1 immutable function, bad pe= rformance if check number =3D 'NaN'

=C2=A0

On Mon, Apr 25, 2022 at 11:58 A= M Federico Travaglini <f= ederico.travaglini@aubay.it> wrote:

Good evening, and t= hanks to your excellent Postgres.

=C2=A0

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

If I l= eave the highlighted row it takes 27 seconds, otherwise 14 seconds! Such be= haviour looks not to be reasonable.

=C2=A0

lightly testin= g this, I got 10million iterations in about two seconds, about the same aft= er commenting the NaN test.=C2=A0 Given that, problem=C2=A0is probably fail= ure to inline query.=C2=A0 Careful examination of explain=C2=A0of wrapping = query should prove that.

=C2=A0

merlin



Th= is message is confidential and solely for the intended address(es). If you are not the intended recipient of this message, please notify the sende= r 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 A= ubay S.p.A., except where expressly provided for in a written agreement between = you and Aubay.
--00000000000053f41405dd89e1cc--