Re: Foreign join pushdown vs EvalPlanQual

Hello,

At Wed, 14 Oct 2015 03:07:31 +0000, Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote in <9A28C8860F777E439AA12E8AEA7694F801157077(at)BPXM15GP(dot)gisp(dot)nec(dot)co(dot)jp>
> > I noticed that the approach using a column to populate the foreign
> > scan's slot directly wouldn't work well in some cases. For example,
> > consider:
> >
> > SELECT * FROM verysmall v LEFT JOIN (bigft1 JOIN bigft2 ON bigft1.x =
> > bigft2.x) ON v.q = bigft1.q AND v.r = bigft2.r FOR UPDATE OF v;
> >
> > The best plan is presumably something like this as you said before:
> >
> > LockRows
> > -> Nested Loop
> > -> Seq Scan on verysmall v
> > -> Foreign Scan on bigft1 and bigft2
> > Remote SQL: SELECT * FROM bigft1 JOIN bigft2 ON bigft1.x =
> > bigft2.x AND bigft1.q = $1 AND bigft2.r = $2
> >
> > Consider the EvalPlanQual testing to see if the updated version of a
> > tuple in v satisfies the query. If we use the column in the testing, we
> > would get the wrong results in some cases.

I have a basic (or maybe silly) qustion. Is it true that the
join-inner (the foreignscan in the example) is re-executed with
the modified value of v.r? I observed for a join case among only
local tables that previously fetched tuples for the inner are
simplly reused regardless of join types. Even when a refetch
happens (I haven't confirmed but it would occur in the case of no
security quals), the tuple is pointed by ctid so the re-join
between local and remote would fail. Is this wrong?

> In this case, does ForeignScan have to be reset prior to ExecProcNode()?
> Once ExecReScanForeignScan() gets called by ExecNestLoop(), it marks EPQ
> slot is invalid. So, more or less, ForeignScan needs to kick the remote
> join again based on the new parameter come from the latest verysmall tuple.
> Please correct me, if I don't understand correctly.

So, no rescan would happen for the cases, I think. ReScan seems
to be kicked only for the new(next) outer tuple that causes
change of parameter, but not kicked for EPQ. I might take you
wrongly..

> In case of unparametalized ForeignScan case, the cached join-tuple work
> well because it is independent from verysmall.

> Once again, if FDW driver is responsible to construct join-tuple from
> the base relation's tuple cached in EPQ slot, this case don't need to
> kick remote query again, because all the materials to construct join-
> tuple are already held locally. Right?

It is definitely right and should be doable. But I think the
point we are argueing here is what is the desirable behavior.

regards,

--
Kyotaro Horiguchi
NTT Open Source Software Center