run_bench.sh

application/x-shellscript

Download

#!/bin/bash

TEMPFILE="/tmp/pgbench.sql" # test query

for AMOUNT in 100 1000 10000 100000 1000000; do
    for TYPE in int bigint uuid text; do
        TABLENAME="${TYPE}${AMOUNT}"

        echo "running $TYPE $AMOUNT"
        
        if [ "$AMOUNT" == "100000" ]; then
            DURATION=120 # 2 min
        elif [ "$AMOUNT" == "1000000" ]; then
            DURATION=180 # 3 min
        else
            DURATION=60  # 1 min
        fi

        # Hash Aggregate
        psql -c 'ALTER SYSTEM SET enable_indexagg to OFF'
        psql -c 'ALTER SYSTEM SET enable_sort to OFF;'
        psql -c 'ALTER SYSTEM SET enable_hashagg to ON;'
        psql -c 'SELECT pg_reload_conf();'
        echo "select i, sum(j) from $TABLENAME group by 1 order by 1" >"$TEMPFILE"
        pgbench -f "$TEMPFILE" -n -T $DURATION > "hashagg_$TABLENAME"

        # Group Aggregate
        psql -c 'ALTER SYSTEM SET enable_indexagg to OFF'
        psql -c 'ALTER SYSTEM SET enable_sort to ON;'
        psql -c 'ALTER SYSTEM SET enable_hashagg to OFF;'
        psql -c 'SELECT pg_reload_conf();'
        echo "select i, sum(j) from $TABLENAME group by 1 order by 1;" >"$TEMPFILE"
        pgbench -f "$TEMPFILE" -n -T $DURATION > "groupagg_$TABLENAME"

        # Index Aggregate
        psql -c 'ALTER SYSTEM SET enable_indexagg to ON'
        psql -c 'ALTER SYSTEM SET enable_sort to OFF;'
        psql -c 'ALTER SYSTEM SET enable_hashagg to OFF;'
        psql -c 'SELECT pg_reload_conf();'
        echo "select i, sum(j) from $TABLENAME group by 1 order by 1" >"$TEMPFILE"
        pgbench -f "$TEMPFILE" -n -T $DURATION > "indexagg_$TABLENAME"
    done
done

# echo "running bigtext"
TABLENAME='bigtext'
DURATION=240 # 4 min

# Hash Aggregate
psql -c 'ALTER SYSTEM SET enable_indexagg to OFF'
psql -c 'ALTER SYSTEM SET enable_sort to OFF;'
psql -c 'ALTER SYSTEM SET enable_hashagg to ON;'
psql -c 'SELECT pg_reload_conf();'
echo "select i, sum(j) from $TABLENAME group by 1 order by 1" >"$TEMPFILE"
pgbench -f "$TEMPFILE" -n -T $DURATION > "hashagg_$TABLENAME"

# Group Aggregate
psql -c 'ALTER SYSTEM SET enable_indexagg to OFF'
psql -c 'ALTER SYSTEM SET enable_sort to ON;'
psql -c 'ALTER SYSTEM SET enable_hashagg to OFF;'
psql -c 'SELECT pg_reload_conf();'
echo "select i, sum(j) from $TABLENAME group by 1 order by 1;" >"$TEMPFILE"
pgbench -f "$TEMPFILE" -n -T $DURATION > "groupagg_$TABLENAME"

# Index Aggregate
psql -c 'ALTER SYSTEM SET enable_indexagg to ON'
psql -c 'ALTER SYSTEM SET enable_sort to OFF;'
psql -c 'ALTER SYSTEM SET enable_hashagg to OFF;'
psql -c 'SELECT pg_reload_conf();'
echo "select i, sum(j) from $TABLENAME group by 1 order by 1" >"$TEMPFILE"
pgbench -f "$TEMPFILE" -n -T $DURATION > "indexagg_$TABLENAME"