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

Hi KaiGai-san,

On 2015/07/02 18:31, Kouhei Kaigai wrote:
> Even though I'd like to see committer's opinion, I could not come up
> with the idea better than what you proposed; foreign-/custom-scan
> has alternative plan if scanrelid==0.

I'd also like to hear the opinion!

> Let me introduce a few cases we should pay attention.
>
> Foreign/CustomScan node may stack; that means a Foreign/CustomScan node
> may have child node that includes another Foreign/CustomScan node with
> scanrelid==0.
> (At this moment, ForeignScan cannot have child node, however, more
> aggressive push-down [1] will need same feature to fetch tuples from
> local relation and construct VALUES() clause.)
> In this case, the highest Foreign/CustomScan node (that is also nearest
> to LockRows or ModifyTuples) run the alternative sub-plan that includes
> scan/join plans dominated by fdw_relids or custom_relids.
>
> For example:
>    LockRows
>     -> HashJoin
>       -> CustomScan (AliceJoin)
>         -> SeqScan on t1
>         -> CustomScan (CarolJoin)
>           -> SeqScan on t2
>           -> SeqScan on t3
>       -> Hash
>         -> CustomScan (BobJoin)
>           -> SeqScan on t4
>           -> ForeignScan (remote join involves ft5, ft6)
>
> In this case, AliceJoin will have alternative sub-plan to join t1, t2
> and t3, then it shall be used on EvalPlanQual(). Also, BobJoin will
> have alternative sub-plan to join t4, ft5 and ft6. CarolJoin and the
> ForeignScan will also have alternative sub-plan, however, these are
> not used in this case.
> Probably, it works fine.

Yeah, I think so too.

> Do we have potential scenario if foreign-/custom-join is located over
> LockRows node. (Subquery expansion may give such a case?)
> Anyway, doesn't it make a problem, does it?

IIUC, I don't think we have such a case.

> On the next step, how do we implement this design?
> I guess that planner needs to keep a path that contains neither
> foreign-join nor custom-join with scanrelid==0.
> Probably, "cheapest_builtin_path" of RelOptInfo is needed that
> never contains these remote/custom join logic, as a seed of
> alternative sub-plan.

Yeah, I think so too, but I've not fugiured out how to implement this yet.

To be honest, ISTM that it's difficult to do that simply and efficiently 
for the foreign/custom-join-pushdown API that we have for 9.5.  It's a 
little late, but what I started thinking is to redesign that API so that 
that API is called at standard_join_search, as discussed in [2]; (1) to 
place that API call *after* the set_cheapest call and (2) to place 
another set_cheapest call after that API call for each joinrel.  By the 
first set_cheapest call, I think we could probably save an alternative 
path that we need in "cheapest_builtin_path".  By the second 
set_cheapest call following that API call, we could consider 
foreign/custom-join-pushdown paths also.  What do you think about this idea?

> create_foreignscan_plan() or create_customscan_plan() will be
> able to construct these alternative plan, regardless of the
> extensions. So, individual FDW/CSP don't need to care about
> this alternative sub-plan, do it?
>
> After that, once ExecScanFetch() is called under EvalPlanQual(),
> these Foreign/CustomScan with scanrelid==0 runs the alternative
> sub-plan, to validate the latest tuple.
>
> Hmm... It looks to me a workable approach.

Year, I think so too.

> Fujita-san, are you available to make a patch with this approach?
> If so, I'd like to volunteer its reviewing.

Yeah, I'm willing to make a patch if we obtain the consensus!  And I'd 
be happy if you help me doing the work!

Best regards,
Etsuro Fujita

[2] http://www.postgresql.org/message-id/5451.1426271510@sss.pgh.pa.us