Re: 14.1 immutable function, bad performance if check number = 'NaN' - Mailing list pgsql-bugs
From | Merlin Moncure |
---|---|
Subject | Re: 14.1 immutable function, bad performance if check number = 'NaN' |
Date | |
Msg-id | CAHyXU0wM781jxN5pLb4cs4pD+iYCXPMoeJqDv+ZkkBD7P0cHdA@mail.gmail.com Whole thread Raw |
In response to | R: 14.1 immutable function, bad performance if check number = 'NaN' (Federico Travaglini <federico.travaglini@collaboration.aubay.it>) |
List | pgsql-bugs |
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
pgsql-bugs by date: