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

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-2800: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.42rows=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.98rows=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.08rows=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