Re: [idea] more aggressive join pushdown on postgres_fdw

On Thu, Jun 4, 2015 at 9:40 PM, Kouhei Kaigai <kaigai@ak.jp.nec.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