Horton

show_plan.sh

application/x-shellscript

Download
Filename: show_plan.sh
Type: application/x-shellscript
Part: 3
Message: Re: Introduce Index Aggregate - new GROUP BY strategy 
#!/bin/bash

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

        echo "running $TYPE $AMOUNT"

        # 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();'
        psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"hashagg_${TABLENAME}_explain"

        # 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();'
        psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"groupagg_${TABLENAME}_explain"

        # 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();'
        psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"indexagg_${TABLENAME}_explain"
    done
done

TABLENAME='bigtext'

# 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();'
psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"hashagg_${TABLENAME}_explain"

# 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();'
psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"groupagg_${TABLENAME}_explain"

# 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();'
psql -c "explain select i, sum(j) from $TABLENAME group by 1 order by 1" >"indexagg_${TABLENAME}_explain"