test1.sh.txt

text/plain

Download

#!/bin/bash

dbname=postgres
port=5432

test_cases=(
"100 100 5000000"		# only 100 bytes	
"1024 10240 200000"    # 1Kb and 10Kb 
"1024 1048576 2000"		# 1Kb and 1Mb 
"1048576 1048576 1000"	# all 1Mb
)

insert_rows(){
	psql -d $dbname -p $port -c "
	DO \$\$
	DECLARE
	    counter INT;
	BEGIN
	    FOR counter IN 1..$3 LOOP
	        IF counter % 2 = 1 THEN
	            INSERT INTO test_table VALUES (repeat('a', $1)::text);
	        ELSE
	            INSERT INTO test_table VALUES (repeat('b', $2)::text);
	        END IF;
	    END LOOP;
	END \$\$;
	" > /dev/null
}


psql -d $dbname -p $port -c "CREATE EXTENSION IF NOT EXISTS pg_prewarm;" > /dev/null

for case in "${test_cases[@]}" 
do
	psql -d $dbname -p $port -c "DROP TABLE IF EXISTS test_table;" > /dev/null
	psql -d $dbname -p $port -c "CREATE TABLE test_table(data text not null);" > /dev/null
	insert_rows $case

	psql -d $dbname -p $port -c "select pg_prewarm('test_table');" > /dev/null

	echo -n "Run $case: "

	elapsed_time=0
	for a in {1..30}
	do
		start_time=$(perl -MTime::HiRes=time -e 'printf "%.9f\n", time')
		psql -d $dbname -p $port -c "COPY test_table TO STDOUT;" > /dev/null
		end_time=$(perl -MTime::HiRes=time -e 'printf "%.9f\n", time')
		elapsed_time=$(perl -e "printf('%.9f', ($end_time - $start_time) + $elapsed_time)")
	done
	
	avg_elapsed_time_in_ms=$(perl -e "printf('%.9f', ($elapsed_time / 30))")
	echo $avg_elapsed_time_in_ms
done