Re: [HACKERS] postgres_fdw bug in 9.6

On 2016/12/19 13:59, Ashutosh Bapat wrote:
> On Fri, Dec 16, 2016 at 9:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp> writes:
>>> On 2016/12/16 11:25, Etsuro Fujita wrote:
>>>> As I said upthread, an alternative I am thinking is (1) to create an
>>>> equivalent nestloop join path using inner/outer paths of a foreign join
>>>> path, except when that join path implements a full join, in which case a
>>>> merge/hash join path is used, (2) store it in fdw_outerpath as-is, and
>>>> (3) during an EPQ recheck, apply postgresRecheckForeignScan to the outer
>>>> subplan created from the fdw_outerpath as-is.  What do you think about
>>>> that?

>>> Let me explain about #1 and #2 a bit more.  What I have in mind is:

>>> * modify postgresGetForeignPaths so that a simple foreign table scan
>>> path is stored into the base relation's PgFdwRelationInfo.
>>> * modify postgresGetForeignJoinPaths so that
>>>      (a) a local join path for a 2-way foreign join is created using
>>> simple foreign table scan paths stored in the base relations'
>>> PgFdwRelationInfos, and stored into the join relation's PgFdwRelationInfo.
>>>      (b) a local join path for a 3-way foreign join, whose left input is
>>> a 2-way foreign join, is created using a local join path stored in the
>>> left input join relation's PgFdwRelationInfo and a simple foreign table
>>> scan path stored into the right input base relation's PgFdwRelationInfo.
>>>      (c) Likewise for higher level foreign joins.
>>>      (d) local join paths created are passed to create_foreignscan_path
>>> and stored into the fdw_outerpaths of the resulting ForeignPahts.

>> Hm, isn't this overcomplicated?  As you said earlier, we don't really
>> care all that much whether the fdw_outerpath is free of lower foreign
>> joins, because EPQ setup will select those lower join's substitute EPQ
>> plans anyway.  All that we need is that the EPQ tree be a legal
>> implementation of the join order with join quals applied at the right
>> places.  So I think the rule could be
>>
>> "When first asked to produce a path for a given foreign joinrel, collect
>> the cheapest paths for its left and right inputs, and make a nestloop path
>> (or hashjoin path, if full join) from those, using the join quals needed
>> for the current input relation pair.  Use this as the fdw_outerpath for
>> all foreign paths made for the joinrel."

> We could use fdw_outerpath of the left and right inputs instead of
> looking for the cheapest paths. For base relations as left or right
> relations, use the unparameterized cheapest foreign path. This will
> ensure that all joins in fdw_outerpath are local joins, making EPQ
> checks slightly efficient by avoiding redirection at every foreign
> path.

That seems close to what I had in mind described above.  One additional 
work required for that would to store the fdw_outerpath into the 
RelOptInfo's fdw_private such as PgFdwRelationInfo before add_path for 
the foreign-join path containing the fdw_outerpath, because add_path 
might reject the foreign-join path.  I think the additional work would 
make that rather complicated.

Best regards,
Etsuro Fujita