对于insert 与 copy 性能的测试

lsd_base=> insert into panj1 select * from td_user_info where usr_id > 5000000 and mod(usr_id,40)=1;
INSERT 0 337943
Time: 58468.442 ms
lsd_base=> \copy (select * from td_user_info where usr_id > 5000000 and mod(usr_id,40)=1) to ‘/tmp/panjtest1.sql‘
Time: 4864.592 ms
lsd_base=> \copy panj2 from ‘/tmp/panjtest1.sql‘
Time: 4972.855 ms

 

 

 

lsd_base=> insert into panj1 select * from td_user_info where usr_id < 5000000;
INSERT 0 621278
Time: 141444.356 ms
lsd_base=> \copy (select * from td_user_info where usr_id < 5000000) to ‘/tmp/panjtest1.sql‘
Time: 5969.146 ms
lsd_base=> ^C
lsd_base=> create table panj2 (like td_user_info including all);
CREATE TABLE
Time: 192.761 ms
lsd_base=> \copy panj2 from ‘/tmp/panjtest1.sql‘
Time: 12131.699 ms

 

主要是，原先我们的一个表的数据太大了，想把这个表的数据进行分表，然后要把原先的表里的数据按规则放到不同的分表里去，因为分表也是在同一个数据库中，所以有个方案就是直接执行sql语句insert into goaltablename select * from sourcetablename where  condition; 还有一个方案就是先copy to 再 copy from

经过测试 copy 的方案速度快于insert的方案