Skip to content

Instantly share code, notes, and snippets.

@matt-forster
Forked from trs/time.sh
Last active October 21, 2021 20:27
Show Gist options
  • Save matt-forster/79b6f08ad4dda55bec3c3739756c38c7 to your computer and use it in GitHub Desktop.
Save matt-forster/79b6f08ad4dda55bec3c3739756c38c7 to your computer and use it in GitHub Desktop.
Run `EXPLAIN ANALYZE` on a given query multiple times, output the average planning and execution times
#!/bin/bash
# Usage
# time.sh "$(r2 creds -e staging --bare)" "$(cat query.sql)" 10
creds="$1"
query="$2"
count="${3:-10}"
out="$(mktemp)"
ignore_first=0 # Change to ignore the first n tries
function get_time() {
grep -oE "$1 ([0-9]+\.[0-9]+)" | awk -F ": " '{print $2}'
}
function get_average_time() {
type="$1"
time=$(get_time "$type" | awk 'BEGIN { total = 0 } { total += $1; count++ } END { print total/count }')
echo "$type $time"
}
function get_max_time() {
type="$1"
time=$(get_time "$type" | awk 'BEGIN { max = 0 } { max = (max > $1 ? max : $1) } END { print max }')
echo "$type $time"
}
function get_min_time() {
type="$1"
time=$(get_time "$type" | awk 'BEGIN { min = 999999999 } { min = (min < $1 ? min : $1) } END { print min }')
echo "$type $time"
}
for (( c=0; c<count+ignore_first; c++ )); do
result=$(psql "$creds" -q -c "EXPLAIN (ANALYZE, VERBOSE, BUFFERS) $query")
if (( c>ignore_first )); then
echo "$result" >> "$out"
fi
done
echo Average
cat "$out" | get_average_time "Planning time:"
cat "$out" | get_average_time "Execution time:"
echo Max
cat "$out" | get_max_time "Planning time:"
cat "$out" | get_max_time "Execution time:"
echo Min
cat "$out" | get_min_time "Planning time:"
cat "$out" | get_min_time "Execution time:"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment