Re: Optimization for updating foreign tables in Postgres FDW

Fujita-san,

On 16-04-2015 PM 08:40, Etsuro Fujita wrote:
>> From what I see in Tom's commit message[0] for FTI patch, this shouldn't be,
>> right?
>>
>> To be specific, there should be "Foreign Scan" there as per the commit. Am I
>> missing something?
>
> As shown in the below example, this patch doesn't change the EXPLAIN
> output for non-pushed-down update (delete) cases, but since we changed
> the EXPLAIN output as discussed in [1], the patch doubly displays
> "Foreign Update" ("Foreign Delete") for pushed-down update (delet) cases
> like the above example.
>
> 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
> Foreign Update on public.ft1
> Remote SQL: UPDATE public.t1 SET c1 = $2 WHERE ctid = $1
> Foreign 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'::double precision) +
> '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 on public.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)
>

I think I missed the point that you are talking about the result after the
patch for foreign udpate pushdown (which is the topic of this thread) has been
applied. Sorry about the noise.

By the way, one suggestion may be to attach a "(pushed down)" to the
ModifyTable's "Foreign Update". And in that case, there would be no mention of
corresponding scan node in the list below exactly because there would be none.

postgres=# explain verbose update parent set c1 = c1;
QUERY PLAN
------------------------------------------------------------------------------
Update on public.parent (cost=0.00..364.54 rows=4819 width=10)
Update on public.parent
Foreign Update (pushed down) on public.ft1
Foreign Update (pushed down) on public.ft2
-> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=10)
Output: parent.c1, parent.ctid

Thoughts?

Thanks,
Amit