Re: Foreign join pushdown vs EvalPlanQual

On 2015/07/03 15:32, Kouhei Kaigai wrote:
>> On 2015/07/02 23:13, Kouhei Kaigai wrote:
>>>> To be honest, ISTM that it's difficult to do that simply and efficiently
>>>> for the foreign/custom-join-pushdown API that we have for 9.5. It's a
>>>> little late, but what I started thinking is to redesign that API so that
>>>> that API is called at standard_join_search, as discussed in [2];

>>> Disadvantage is larger than advantage, sorry.
>>> The reason why we put foreign/custom-join hook on add_paths_to_joinrel()
>>> is that the source relations (inner/outer) were not obvious, thus,
>>> we cannot reproduce which relations are the source of this join.
>>> So, I had to throw a spoon when I tried this approach before.

>> Maybe I'm missing something, but my image about this approach is that if
>> base relations for a given joinrel are all foreign tables and belong to
>> the same foreign server, then by calling that API there, we consider the
>> remote join over all the foreign tables, and that if not, we give up to
>> consider the remote join.

> Your understanding is correct, but missing a point. Once foreign tables
> to be joined are informed as a bitmap (joinrel->relids), it is not obvious
> for extensions which relations are joined with INNER JOIN, and which ones
> are joined with OUTER JOIN.

Can't FDWs get the join information through the root, which I think we
would pass to the API as the argument?

> Also, I don't want to stick on the assumption that relations involved in
> remote join are all managed by same foreign-server no longer.
> The following two ideas introduce possible enhancement of remote join
> feature that involved local relations; replicated table or transformed
> to VALUES() clause.
>
> http://www.postgresql.org/message-id/CA+Tgmoai_VUF5h6qVLNLU+FKp0aeBCbnnMT3SCvL-HvOpBR=Xw@mail.gmail.com
> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F20AD@BPXM15GP.gisp.nec.co.jp

Interesting!

> I think add_paths_to_joinrel() is the best location for foreign-join,
> not only custom-join. Relocation to standard_join_search() has larger
> disadvantage than its advantage.

I agree with you that it's important to ensure the expandability, and my
question is, is it possible that the API call from standard_join_search
also realize those idea if FDWs can get the join information through the
root or something like that?

Best regards,
Etsuro Fujita