Hi Ajit,
Ajit Awekar <[email protected]> wrote:
> @Manu: went with your option (1) rather than (2) for case_f
>
> Please find attached V3. Request a review.
Thanks, the new paragraph describes case_f as I saw it, including that
the remote tableoid is the middle foreign table's own OID.
I ran the same eight cases on V3, applied on master e8a3ee5b197, against
that master, both with --enable-cassert. V3 builds without warnings,
and make check (239) and postgres_fdw's check pass.
- Jakub's zero-column case with a BEFORE DELETE trigger no longer fails
the assertion; the output is the same as master's.
- Etsuro's DELETE ... USING case, the dropped and renamed columns with
triggers, the mixed local and foreign partitions and the self-join
give the right rows, as with v2.
- The zero-column RETURNING and the cross-partition cases are the same
as master.
- The chained case still updates the wrong rows, as documented.
Since the commit message mentions the extra column and WHERE clause for
every non-direct UPDATE/DELETE, I measured it, on a loopback server
with 100000 rows, UPDATE ... WHERE id <= N AND random() <= 1, median of 5
runs:
master V3
plain table, 1000 rows 26 ms 29 ms
plain table, 10000 rows 234 ms 224 ms
100 partitions, 1000 rows 42 ms 309 ms
100 partitions, 10000 rows 102 ms 3606 ms
On a plain table I can't see a difference. With partitions V3 looks
much slower, but it is not what it adds. With log_min_duration_statement
= 0, for 1000 rows master sends 100 per-row UPDATEs and V3 sends 1000,
at the same cost each (0.20 ms in one run, 0.25 ms in another, the same
for both). Master sends fewer because each
"WHERE ctid = $1" hits that ctid in every partition, so the rows already
changed are not fetched again. Master is faster only because it is
doing the wrong thing.
What remains is the cost your paragraph describes: each per-row UPDATE
goes through the root and scans every partition, so with 100 partitions
a row costs about ten times what it costs on a plain table (309 ms vs
29 ms for 1000 rows). Prepared on the remote side the way postgres_fdw
does it, "WHERE ctid = $1 AND tableoid = $2" costs about 10% more than
"WHERE ctid = $1" (190 vs 172 ms for 1000 executions), so the tableoid
condition itself is cheap.
The attached fdw_update_cost.sh does both measurements: the medians, and
the statements the remote side receives with their count and time. It
takes an install prefix and a port, like run_cases.sh. The numbers move
a bit between runs, but the counts don't.
I have nothing else to raise on V3.
Regards,
Manu
#!/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
