Re: 14.1 immutable function, bad performance if check number = 'NaN'

On Tue, Apr 26, 2022 at 2:45 AM Federico Travaglini
<federico(dot)travaglini(at)collaboration(dot)aubay(dot)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