Re: Optimization for updating foreign tables in Postgres FDW

On 17-04-2015 PM 12:35, Etsuro Fujita wrote:
> On 2015/04/17 10:23, Amit Langote wrote:
>> 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
> 
> Thanks for the suggestion!
> 
> I'm not sure that that is a good idea because (1) that is contrary to
> the reality (the update pushdown patch lets the ForeignScan nodes do
> UPDATE/DELETE RETURNING and then do nothing at ModifyTable!) and because

Ah, the reality is exactly the reverse then. Thanks for your patience.

> (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,
Amit