Tables to test effect of different data types on joins with 5m rows

2-col.sql

create unlogged table int4_aa (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table int4_bb (id int, id2 int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into int4_aa
  select i, i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into int4_bb
  select * from int4_aa;

create unlogged table int8_aa (id int8, id2 int8, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table int8_bb (id int8, id2 int8, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into int8_aa
  select i, i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into int8_bb
  select * from int8_aa;

create unlogged table numeric_aa (id numeric, id2 numeric, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table numeric_bb (id numeric, id2 numeric, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into numeric_aa
  select i, i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into numeric_bb
  select * from numeric_aa;

create unlogged table uuid_aa (id uuid, id2 uuid, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table uuid_bb (id uuid, id2 uuid, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

create extension if not exists "uuid-ossp";

insert into uuid_aa
  select uid, uid, i, i, i, i, now(), now()
  from (
  select uuid_generate_v4() as uid, i
     from generate_series(1, 5*1e6) i) a;

insert into uuid_bb
  select * from uuid_aa;

create extension if not exists pg_stat_statements;

-- vacuum or analyze kicking in during tests could affect the results
vacuum analyze;

-- cleanup
-- drop table int4_b, int4_a, int8_a, int8_b, numeric_a, numeric_b, uuid_a, uuid_b;

int4_vs_in8_vs_numeric_vs_uuid.sql

create unlogged table int4_a (id int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table int4_b (id int, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into int4_a
  select i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into int4_b
  select * from int4_a;

create unlogged table int8_a (id int8, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table int8_b (id int8, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into int8_a
  select i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into int8_b
  select * from int8_a;

create unlogged table numeric_a (id numeric, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table numeric_b (id numeric, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

insert into numeric_a
  select i, i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into numeric_b
  select * from numeric_a;

create unlogged table uuid_a (id uuid, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);
create unlogged table uuid_b (id uuid, col1 int, col2 int, col3 text, col4 text, col5 timestamptz, col6 timestamptz);

create extension if not exists "uuid-ossp";

insert into uuid_a
  select uuid_generate_v4(), i, i, i, i, now(), now()
from generate_series(1, 5*1e6) i;

insert into uuid_b
  select * from uuid_a;

create extension if not exists pg_stat_statements;

-- vacuum or analyze kicking in during tests could affect the results
vacuum analyze;

-- cleanup
-- drop table int4_b, int4_a, int8_a, int8_b, numeric_a, numeric_b, uuid_a, uuid_b;

test.sh

LOOPCOUNT=1000

echo "resetting pg_stat_statements data..."
psql -qXc "select pg_stat_statements_reset()"

echo "doing ${LOOPCOUNT}x int4..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from int4_a join int4_b using(id)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x int8..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from int8_a join int8_b using(id)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x numeric..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from numeric_a join numeric_b using(id)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x uuid..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from uuid_a join uuid_b using(id)" postgres &>/dev/null
done

test_2-col.sh

LOOPCOUNT=1000

echo "resetting pg_stat_statements data..."
psql -qXc "select pg_stat_statements_reset()"

echo "doing ${LOOPCOUNT}x int4..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from int4_aa join int4_bb using(id, id2)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x int8..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from int8_aa join int8_bb using(id, id2)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x numeric..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from numeric_aa join numeric_bb using(id, id2)" postgres &>/dev/null
done

echo "doing ${LOOPCOUNT}x uuid..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "select count(*) from uuid_aa join uuid_bb using(id, id2)" postgres &>/dev/null
done

test_index_building.sh

LOOPCOUNT=1000

#echo "resetting pg_stat_statements data..."
#psql -qXc "select pg_stat_statements_reset()"

echo "building index ${LOOPCOUNT}x int4..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "drop index if exists test_idx_int4" postgres &>/dev/null
	psql -qXc "create index test_idx_int4 on int4_a (id)" postgres &>/dev/null
done

echo "building index ${LOOPCOUNT}x int8..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "drop index if exists test_idx_int8" postgres &>/dev/null
	psql -qXc "create index test_idx_int8 on int8_a (id)" postgres &>/dev/null
done

echo "building index ${LOOPCOUNT}x numeric..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "drop index if exists test_idx_numeric" postgres &>/dev/null
	psql -qXc "create index test_idx_numeric on numeric_a (id)" postgres &>/dev/null
done

echo "building index ${LOOPCOUNT}x uuid..."
for x in $(seq 1 ${LOOPCOUNT}) ; do 
	psql -qXc "drop index if exists test_idx_uuid" postgres &>/dev/null
	psql -qXc "create index test_idx_uuid on uuid_a (id)" postgres &>/dev/null
done