#!/bin/bash
# usage: fdw_update_cost.sh <install prefix> <port>
#
# Cost of a non-direct UPDATE through postgres_fdw, on a loopback server.
# Remote tables: a plain one and one with 100 list partitions, 100000 rows
# each.  "random() <= 1" keeps the UPDATE from being shipped whole.
#
# 1. Median of 5 runs, for 1000 and 10000 rows, on each table.
# 2. For 1000 rows on the partitioned table, the statements the remote
#    side received (log_min_duration_statement = 0), grouped, with their
#    total time and count.
P=$1; PORT=$2
D=/tmp/fdwcost_$PORT
rm -rf $D
$P/bin/initdb -D $D -U postgres --no-sync >/dev/null 2>&1
cat >> $D/postgresql.conf <<EOF
shared_buffers = 512MB
fsync = off
synchronous_commit = off
EOF
$P/bin/pg_ctl -D $D -o "-p $PORT -k /tmp" -l $D/log -w start >/dev/null
trap '$P/bin/pg_ctl -D $D -m immediate -w stop >/dev/null 2>&1; rm -rf $D' EXIT
PSQL="$P/bin/psql -h /tmp -p $PORT -U postgres -X -q postgres"

$PSQL <<SQL
CREATE EXTENSION postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'postgres', port '$PORT', host '/tmp');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback OPTIONS (user 'postgres');
CREATE TABLE plain (id int PRIMARY KEY, grp int, v int);
INSERT INTO plain SELECT g, g % 100, 0 FROM generate_series(1, 100000) g;
CREATE TABLE part (id int, grp int, v int) PARTITION BY LIST (grp);
SELECT format('CREATE TABLE part_%s PARTITION OF part FOR VALUES IN (%s)', i, i)
  FROM generate_series(0, 99) i \gexec
INSERT INTO part SELECT g, g % 100, 0 FROM generate_series(1, 100000) g;
CREATE INDEX ON part (id);
VACUUM ANALYZE plain, part;
CREATE FOREIGN TABLE f_plain (id int, grp int, v int)
  SERVER loopback OPTIONS (table_name 'plain');
CREATE FOREIGN TABLE f_part (id int, grp int, v int)
  SERVER loopback OPTIONS (table_name 'part');
SQL

median() {  # $1 = foreign table, $2 = rows
  for i in 1 2 3 4 5; do
    $PSQL -At -c '\timing on' \
      -c "UPDATE $1 SET v = v + 1 WHERE id <= $2 AND random() <= 1" 2>&1 |
      grep -oE 'Time: [0-9.]+' | cut -d' ' -f2
  done | sort -n | sed -n 3p
}
echo "== median of 5 runs (ms)"
for t in f_plain f_part; do
  for n in 1000 10000; do
    printf "   %-8s %5d rows  %10s\n" $t $n "$(median $t $n)"
  done
done

echo "== statements received by the remote side, 1000 rows on f_part"
$PSQL -c "ALTER SYSTEM SET log_min_duration_statement = 0" -c "SELECT pg_reload_conf()" >/dev/null
sleep 1
OFF=$(wc -l < $D/log)
$PSQL -c "UPDATE f_part SET v = v + 1 WHERE id <= 1000 AND random() <= 1" >/dev/null
tail -n +$((OFF + 1)) $D/log | perl -ne '
  next unless /duration: ([0-9.]+) ms\s+(\w+)[^:]*: (.*)/;
  my ($ms, $kind, $sql) = ($1, $2, $3);
  $sql =~ s/\s+/ /g;
  my $k = "$kind " . substr($sql, 0, 60);
  $tot{$k} += $ms; $cnt{$k}++;
  END {
    for my $k (sort { $tot{$b} <=> $tot{$a} } keys %tot) {
      printf "   %9.1f ms  x%-5d %s\n", $tot{$k}, $cnt{$k}, $k;
    }
  }' | head -6