Re: 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)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