test-saop-hashjoin.sh
application/x-sh
Filename: test-saop-hashjoin.sh
Type: application/x-sh
Part: 0
#!/bin/bash
psql test -c "drop table if exists t" > /dev/null
psql test -c "create table t (a int)" > /dev/null
echo '' > saop-queries.sql
echo '' > saop-inserts.sql
x=0
y=0
echo insert query fraction type rows hashjoin distinct bloom binsearch fpr run duration results
for n in 10 100 1000 10000 100000; do
for d in 10 100 1000 10000 25000; do
if [ "$d" -gt "$n" ]; then
continue
fi
psql test -c "truncate t" > /dev/null
psql test -c "insert into t select mod(i,$d) from generate_series(1,$n) s(i)" > /dev/null
psql test -c "vacuum analyze t" > /dev/null
psql test -c "checkpoint" > /dev/null
x=$((x+1))
echo $x "insert into t select mod(i,$d) from generate_series(1,$n) s(i)" >> saop-inserts.sql
# 100% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ', '), string_agg(i::text, '), (') from generate_series(0, $d-1) s(i)"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ($v1);" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ($v1);
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-100" int $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ($v2));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ($v2));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-100" int $n $d on off off $fpr $r $time $rows
fi
done
done
# 50% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ', '), string_agg(i::text, '), (') from (select (i-1) as i from generate_series(1, $d * 2) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ($v1);" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ($v1);
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-50" int $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ($v2));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ($v2));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-50" int $n $d on off off $fpr $r $time $rows
fi
done
done
# 10% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ', '), string_agg(i::text, '), (') from (select (i-1) as i from generate_series(1, $d * 10) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ($v1);" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ($v1);
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-10" int $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ($v2));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ($v2));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-10" int $n $d on off off $fpr $r $time $rows
fi
done
done
# 1% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ', '), string_agg(i::text, '), (') from (select (i-1) as i from generate_series(1, $d * 100) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ($v1);" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ($v1);
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-1" int $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ($v2));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ($v2));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-1" int $n $d on off off $fpr $r $time $rows
fi
done
done
done
done
psql test -c "drop table if exists t" > /dev/null
psql test -c "create table t (a text collate \"en_US\")" > /dev/null
for n in 10 100 1000 10000 100000; do
for d in 10 100 1000 10000 25000; do
if [ "$d" -gt "$n" ]; then
continue
fi
psql test -c "truncate t" > /dev/null
psql test -c "insert into t select md5(mod(i,$d)::text) from generate_series(1,$n) s(i)" > /dev/null
psql test -c "vacuum analyze t" > /dev/null
psql test -c "checkpoint" > /dev/null
x=$((x+1))
echo $x "insert into t select md5(mod(i,$d)::text) from generate_series(1,$n) s(i)" >> saop-inserts.sql
# 100% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(md5(i::text), ''', '''), string_agg(md5(i::text), '''), (''') from generate_series(0, $d-1) s(i)"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ('$v1');" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ('$v1');
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-100" text $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-100" text $n $d on off off $fpr $r $time $rows
fi
done
done
# 50% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ''', '''), string_agg(i::text, '''), (''') from (select md5((i-1)::text) as i from generate_series(1, $d * 2) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ('$v1');" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ('$v1');
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-50" text $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-50" text $n $d on off off $fpr $r $time $rows
fi
done
done
# 10% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ''', '''), string_agg(i::text, '''), (''') from (select md5((i-1)::text) as i from generate_series(1, $d * 10) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ('$v1');" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ('$v1');
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-10" text $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-10" text $n $d on off off $fpr $r $time $rows
fi
done
done
# 1% matches
for fpr in 0.01 0.05 0.1 0.2; do
for r in `seq 1 5`; do
v=`psql test -t -A -c "select string_agg(i::text, ''', '''), string_agg(i::text, '''), (''') from (select md5((i-1)::text) as i from generate_series(1, $d * 100) s(i) order by random() limit $d) foo"`
v1=`echo $v | sed 's/|.*//'`
v2=`echo $v | sed 's/.*|//'`
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN ('$v1');" >> saop-queries.sql
for bloom in on off; do
for binsearch in on off; do
if [ "$bloom" == "on" ] && [ "$binsearch" == "on" ]; then
continue
fi
if [ "$bloom" == "off" ] && [ "$fpr" != "0.01" ]; then
continue
fi
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = $bloom;
set enable_saop_binsearch = $binsearch;
set max_parallel_workers_per_gather = 0;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN ('$v1');
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-1" text $n $d off $bloom $binsearch $fpr $r $time $rows
done
done
# try running a hashjoin-based version of the query
if [ "$fpr" == "0.01" ]; then
y=$((y+1))
echo $y "SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));" >> saop-queries.sql
psql -t -A test > tmp.log <<EOF
set enable_saop_threshold = 1;
set enable_saop_bloom = off;
set enable_saop_binsearch = off;
set max_parallel_workers_per_gather = 0;
set enable_mergejoin = off;
set enable_nestloop = off;
set saop_bloom_false_positives = $fpr;
\timing
SELECT count(*) FROM t WHERE a IN (VALUES ('$v2'));
EOF
time=`grep Time tmp.log | awk '{print $2}'`
rows=`tail -n 2 tmp.log | head -n 1`
echo $x $y "match-1" text $n $d on off off $fpr $r $time $rows
fi
done
done
done
done