Re: Foreign join pushdown vs EvalPlanQual - Mailing list pgsql-hackers

Hi KaiGai-san,

On 2015/06/27 21:09, Kouhei Kaigai wrote:
>>> BTW, if you try newer version of postgres_fdw foreign join patch,
>>> please provide me to reproduce the problem/

>> OK

> Did you forget to attach the patch, or v17 is in use?

Sorry, I made a mistake.  The problem was produced using v16 [1].

>>> I'd like to suggest a solution that re-construct remote tuple according
>>> to the fdw_scan_tlist on ExecScanFetch, if given scanrelid == 0.
>>> It enables to run local qualifier associated with the ForeignScan node,
>>> and it will also work for the case when tuple in es_epqTupleSet[] was
>>> local heap.

>> Maybe I'm missing something, but I don't think your proposal works
>> properly because we don't have any component ForeignScan state node or
>> subsidiary join state node once we've replaced the entire join with the
>> ForeignScan performing the join remotely, IIUC.  So, my image was to
>> have another subplan for EvalPlanQual as well as the ForeignScan, to do
>> the entire join locally for the component test tuples if we are inside
>> an EvalPlanQual recheck.

> Hmm... Probably, we have two standpoints to tackle the problem.
>
> The first standpoint tries to handle the base foreign table as
> a prime relation for locking. Thus, we have to provide a way to
> fetch a remote tuple identified with the supplied ctid.
> The advantage of this approach is the way to fetch tuples from
> base relation is quite similar to the existing form, however,
> its disadvantage is another side of the same coin, because the
> ForeignScan node with scanrelid==0 (that represents remote join
> query) may have local qualifiers which shall run on the tuple
> according to fdw_scan_tlist.

IIUC, I think this approach would also need to evaluate join conditions 
and remote qualifiers in addition to local qualifiers in the local, for 
component tuples that were re-fetched from the remote (and remaining 
component tuples that were copied from whole-row vars, if any), in cases 
where the re-fetched tuples were updated versions of those tuples rather 
than the same versions priviously obtained.

> One other standpoint tries to handle a bunch of base foreign
> tables as a unit. That means, if any of base foreign table is
> the target of locking, it prompts FDW driver to fetch the latest
> "joined" tuple identified by "ctid", even if this join contains
> multiple base relations to be locked.
> The advantage of this approach is that we can use qualifiers of
> the ForeignScan node with scanrelid==0 and no need to pay attention
> of remote qualifier and/or join condition individually.
> Its disadvantage is, we may extend EState structure to keep the
> "joined" tuples, in addition to es_epqTupleSet[].

That is an idea.  However, ISTM there is another disadvantage; that is 
not efficient because that would need to perform another remote join 
query having such additional conditions during an EvalPlanQual check, as 
you proposed.

> I'm inclined to think the later standpoint works well, because
> it does not need to reproduce an alternative execution path in
> local side again, even if a ForeignScan node represents much
> complicated remote query.
> If we would fetch tuples of individual base relations, we need
> to reconstruct identical join path to be executed on remote-
> side, don't it?

Yeah, that was my image for fixing this issue.

> IIUC, the purpose of EvalPlanQual() is to ensure the tuples to
> be locked is still visible, so it is not an essential condition
> to fetch base tuples individually.

I think so too, but taking the similarity and/or efficiency of 
processing into consideration, I would vote for the idea of having an 
alternative execution path in the local.  That would also allow FDW 
authors to write the foreign join pushdown functionality in their FDWs 
by smaller efforts.

Best regards,
Etsuro Fujita

[1] 
http://www.postgresql.org/message-id/CAEZqfEe9KGy=1_waGh2rgZPg0o4pqgD+iauYaj8wTze+CYJUHg@mail.gmail.com