fdw_copy_test.sql

create database test;
\c test
drop table if exists batch_table_3;
create table batch_table_3(x int, b float8 default random(min=>1::int, max=> 10));
create function dummy() returns trigger as $$ begin return new; end $$ language plpgsql;
create trigger dummy
	before insert or update on batch_table_3
	for each row execute procedure dummy();

\c src3
SELECT current_database() AS current_database,
  current_setting('port') AS current_port
\gset

CREATE SERVER loopback1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'test', port :'current_port');
CREATE USER MAPPING FOR public SERVER loopback1;

DROP FOREIGN TABLE IF EXISTS ftable1, ftable2;
CREATE FOREIGN TABLE ftable1 (x int, b float8 default random(min=>1::int, max=> 10)) 
    SERVER loopback1 OPTIONS ( table_name 'batch_table_3', batch_size '2');

CREATE FOREIGN TABLE ftable2 (x int, b float8 default random(min=>1::int, max=> 10)) 
    SERVER loopback1 OPTIONS ( table_name 'batch_table_3', batch_size '10');

CREATE FOREIGN TABLE ftable3 (x int, b float8 default random(min=>1::int, max=> 10)) 
    SERVER loopback1 OPTIONS ( table_name 'batch_table_3', batch_size '5');

CREATE FOREIGN TABLE ftable4 (x int, b float8 default random(min=>1::int, max=> 10)) 
    SERVER loopback1 OPTIONS ( table_name 'batch_table_3', batch_size '3');

explain (analyze, verbose, costs off) INSERT INTO ftable1 SELECT g, random() from generate_series(1, 100_000) g;
/*
COPY
11172.764 ms
11170.890 ms
11082.659 ms
11157.299 ms

BATCH INSERT:
11262.645 ms
11159.859 ms
11149.722 ms
11145.885 ms
*/

--batch 10 COPY:3399.095 ms, 3782.840 ms
explain (analyze, verbose, costs off) INSERT INTO ftable2 SELECT g, random() from generate_series(1, 100_000) g;

--batch 5 COPY: 5266.171 ms 5594.412 ms
explain (analyze, verbose, costs off) INSERT INTO ftable3 SELECT g, random() from generate_series(1, 100_000) g;

--batch 3 COPY: 7795.766 ms 8137.602 ms
explain (analyze, verbose, costs off) INSERT INTO ftable4 SELECT g, random() from generate_series(1, 100_000) g;