copy_par_regress_test.sql

begin;
drop table if exists pp;
CREATE TABLE pp (id  INT, val int ) PARTITION BY RANGE (id);
create table pp_110 partition of pp for values from (1) to (100_000) partition by range (id);
create table pp_112 partition of pp for values from (100_000) to (200_000) partition by range (id);
create table pp_113 partition of pp for values from (200_000) to (300_000) partition by range (id);
create table pp_114 partition of pp for values from (300_000) to (400_000) partition by range (id);
create table pp_115 partition of pp for values from (400_000) to (500_000) partition by range (id);
create table pp_116 partition of pp for values from (500_000) to (600_000) partition by range (id);
create table pp_117 partition of pp for values from (600_000) to (700_000) partition by range (id);
create table pp_118 partition of pp for values from (700_000) to (800_000) partition by range (id);
create table pp_119 partition of pp for values from (800_000) to (900_000) partition by range (id);
create table pp_120 partition of pp for values from (900_000) to (1000_001) partition by range (id);

create table pp_110_1 (like pp);
create table pp_110_2 (like pp);
create table pp_110_3 (like pp);
create table pp_110_4 (like pp);
create table pp_110_5 (like pp);
create table pp_110_6 (like pp);
create table pp_110_7 (like pp);
create table pp_110_8 (like pp);
create table pp_110_9 (like pp);
create table pp_110_10 (like pp);
 
ALTER TABLE pp_110 ATTACH PARTITION pp_110_1 FOR VALUES FROM (1) TO (100_000);
ALTER TABLE pp_112 ATTACH PARTITION pp_110_2 for values from (100_000) to (200_000);
ALTER TABLE pp_113 ATTACH PARTITION pp_110_3 for values from (200_000) to (300_000);
ALTER TABLE pp_114 ATTACH PARTITION pp_110_4 for values from (300_000) to (400_000);
ALTER TABLE pp_115 ATTACH PARTITION pp_110_5 for values from (400_000) to (500_000);
ALTER TABLE pp_116 ATTACH PARTITION pp_110_6 for values from (500_000) to (600_000);
ALTER TABLE pp_117 ATTACH PARTITION pp_110_7 for values from (600_000) to (700_000);
ALTER TABLE pp_118 ATTACH PARTITION pp_110_8 for values from (700_000) to (800_000);
ALTER TABLE pp_119 ATTACH PARTITION pp_110_9 for values from (800_000) to (900_000);
ALTER TABLE pp_120 ATTACH PARTITION pp_110_10 for values from (900_000) to (1000_001);

insert into pp select g, g+1 from generate_series(1,1_000_000) g;
commit;

--last time of the 10 execution
-- Time: 1566.187 ms (00:01.566)
copy (select * from pp) to '/tmp/1.txt' \watch i=0.1 c=10

--last time of the 10 execution
--Time: 1444.596 ms (00:01.445)
copy pp to '/tmp/2.txt' \watch i=0.1 c=10