Re: [HACKERS] postgres_fdw bug in 9.6

On Wed, Jan 11, 2017 at 1:15 PM, Etsuro Fujita
<fujita.etsuro@lab.ntt.co.jp> wrote:
> On 2017/01/11 13:40, Ashutosh Bapat wrote:
>>
>> CreateLocalJoinPath tries to construct a nestloop path for the given
>> join relation because it wants to avoid merge/hash join paths which
>> require expensive setup not required for EPQ. But it chooses cheap
>> paths for joining relations which may not be nestloop paths. So,
>> effectively it could happen that the whole alternate local plan would
>> be filled with hash/merge joins except the uppermost join.
>
>
> In many cases the cheapest-total-cost outer and inner paths for a higher
> foreign-join relation would be foreign join paths, which would have nestloop
> paths as their fdw_outerpaths if not full joins.  So by redirection, the
> plan tree for EPQ would be mostly constructed by nestloop joins.  No?

It's not guaranteed that we will always have foreign join paths there.
We have seen this in Jeff's example, which started this thread. We
don't know in what all cases we have a tree entirely consisting of
(cheapest) foreign join paths.

>
>> Or it can
>> have foreign paths in there, which will need redirection. That's not
>> very good.
>
>
> Maybe I'm missing something, but redirection isn't a problem.

Peformance wise it is, correctness-wise it is not. Why do we want to
incur a hop, when we can avoid it.

>
>> We have to choose one of the following
>> alternatives
>>
>> 1. develop a mechanism to remember epq path for joining relations so
>> that it's available to CreateLocalJoinPath
>> 2.let the caller pass epq local paths for joining relations to
>> CreateLocalJoinPath. How it remembers those, is FDW's problem.
>
>
> I thought such a thing, but I think that would be overcomplicated, as
> pointed out by Tom [2].
>
>> 2. Fix existing code by applying patch from [1]
>
>
> As I said before, that might be fine for 9.6, but I don't think it's a good
> idea to search the pathlist because once we support parameterized foreign
> join paths, which is on my TODOs, we would have to traverse through the
> possibly-lengthy pathlist to find a local-join path, as mentioned in [3].

I don't agree that pathlists will be long enough to make this a
non-attractive solution. For parameterized foreign join paths, with
the approach that this patch takes, we will require to search in two
such pathlists, inner and outer.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company