Created
February 26, 2022 13:29
-
-
Save ahukkanen/0020604a1c3dbb0deca19ac8c9a001e7 to your computer and use it in GitHub Desktop.
Inserts records and runs select queries on a PostgreSQL database to compare the performance of two alternate approaches.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/bash | |
| # Amount of inserts and selects | |
| AMT_INSERTS=10000 | |
| AMT_SELECTS=1000 | |
| # Executes the given statements and prints out the elapsed time | |
| execute_statements() { | |
| local statements=("$@") | |
| local tmp=$(mktemp /tmp/psql-performance.XXXXXX) | |
| printf -- '%s;\n' "${statements[@]}" > $tmp | |
| local start=$(date +%s.%3N) | |
| psql -U postgres test -f $tmp > /dev/null | |
| local end=$(date +%s.%3N) | |
| rm $tmp | |
| local elapsed=$(echo "scale=3; $end - $start" | bc) | |
| local avg=$(echo "scale=6; $elapsed / ${#statements[@]}" | bc) | |
| echo "Time elapsed: ${elapsed}" | |
| echo "Average query time: ${avg}" | |
| } | |
| progress() { | |
| local current=$1 | |
| local total=$2 | |
| local progress=$(( 100 * $current / $total )) | |
| echo -ne "${current} / ${total} (${progress}%)\r" | |
| } | |
| echo "Creating database 'test' with a table 'performance'" | |
| psql -U postgres -c "CREATE DATABASE test" | |
| psql -U postgres test -c "CREATE TABLE performance(id serial PRIMARY KEY, settings JSONB, some_toggle BOOLEAN)" | |
| psql -U postgres test -c "CREATE INDEX some_toggle_index ON performance (some_toggle)" | |
| boolvals=(TRUE FALSE) | |
| echo "Creating insert statements..." | |
| inserts=() | |
| for i in $(seq 1 $AMT_INSERTS); do | |
| progress $i $AMT_INSERTS | |
| bool=${boolvals[$RANDOM % 2]} | |
| toggle=$(($RANDOM % 2)) | |
| inserts+=("INSERT INTO performance (settings, some_toggle) VALUES ('{\"test_setting\": ${toggle}}', ${bool});") | |
| done | |
| echo "" | |
| echo "Inserting the records to the database..." | |
| execute_statements "${inserts[@]}" | |
| echo "Generating select statements for the JSONB column..." | |
| selects=() | |
| for i in $(seq 1 $AMT_SELECTS); do | |
| progress $i $AMT_SELECTS | |
| toggle=$(($RANDOM % 2)) | |
| selects+=("SELECT * FROM performance WHERE (settings->'test_setting')::int = ${toggle} ORDER BY RANDOM()") | |
| done | |
| echo "" | |
| echo "Running the database selects => JSONB" | |
| execute_statements "${selects[@]}" | |
| echo "Generating select statements for the BOOLEAN column..." | |
| selects=() | |
| for i in $(seq 1 $AMT_SELECTS); do | |
| progress $i $AMT_SELECTS | |
| bool=${boolvals[$RANDOM % 2]} | |
| selects+=("SELECT * FROM performance WHERE some_toggle = ${bool} ORDER BY RANDOM()") | |
| done | |
| echo "" | |
| echo "Running the database selects => BOOLEAN" | |
| execute_statements "${selects[@]}" | |
| echo "Dropping database 'test' with a table 'performance'" | |
| psql -U postgres test -c "DROP INDEX some_toggle_index" | |
| psql -U postgres test -c "DROP TABLE performance" | |
| psql -U postgres -c "DROP DATABASE test" |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment