copy_on_conflict_no_unique_idx_test.sql

/*
performance tests for
discussion: https://postgr.es/m/CACJufxG672yotDt87Dbazf1C9scnZm7QSB+zu6vHc+j5QrjXvA@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/6736

using psql do the tests
*/

CREATE unlogged TABLE err_tbl1(copy_tbl oid, filename text, lineno bigint, line text);
create unlogged table t0(col1 int);
create unlogged table t1(col1 int);
create unlogged table t2(
    col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int, col9 int, col10 int
   ,col11 int, col12 int, col13 int, col14 int, col15 int, col16 int, col17 int, col18 int, col19 int, col20 int
   ,col21 int, col22 int, col23 int, col24 int, col25 int, col26 int, col27 int, col28 int, col29 int, col30 int);
create unlogged table t3(col1 int);
create unlogged table t4(
    col1 int, col2 int, col3 int, col4 int, col5 int, col6 int, col7 int, col8 int, col9 int, col10 int
   ,col11 int, col12 int, col13 int, col14 int, col15 int, col16 int, col17 int, col18 int, col19 int, col20 int
   ,col21 int, col22 int, col23 int, col24 int, col25 int, col26 int, col27 int, col28 int, col29 int, col30 int);

truncate t1;
truncate t2;
truncate t3;
truncate t4;
insert into t1 select g from generate_series(1, 10_000_000) g;
insert into t2 select  g + 1 ,g + 2 ,g + 3 ,g + 4 ,g + 5 ,g + 6 ,g + 7 ,g + 8 ,g + 9 ,g + 10 ,g + 11 ,g + 12 ,g + 13 ,g + 14 ,g + 15 ,
            g + 16 ,g + 17 ,g + 18 , g + 19 ,g + 20 ,g + 21 ,
            g + 22 ,g + 23 ,g + 24 ,g + 25 ,g + 26 ,g + 27 ,g + 28 ,g + 29 , g + 30 from generate_series(1, 10_000_000) g;
copy t1 to '/tmp/onecol.csv';
copy t2 to '/tmp/30col.csv';

create index on t1(col1);
create index on t3(col1);

-- Time: 3015.693 ms (00:03.016)
truncate t1 \; copy t1 from '/tmp/onecol.csv' \watch i=0.1 c=10

-- Time: 2980.963 ms (00:02.981)
truncate t3 \; copy t3 from '/tmp/onecol.csv' with (on_conflict table, conflict_table 'err_tbl1') \watch i=0.1 c=10

create index on t2(col30);
create index on t4(col30);

-- Time: 7419.685 ms (00:07.420)
truncate t2 \; copy t2 from '/tmp/30col.csv' \watch i=0.1 c=5

-- Time: 7409.777 ms (00:07.410)
truncate t4 \; copy t4 from '/tmp/30col.csv' with (on_conflict table, conflict_table 'err_tbl1') \watch i=0.1 c=5