Re: postgres_fdw bug in 9.6

On Wed, Jan 11, 2017 at 3:30 PM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/01/11 17:51, Ashutosh Bapat wrote:
>>
>> On Wed, Jan 11, 2017 at 1:15 PM, Etsuro Fujita
>> <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)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.
>
>
> Right, but local-join plans need not be efficient since no base table will
> return more than one row, as stated in the documentation. (I think
> efficient plans without complicating the code would be better, though.)
>
>>>> 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.
>
>
> ISTM that's solving a problem that hasn't been proven to be a problem.

A hop will consume a function call worth CPU at least.

>
>>>> 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.
>
>
> Sorry, I don't understand this part.

A parameterized join is built if the joining paths are parameterized
as well. Thus building a parameterized local path would require one
to search suitably parameterized paths in joining relations in their
pathlists.

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