Re: Optimization for updating foreign tables in Postgres FDW

(2014/09/13 0:13), Tom Lane wrote:
> Albe Laurenz <laurenz.albe@wien.gv.at> writes:
>> Tom Lane wrote:
>>> I'm not sure offhand what the new plan tree ought to look like.  We could
>>> just generate a ForeignScan node, but that seems like rather a misnomer.
>>> Is it worth inventing a new ForeignUpdate node type?  Or maybe it'd still
>>> be ForeignScan but with a flag field saying "hey this is really an update
>>> (or a delete)".  The main benefit I can think of right now is that the
>>> EXPLAIN output would be less strange-looking --- but EXPLAIN is hardly
>>> the only thing that ever looks at plan trees, so having an outright
>>> misleading plan structure is likely to burn us down the line.

> I was envisioning that the EXPLAIN output would look like
> 
>          Foreign Scan on tab1
>            Remote SQL: SELECT ...
> 
> for the normal case, versus
> 
>          Foreign Update on tab1
>            Remote SQL: UPDATE ...
> 
> for the pushed-down-update case (and similarly for DELETE).  For a
> non-optimized update it'd still be a ForeignScan underneath a ModifyTable.

> As for the internal representation, I was thinking of adding a CmdType
> field to struct ForeignScan, with currently only CMD_SELECT, CMD_UPDATE,
> CMD_DELETE as allowed values, though possibly in future we'd think of a
> reason to allow CMD_INSERT there.

+1

> The only thing that's bothering me about this concept is that I'm not
> seeing how to scale it up to handling a pushed-down update on a join,
> ie, "UPDATE foo ... FROM bar ..." where both foo and bar are remote.
> Maybe it's silly to worry about that until join push-down is done;
> but in that case I'd vote for postponing this whole patch until we
> have join push-down.

OK

Thanks,

PS: I'll help Hanada-san do the work if there is anything I can do.

Best regards,
Etsuro Fujita