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.

—
Shigeru HANADA