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

> -----Original Message-----
> From: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Kyotaro HORIGUCHI
> Sent: Friday, October 02, 2015 9:50 AM
> To: Kaigai Kouhei(海外 浩平)
> Cc: fujita.etsuro@lab.ntt.co.jp; robertmhaas@gmail.com;
> pgsql-hackers@postgresql.org; shigeru.hanada@gmail.com
> Subject: Re: [HACKERS] Foreign join pushdown vs EvalPlanQual
>
> Hello, I had more condieration on this.
>
> > As long as FDW author can choose their best way to produce a joined
> > tuple, it may be worth to investigate.
> >
> > My comments are:
> > * ForeignRecheck is the best location to call RefetchForeignJoinRow
> >   when scanrelid==0, not ExecScanFetch. Why you try to add special
> >   case for FDW in the common routine.
> > * It is FDW's choice where the remote join tuple is kept, even though
> >   most of FDW will keep it on the private field of ForeignScanState.
>
> I think that scanrelid == 0 means that the node in focus is not a
> scan node in current executor
> semantics. EvalPlanQualFetchRowMarks fetches the possiblly
> modified row then EvalPlanQualNext does recheck for the new
> row. It's the roles of each functions.
>
> In this criteria, recheck routines are not the place for
> refetching.  EvalPlanQualFetchRowMarks is that.
>
I never say FDW should refetch tuples on the recheck routine.
All I suggest is, projection to generate a joined tuple and
recheck according to the qualifier pushed down are role of
FDW driver, because it knows the best strategy to do the job.

> Again, the problem here is that "foreign join" scan node is
> actually a scan node but it doesn't provide all materials which
> executor expects for a scan node. So the way to fix this
> preserving the semantics would be in two choices.
>
> 1. make "foreign join" scan node to behave as complete scan
>    node. That is, EvalPlanQualFetchRowMarks can retrieve the
>    modified row version anyhow according to the type of row mark.
>
> 2. make "foreign join" node that the node actuall a join node
>    which has subnodes and the "foreign join" node can reconstruct
>    the result row using the result of subnodes on EPQ.
>    (ExecForeignJoinNode would cease to call subnodes if it is
>     actually a scan node)
>
> "3". Any other means to break current semantics of joins and
>   scans in executor, as you recommends. Some more adjustment
>   would be needed to go on this way.
>
>
> I don't know how the current disign of FDW has been built,
> especialy about join pushdown feature so I should be missing
> something but I think as the above for this issue.
>
It looks to me all of them makes the problem complicated more.
I never heard why "foreign-join" scan node is difficult to construct
a joined tuple using the EPQ slots that are already loaded on.

Regardless of the early or late locking, EPQ slots of base relation
are already filled up, aren't it?

All mission of the "foreign-join" scan node is return a joined
tuple as if it was executed by local join logic.
Local join consumes two tuples then generate one tuple.
The "foreign-join" scan node can perform equivalently, even if it
is under EPQ recheck context.

So, job of FDW driver is...
Step-1) Fetch tuples from the EPQ slots of the base foreign relation       to be joined. Please note that it is just a
pointerreference. 
Step-2) Try to join these two (or more) tuples according to the       join condition (only FDW knows because it is kept
inprivate) 
Step-3) If result is valid, FDW driver makes a projection from these       tuples, then return it.

If you concern about re-invention of the code for each FDW, core
can provide a utility routine to cover 95% of FDW structure.

I want to keep EvalPlanQualFetchRowMarks per base relation basis.
It is a bad choice to consider join at this point.

> > Apart from FDW requirement, custom-scan/join needs recheckMtd is
> > called when scanrelid==0 to avoid assertion fail. I hope FDW has
> > symmetric structure, however, not a mandatory requirement for me.
>
> It wouldn't be needed if EvalPlanQualFetchRowMarks works as
> exepcted. Is this wrong?
>
Yes, it does not work.
Expected behavior EvalPlanQualFetchRowMarks is to load the tuple
to be rechecked onto EPQ slot, using heap_fetch or copied image.
It is per base relation basis.

Who can provide a projection to generate joined tuple?
It is a job of individual plan-state-node to be walked on during
EvalPlanQualNext().

Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei <kaigai@ak.jp.nec.com>

> regards,
>
>
> At Thu, 1 Oct 2015 13:17:34 +0000, Kouhei Kaigai <kaigai@ak.jp.nec.com> wrote
> in <9A28C8860F777E439AA12E8AEA7694F80114D7BB@BPXM15GP.gisp.nec.co.jp>
> > > > In case when FDW is not designed to handle join by itself, it is
> > > > a reasonable fallback I think.
> > > >
> > > > I expect FDW driver needs to handle EPQ recheck in the case below:
> > > > * ForeignScan on base relation and it uses late row locking.
> > > > * ForeignScan on join relation, even if early locking.
> > >
> > > I also think the approach would be one choice.  But one thing I'm
> > > concerned about is plan creation for that by the FDW author; that would
> > > make life hard for the FDW author.  (That was proposed by me ...)
> > >
> > I don't follow the standpoint, but not valuable to repeat same discussion.
> >
> > > So, I'd like to investigate another approach that preserves the
> > > applicability of late row locking to the join pushdown case as well as
> > > the spirit of what's there now.  The basic idea is (1) add a new
> > > callback routine RefetchForeignJoinRow that refetches one foreign-join
> > > tuple from the foreign server, after locking remote tuples for the
> > > component foreign tables if required, and (2) call that routine in
> > > ExecScanFetch if the target scan is for a foreign join and the component
> > > foreign tables require to be locked lately, else just return the
> > > foreign-join tuple stored in the parent's state tree, which is the tuple
> > > mentioned by Robert, for preserving the spirit of what's there now. I
> > > think that ExecLockRows and EvalPlanQualFetchRowMarks should probably be
> > > modified so as to skip foreign tables involved in a foreign join.
> > >
> > As long as FDW author can choose their best way to produce a joined
> > tuple, it may be worth to investigate.
> >
> > My comments are:
> > * ForeignRecheck is the best location to call RefetchForeignJoinRow
> >   when scanrelid==0, not ExecScanFetch. Why you try to add special
> >   case for FDW in the common routine.
> > * It is FDW's choice where the remote join tuple is kept, even though
> >   most of FDW will keep it on the private field of ForeignScanState.
> >
> > Apart from FDW requirement, custom-scan/join needs recheckMtd is
> > called when scanrelid==0 to avoid assertion fail. I hope FDW has
> > symmetric structure, however, not a mandatory requirement for me.
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers