Re: Optimization for updating foreign tables in Postgres FDW

On Wed, Mar 9, 2016 at 1:12 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Overall, I think this is looking pretty good.
>
> I hadn't been paying any attention to this thread, but I wonder whether
> this entire approach isn't considerably inferior to what we can do now
> with the planner pathification patch. To quote from the new docs:
>
> PlanForeignModify and the other callbacks described in Section 54.2.3
> are designed around the assumption that the foreign relation will be
> scanned in the usual way and then individual row updates will be driven
> by a local ModifyTable plan node. This approach is necessary for the
> general case where an update requires reading local tables as well as
> foreign tables. However, if the operation could be executed entirely by
> the foreign server, the FDW could generate a path representing that and
> insert it into the UPPERREL_FINAL upper relation, where it would
> compete against the ModifyTable approach. This approach could also be
> used to implement remote SELECT FOR UPDATE, rather than using the row
> locking callbacks described in Section 54.2.4. Keep in mind that a path
> inserted into UPPERREL_FINAL is responsible for implementing all
> behavior of the query.
>
> I don't really see anything that this patch does that wouldn't be better
> done that way. And I'd kind of like to get a working example of that type
> of path insertion into 9.6, so that we can find out if we need any hooks
> or callbacks that aren't there today.

Well, I guess I'm not quite seeing it. What do you have in mind?
Just taking a guess here, you might be thinking that instead of
something like this...

Update on public.ft2
-> Foreign Update on public.ft2

We could boil it all the way down to this:

Foreign Update on public.ft2

But can you, really? What if the UPDATE is targeting an inheritance
hierarchy containing some local tables and some remote tables?

Apologies if I've completely misunderstood what you have in mind here,
but you haven't really explained what you have in mind here.

IMHO, if you want to do something really cool with the new
pathification stuff, the thing to do would be pushing down aggregates
to foreign servers. A lot of people would be really happy to see
SELECT count(*) FROM ft ship the count operation to the remote side!

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company