關于資料庫postgresql, delete 與 insert，create 的性能比較

關于數字索引與字元索引的性能比較

44w資料 固态硬碟：數字索引288ms，字元索 varchar(300)引 2 s 99 ms

hhy_event_fuse.public> create index tmp23_event_time_index
                           on tmp23 (event_time)
[2023-01-13 14:09:01] completed in 288 ms
hhy_event_fuse.public> create index tmp23format_index
                           on tmp23 (time_format)
[2023-01-13 14:09:04] completed in 2 s 99 ms
           

關于 delete 與 insert，create 的性能比較

create -->1 s 571 ms

insert -->6 s 308 ms

delete -->517 ms

是以在資料量不是很大，或者保留比不是很大(3kw保留50w)的情況下 該delete還是delete吧

hhy_event_fuse.public> create table tmp24 as select * from  ods_traffic_event_info  where time_format <'2023-01-03 00:00:00'
[2023-01-13 14:15:09] 444,269 rows affected in 1 s 571 ms

hhy_event_fuse.public> insert into tmp23 select * from ods_traffic_event_info where time_format <'2023-01-03 00:00:00'
[2023-01-13 14:11:17] 444,269 rows affected in 6 s 308 ms

hhy_event_fuse.public> delete  from tmp23 where time_format <'2023-01-03 00:00:00'
[2023-01-13 14:10:37] 444,269 rows affected in 517 ms