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(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <5534AD84(dot)3020501(at)lab(dot)ntt(dot)co(dot)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