On Fri, Aug 28, 2026 at 7:43 AM Ajit Awekar <[email protected]> wrote:
>
> Hi Nikita,
>
> I have added it in the commitfest:
> https://commitfest.postgresql.org/patch/7211/
>
> Requesting a review.
Hi Ajit,
respect for taking working such ambitious bug without proper solution since
2018. I've just tried playing devil's advocate and tried to test & break this
patch (I'm not that familiar with the optimizer guts changed (so findings are
written based mostly from performance and end user kind of angle).
First and most important, the patch fixes the logical corruption bug
reported and known since at least 2018, so that's good. Even without
solving below I think it's good.
Stuff that I've discovered (let's call those optimization opporutnities):
1. The patch adds ROW(), but then also duplicates the cols (here it is 4
column table), c1/c2/c3 are sent twice over the wire:
postgres=# explain verbose UPDATE fplt SET c2 = (CASE WHEN random() <= 1
THEN 10 ELSE 20 END) WHERE c1 = 1 AND c2 = 2 AND c3 = 3;
[..]
Remote SQL: SELECT ctid, tableoid, ROW(c1, c2, c3, c4), c1, c2, c3 FROM[..]
Do we need to transfer them twice over the network?
The ROW() seems to be pretty taxing in terms of CPU/time. Measuring just
using ROW(c1, ...) vs pure c1, c2, ... shows that it costs ~2x (in
statement duration) and it is sent also for non-partittioned tables
(because postgres_fdw does know if the non-part table is involved or
not). However to my measuremnet that does NOT seem to result in degraded
overall timing, because even if ROW() returns plenty of rows (and gets
regression in timing) that's tiny cost compared to issuing all those
UPDATEs with binds for each of those fetched and then updated rows.
The only problem I is that indirect UPDATEs on all postgres_fdw foreign
tables are affected by this. So maybe this should be somhow called out in
the docs or commit more loudly? (I take no logical corruption over
performance any day).
2. If I create 500 partitions it will significatnly show how we lack partition
prunning based on the "tableoid" special column:
DROP TABLE plt;
DROP FOREIGN TABLE fplt;
CREATE TABLE plt (c1 int, c2 int) PARTITION BY RANGE (c1);
SELECT format('CREATE TABLE plt%s PARTITION OF plt FOR VALUES
FROM(%s)TO(%s)', i, 10*(i-1), 10*i) FROM generate_series(1,500) i;
\gexec
INSERT INTO plt SELECT MOD(s, 5000-1), s
FROM generate_series(1, 5000000) s;
ANALYZE plt;
CREATE FOREIGN TABLE fplt (c1 int, c2 int) SERVER loopback
OPTIONS (table_name 'plt');
explain verbose
UPDATE fplt SET c2 = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END)
WHERE c1 = 1;
Update on public.fplt
Remote SQL: UPDATE public.fplt SET c2 = $3 WHERE ctid = $1 AND tableoid =$2
-> Foreign Scan on public.fplt
Output: CASE WHEN (random() <= '1'::double precision) THEN 10 [..]
Remote SQL: SELECT ctid, tableoid, ROW(c1, c2), c1 FROM public.fplt
WHERE ((c1 = 1)) FOR UPDATE
and we are going to execute apparently around 1k UPDATEs like that (because
that's hhow many values we have for c1 = 1):
LOG: execute pgsql_fdw_prep_3: UPDATE public.plt SET c2 = $3
WHERE ctid = $1 AND tableoid = $2
DETAIL: Parameters: $1 = '(26,15)', $2 = '24246', $3 = '10'
LOG: execute pgsql_fdw_prep_3: UPDATE public.plt SET c2 = $3
WHERE ctid = $1 AND tableoid = $2
DETAIL: Parameters: $1 = '(26,25)', $2 = '24246', $3 = '10'
[..]
and those cannot benefit from partition prunning at all, e.g. (but also
explain analyze too):
postgres=# explain (verbose, generic_plan) UPDATE public.plt SET c2 = $3
WHERE ctid = $1 AND tableoid = $2 ;
QUERY PLAN
--------------------------------------------------------------------------
Update on public.plt (cost=0.00..2010.00 rows=0 width=0)
Update on public.plt1 plt_1
Update on public.plt2 plt_2
Update on public.plt3 plt_3
[..]
Update on public.plt500 plt_500
-> Append (cost=0.00..2010.00 rows=500 width=14)
-> Tid Scan on public.plt1 plt_1 (cost=0.00..4.02 rows=1 width=14)
Output: $3, plt_1.tableoid, plt_1.ctid
TID Cond: (plt_1.ctid = $1)
Filter: (plt_1.tableoid = $2)
-> Tid Scan on public.plt2 plt_2 (cost=0.00..4.02 rows=1 width=14)
Output: $3, plt_2.tableoid, plt_2.ctid
TID Cond: (plt_2.ctid = $1)
Filter: (plt_2.tableoid = $2)
[..]
Of course the more such individual rows (R) will be returned by the SELECT
to be updated, and the more partitions (P) there the worse the problem(R*P)
EXPLAIN ANALYZE for it here shows that with 500 partitions I get single
"local" UPDATE costs around this per single updated row:
Planning Time: 8.786 ms
Execution Time: 3.873 ms
This patch could at least add some single statement in documentation that
there is current known performance inhibitor. Currently docs have
"F.38.5.Remote Query Optimization" and we could simply document it as known
limitation that complex (indirect?) UPDATES/DELETing referencing
postgres_fdw tables should limit themselves to modifing low number of
rows in case of refering to remote root of partition, because issued
UPDATE/DELETE may not prune optimally based on "tableoid" (but that might
be subject to change?).
3. If there's type mismatch between foreign table and real remote
table like below:
DROP TABLE terr;
DROP FOREIGN TABLE fterr;
CREATE TABLE terr (id int, a text);
INSERT INTO terr VALUES (1, 'sometext');
-- a is int, but should be "ext"
CREATE FOREIGN TABLE fterr (id int, a int)
SERVER loopback OPTIONS (table_name 'terr');
UPDATE fterr SET a = (CASE WHEN random() <= 1 THEN 10 ELSE 20 END)
WHERE id = 1 AND random() <= 1;
we get with the patch:
ERROR: invalid input syntax for type integer: "sometext"
CONTEXT: whole-row reference to foreign table "fterr"
(this is cast failure from our postgres_fdw side, try guessing which column
is wrong with hundreths of those!), but without the patch it was way
better then:
ERROR: invalid input syntax for type integer: "sometext"
CONTEXT: column "a" of foreign table "fterr"
No idea if that can be fixed.
4. Nested loop involving foreign scan with this ROW() patch seems to be
adding to transfer something additional that is not necessary ($1)?
Given:
DROP TABLE big;
DROP TABLE tiny;
DROP FOREIGN TABLE fbig;
CREATE TABLE big (id int primary key, a int, b text);
INSERT INTO big SELECT s, s, 'x'||s FROM generate_series(1,200000) s;
ANALYZE big;
CREATE FOREIGN TABLE fbig (id int, a int, b text) SERVER loopback
OPTIONS (table_name 'big', use_remote_estimate 'true');
CREATE TABLE tiny (id int primary key);
INSERT INTO tiny VALUES (1234);
ANALYZE tiny, fbig;
SET enable_hashjoin = off;
SET enable_mergejoin = off;
The fbig.b like '%' below is just to highlight where stuff is put inside
the complex remote SQL:
postgres=# explain (costs off, verbose) UPDATE fbig SET b = fbig.b||'!'
FROM tiny WHERE fbig.id = tiny.id AND random() <= 1 AND fbig.b like '%';
Update on public.fbig
Remote SQL: UPDATE public.big SET b = $3 WHERE ctid = $1 AND tableoid = $2
->Nested Loop
Output: (fbig.b || '!'::text), fbig.ctid, remotetableoid, fbig.*,
tiny.ctid
Join Filter: (random() <= '1'::double precision)
-> Seq Scan on public.tiny
Output: tiny.ctid, tiny.id
-> Foreign Scan on public.fbig
Output: fbig.b, fbig.ctid, remotetableoid, fbig.*, fbig.id
Remote SQL: SELECT b, ctid, tableoid, ROW(id, a, b), id, $1::integer
FROM public.big WHERE ((b ~~ '%')) AND ((id = $1::integer))
FOR UPDATE
I understand the "WHERE id = $1::integer" (it's value from "tiny" passed),
but why we are asking remote to return back the same $1:integer we are
asking for? Exactly I'm asking for this:
Remote SQL: SELECT b, ctid, tableoid, ROW(id, a, b), id, >> $1::integer <<
This somehow works fine, because $1 is somehow amazingly passed correctly by
the executor (Parameters: $1 = '1234') see below:
[112269] LOG: execute <unnamed>: DECLARE c1 CURSOR FOR
SELECT b, ctid, tableoid, ROW(id, a, b), id, $1::integer FROM
public.big WHERE ((b ~~ '%')) AND ((id = $1::integer)) FOR UPDATE
[112269] DETAIL: Parameters: $1 = '1234'
[112269] LOG: statement: FETCH 100 FROM c1
[..]
but isn't that unnecessary leak? (if that join condition would be huge and
repeated milion times, why we do we need to transfer it back with every
returned row?). Without patch it is not happening.
-J.
