(unnamed)

text/plain

Download

#!/usr/bin/bash

DIR=${1:-$(pwd)}

psql postgres > /dev/null 2>&1 <<EOF
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
EOF
psql test > /dev/null 2>&1 <<EOF
CREATE EXTENSION blackhole_am;
CREATE OR REPLACE FUNCTION create_table_cols(tabname text, num_cols int)
RETURNS VOID AS
\$func\$
DECLARE
  query text;
BEGIN
  query := 'CREATE UNLOGGED TABLE ' || tabname || ' (';
  FOR i IN 1..num_cols LOOP
    query := query || 'a_' || i::text || ' int default 1';
    IF i != num_cols THEN
      query := query || ', ';
    END IF;
  END LOOP;
  query := query || ')';
  EXECUTE format(query);
END
\$func\$ LANGUAGE plpgsql;
EOF

for c in $(seq 5 5 30); do

	for rows in $(seq 1 10); do

		psql test > /dev/null 2>&1 <<EOF
DROP TABLE IF EXISTS to_table;
DROP TABLE IF EXISTS from_table;

SELECT create_table_cols ('to_table', $c);
SELECT create_table_cols ('from_table', $c);

INSERT INTO to_table SELECT FROM generate_series(1, $rows * 1000000);

COPY to_table TO '$DIR/test.data' WITH (FORMAT text);

ALTER TABLE from_table SET ACCESS METHOD blackhole_am;
EOF

		for r in $(seq 1 10); do

			s=$(psql test -t -A -c "SELECT EXTRACT(EPOCH FROM now())")
			psql test -c "COPY to_table TO '/dev/null' WITH (FORMAT text)" > /dev/null 2>&1
			d=$(psql test -t -A -c "SELECT 1000 * (EXTRACT(EPOCH FROM now()) - $s)")

			echo "COPY_TO" $c $rows $r $d

		done

		# run COPY FROM 10x
		for r in $(seq 1 10); do

			s=$(psql test -t -A -c "SELECT EXTRACT(EPOCH FROM now())")
			psql test -c "COPY from_table FROM '$DIR/test.data' WITH (FORMAT text)" > /dev/null 2>&1
			d=$(psql test -t -A -c "SELECT 1000 * (EXTRACT(EPOCH FROM now()) - $s)")

			echo "COPY_FROM" $c $rows $r $d

		done

	done

done