Re: [idea] more aggressive join pushdown on postgres_fdw

> On Thu, Jun 4, 2015 at 9:40 PM, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:
> >> Neat idea. This ties into something I've thought about and mentioned
> >> before: what if the innerrel is local, but there's a replicated copy
> >> on the remote server? Perhaps both cases are worth thinking about at
> >> some point.
> >>
> > I think, here is both merit and de-merit for each. It implies either of
> > them never always-better-strategy.
> >
> > * Push out local table as VALUES(...) clause
> > Good: No restriction to functions/operators in the local scan or
> > underlying plan node.
> > Bad: High cost for data format modification (HeapTupleSlot =>
> > VALUES(...) clause in text), and 2-way data transfer.
> >
> > * Remote join between foreign table and replicated table
> > Good: Data already exists on remote side, no need to kick out
> > contents of local relation (and no need to consume CPU
> > cycle to make VALUES() clause).
> > Bad: Functions/operators are restricted as existing postgres_fdw
> > is doing. Only immutable and built-in ones are available to
> > run on the remote side.
>
> Sure.
>
> > BTW, do we need either of tables being foreign table, if entire database
> > is (synchronously) replicated?
> > Also, loopback server may be a candidate even if not replicated (although
> > it may be an entrance of deadlock heaven).
>
> I suppose it's possible that this sort of thing could work out to a
> win, but I think it's much less likely to work out than pushing down a
> foreign/local join using either the VALUES trick or a replicated copy.
>
Hmm, it might be too aggressive approach.
If we would try to implement, postgres_fdw will need to add so many junk
paths (expensive than usual local ones) to consider remote join between
replicated local tables.

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>