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.

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