Skip to content

Instantly share code, notes, and snippets.

@kmoppel

kmoppel/2-col.sql

Last active Jan 16, 2018
Embed
What would you like to do?
Tables to test effect of different data types on joins with 5m rows
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;
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;
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
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
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment