Re: postgres_fdw bug in 9.6

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?

> 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.

> 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].

Best regards,
Etsuro Fujita

[2] https://www.postgresql.org/message-id/12565.1481904788%40sss.pgh.pa.us
[3] 
https://www.postgresql.org/message-id/c1075e4e-8297-5cf6-3f30-cb21266aa2ee%40lab.ntt.co.jp