Re: Custom/Foreign-Join-APIs (Re: [v9.5] Custom Plan API)

> 2015/03/25 19:09、Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com> のメール：
>
> >> On Wed, Mar 25, 2015 at 3:14 PM, Shigeru HANADA <shigeru(dot)hanada(at)gmail(dot)com>
> wrote:
> >> Or bottom of make_join_rel(). IMO build_join_rel() is responsible for
> >> just building (or searching from a list) a RelOptInfo for given relids. After
> >> that make_join_rel() calls add_paths_to_joinrel() with appropriate arguments
> per
> >> join type to generate actual Paths implements the join. make_join_rel() is
> >> called only once for particular relid combination, and there SpecialJoinInfo
> and
> >> restrictlist (conditions specified in JOIN-ON and WHERE), so it seems promising
> >> for FDW cases.
> >>
> >>
> >>
> >> I like that idea, but I think we will have complex hook signature, it won't
> remain
> >> as simple as hook (root, joinrel).
> >>
> > In this case, GetForeignJoinPaths() will take root, joinrel, rel1, rel2,
> > sjinfo and restrictlist.
> > It is not too simple, but not complicated signature.
> >
> > Even if we reconstruct rel1 and rel2 using sjinfo, we also need to compute
> > restrictlist using build_joinrel_restrictlist() again. It is a static function
> > in relnode.c. So, I don't think either of them has definitive advantage from
> > the standpoint of simplicity.
>
> The bottom of make_join_rel() seems good from the viewpoint of information, but
> it is called multiple times for join combinations which are essentially identical,
> for INNER JOIN case like this:
>
> fdw=# explain select * from pgbench_branches b join pgbench_tellers t on t.bid
> = b.bid join pgbench_accounts a on a.bid = b.bid and a.bid = t.bid;
> INFO: postgresGetForeignJoinPaths() 1x2
> INFO: postgresGetForeignJoinPaths() 1x4
> INFO: postgresGetForeignJoinPaths() 2x4
> INFO: standard_join_search() old hook point
> INFO: standard_join_search() old hook point
> INFO: standard_join_search() old hook point
> INFO: postgresGetForeignJoinPaths() 0x4
> INFO: postgresGetForeignJoinPaths() 0x2
> INFO: postgresGetForeignJoinPaths() 0x1
> INFO: standard_join_search() old hook point
> QUERY PLAN
> ---------------------------------------------------------
> Foreign Scan (cost=100.00..102.11 rows=211 width=1068)
> (1 row)
>
> Here I’ve put probe point in the beginnig of GetForeignJoinPaths handler and just
> before set_cheapest() call in standard_join_search() as “old hook point”. In
> this example 1, 2, and 4 are base relations, and in the join level 3 planner calls
> GetForeignJoinPaths() three times for the combinations:
>
> 1) (1x2)x4
> 2) (1x4)x2
> 3) (2x4)x1
>
> Tom’s suggestion is aiming at providing a chance to consider join push-down in
> more abstract level, IIUC. So it would be good to call handler only once for
> that case, for flattened combination (1x2x3).
>
> Hum, how about skipping calling handler (or hook) if the joinrel was found by
> find_join_rel()? At least it suppress redundant call for different join orders,
> and handler can determine whether the combination can be flattened by checking
> that all RelOptInfo with RELOPT_JOINREL under joinrel has JOIN_INNER as jointype.
>
The reason why FDW handler was called multiple times on your example is,
your modified make_join_rel() does not check whether build_join_rel()
actually build a new RelOptInfo, or just a cache reference, doesn't it?

If so, I'm inclined to your proposition.
A new "bool *found" argument of build_join_rel() makes reduce number of
FDW handler call, with keeping reasonable information to build remote-
join query.

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>