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

a) Both outer and inner relations can be pushed down (NULL in RelOptInfo#fdw_private indicates such situation)
b) Outmost command is a SELECT (this can be relaxed in the future)
c) Join type is inner or one of outer
d) Server of all relations in the join are identical
e) Effective user id for all relations in the join are identical (they might be different some were accessed via views)
f) No local filters (this can be relaxed if inner && non-volatile)
g) Join conditions doesn't contain any "unsafe" expression
h) Remote filter doesn't contain any "unsafe" expression

If all criteria passed, postgres_fdw makes ForeignPath for the join and store these information in its fdw_private.

a) ForeignPath of outer relation, first non-parameterized one
b) ForeignPath of outer relation, first non-parameterized one
c) join type (as integer)
d) join conditions (as List of Expr)
e) other conditions (as List of Expr)

As of now the costs of the path is not so accurate, this is a possible enhancement.

2) Optimizer calls postgresGetForeignPlan() for the cheapest topmost Path.  If foreign join is the cheapest way to execute the query, optimizer calls postgresGetForeignPlan for the topmost path generated by postgresGetForeignJoinPaths.  As Robert and Tom mentioned in the thread, large_table JOIN huge_table might be removed even (large_table JOIN huge_table) JOIN small_table is the cheapest in the join level 3, so postgres_fdw can't assume that paths in lower level survived planning.

To cope with the situation, I'm trying to avoid calling create_plan_recurse() for underlying paths by putting necessary information into PgFdwRelationInfo and link it to appropriate RelOptInfo.

Anyway in current version query string is built up from bottom (BASEREL) to upper recursively.  For a join, unerlying outer/inner query are put into FROM clause with wrapping with parenthesis and aliasing.  For example:

select * from pgbench_branches b join pgbench_tellers t on t.bid = b.bid;

is transformed to a query like this:

SELECT l.a1, l.a2, l.a3, r.a1, r.a2, r.a3, r.a4 FROM (SELECT bid a9, bbalance a10, filler a11 FROM public.pgbench_branches) l (a1, a2, a3) INNER JOIN (SELECT tid a9, bid a10, balance a11, filler a12 FROM public.pgbench_tellers) r (a1, a2, a3, a4) ON ((l.a1 = r.a2));

As in the remote query, column aliasing uses attnum-based numbering with shifted by FirstLowInvalidHeapAttributeNumber to make all attnum positive.  For instance, this system uses alias "a9" for the first user column.  For readability of code around this, I introduced TO_RELATEVE() macro which converts absolute attnum (-8~) to relative ones (0~).  Current deparser can also handle whole-row references (attnum == 0) correctly.