Re: Optimization for updating foreign tables in Postgres FDW

On 2015/04/17 13:16, Amit Langote wrote:
> On 17-04-2015 PM 12:35, Etsuro Fujita wrote:
>> (2) that might cause the problem of associating subplans' update
>> information with subplans' scan information, pointed out by Tom [1].

> Having realized how it really works now, my +1 to "Foreign Modifying Scan" for
> cases of pushed down update as suggested by Albe Laurenz. I guess it would be
> signaled by the proposed ForeignScan.CmdType being CMD_UPDATE / CMP_UPDATE
> (/CMD_INSERT).

Thanks for the opinion!  I think that that is an idea.  However, I'd
like to propose to rename "Foreign Update" ("Foreign Delete") of
ModifyTable simply to "Update" ("Delete") not only because (1) that
solves the duplication problem but also because (2) ISTM that is
consistent with the non-inherited updates in both of the
non-pushed-down-update case and the pushed-down-update case.  Here are
examples for (2).

* Inherited and non-inherited updates for the non-pushed-down case:

postgres=# explain verbose update parent set c1 = trunc(random() * 9 +
1)::int;                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------Update on
public.parent (cost=0.00..452.06 rows=5461 width=6)  Update on public.parent  Update on public.ft1    Remote SQL:
UPDATEpublic.t1 SET c1 = $2 WHERE ctid = $1  Update on public.ft2    Remote SQL: UPDATE public.t2 SET c1 = $2 WHERE
ctid= $1  ->  Seq Scan on public.parent  (cost=0.00..0.01 rows=1 width=6)        Output: (trunc(((random() *
'9'::doubleprecision) +
 
'1'::double precision)))::integer, parent.ctid  ->  Foreign Scan on public.ft1  (cost=100.00..226.03 rows=2730 width=6)
      Output: (trunc(((random() * '9'::double precision) +
 
'1'::double precision)))::integer, ft1.ctid        Remote SQL: SELECT ctid FROM public.t1 FOR UPDATE  ->  Foreign Scan
onpublic.ft2  (cost=100.00..226.03 rows=2730 width=6)        Output: (trunc(((random() * '9'::double precision) +
 
'1'::double precision)))::integer, ft2.ctid        Remote SQL: SELECT ctid FROM public.t2 FOR UPDATE
(14 rows)

postgres=# explain verbose update ft1 set c1 = trunc(random() * 9 + 1)::int;
QUERY PLAN
 
------------------------------------------------------------------------------------------------------Update on
public.ft1 (cost=100.00..226.03 rows=2730 width=6)  Remote SQL: UPDATE public.t1 SET c1 = $2 WHERE ctid = $1  ->
ForeignScan on public.ft1  (cost=100.00..226.03 rows=2730 width=6)        Output: (trunc(((random() * '9'::double
precision)+
 
'1'::double precision)))::integer, ctid        Remote SQL: SELECT ctid FROM public.t1 FOR UPDATE
(5 rows)

* Inherited and non-inherited updates for the pushed-down case:

postgres=# explain verbose update parent set c1 = c1 + 1;                                 QUERY PLAN
------------------------------------------------------------------------------Update on public.parent
(cost=0.00..376.59rows=4819 width=10)  Update on public.parent  Update on public.ft1  Update on public.ft2  ->  Seq
Scanon public.parent  (cost=0.00..0.00 rows=1 width=10)        Output: (parent.c1 + 1), parent.ctid  ->  Foreign Update
onpublic.ft1  (cost=100.00..188.29 rows=2409
 
width=10)        Remote SQL: UPDATE public.t1 SET c1 = (c1 + 1)  ->  Foreign Update on public.ft2  (cost=100.00..188.29
rows=2409
width=10)        Remote SQL: UPDATE public.t2 SET c1 = (c1 + 1)
(10 rows)

postgres=# explain verbose update ft1 set c1 = c1 + 1;                                 QUERY PLAN
------------------------------------------------------------------------------Update on public.ft1
(cost=100.00..188.29rows=2409 width=10)  ->  Foreign Update on public.ft1  (cost=100.00..188.29 rows=2409
 
width=10)        Remote SQL: UPDATE public.t1 SET c1 = (c1 + 1)
(3 rows)

Comments are welcome.

Best regards,
Etsuro Fujita