Re: Optimization for updating foreign tables in Postgres FDW

On 2015/04/16 19:57, Amit Langote wrote:
> On 16-04-2015 PM 07:50, Etsuro Fujita wrote:
>>> The EXPLAIN output has also been improved as discussed in [1].
>>
>> I noticed that the EXPLAIN for a pushed-down update (delete) on inheritance
>> childs doubly displays "Foreign Update" ("Foreign Delete"), one for
>> ForeignScan and the other for ModifyTable.  Here is an example:
>>
>> 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 on public.ft1
>>     Foreign Update on public.ft2
>>     ->  Seq Scan on public.parent  (cost=0.00..0.00 rows=1 width=10)
>>           Output: parent.c1, parent.ctid
>>     ->  Foreign Update on public.ft1  (cost=100.00..182.27 rows=2409 width=10)
>>           Remote SQL: UPDATE public.t1 SET c1 = c1
>>     ->  Foreign Update on public.ft2  (cost=100.00..182.27 rows=2409 width=10)
>>           Remote SQL: UPDATE public.t2 SET c1 = c1
>> (10 rows)
>>
>> Should we do something?  Suggestions are welcome.

>>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 =
$2WHERE 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)

Best regards,
Etsuro Fujita