Re: Optimization for updating foreign tables in Postgres FDW

On 2015/04/21 10:07, Kyotaro HORIGUCHI wrote:
> At Mon, 20 Apr 2015 16:40:52 +0900, Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> wrote in
<5534AD84.3020501@lab.ntt.co.jp>
>> 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).

> Update node without "Foreign" that runs "Remote SQL" looks to me
> somewhat unusual..

I think that has a similarity with the existing EXPLAIN outputs for 
non-inherited non-pushed-down updates, as shown in the below exaple.
>> 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>>
-> Foreign Scan 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)
 

> It seems to me that the problem is "Foreign Update"s for
> ModifyTable that does nothing eventually.

> I think the ForeignUpdate nodes should
> be removed during planning if it is really ineffective,

> If removed it looks like,
>
> | =# explain verbose update p set b = b + 1;
> |                                   QUERY PLAN
> | ------------------------------------------------------------------------------
> |  Update on public.p  (cost=0.00..360.08 rows=4311 width=14)
> |    Update on public.p
> |    ->  Seq Scan on public.p  (cost=0.00..0.00 rows=1 width=14)
> |          Output: p.a, (p.b + 1), p.ctid
> |    ->  Foreign Update on public.ft1  (cost=100.00..180.04 rows=2155 width=14)
> |          Remote SQL: UPDATE public.t1 SET b = (b + 1)
> |    ->  Foreign Update on public.ft2  (cost=100.00..180.04 rows=2155 width=14)
> |          Remote SQL: UPDATE public.t2 SET b = (b + 1)

On that point, I agree with Tom that that would cause the problem that 
the user has to guess at which of the child plans goes with which target 
relation of ModifyTable [1].

Thanks for the comments!

Best regards,
Etsuro Fujita

[1] http://www.postgresql.org/message-id/22505.1426986174@sss.pgh.pa.us