Run `EXPLAIN ANALYZE` on a given query multiple times, output the average planning and execution times

time.sh

#!/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:"