Re: WIP: Join push-down for foreign tables

On 02.12.2011 18:55, Tom Lane wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Tom, what do you think of this part? I think it would be a lot more
>> natural API if the planner could directly ask the FDW to construct a
>> plan for a three (or more)-way join, instead of asking it to join a join
>> relation into another relation.
>
> I think this is fundamentally not going to work, at least not without
> major and IMO unwise surgery on the planner. Building up joins pairwise
> is what it does.
>
> Furthermore, you seem to be imagining that there is only one best path
> for any join, which isn't the case.

No, I understand that the planner considers many alternatives, even at
the same time, because of different output sort orders and startup vs.
total cost. I'm imagining that the planner would ask the FDW to
construct the two-way joins, and consider joining the results of those
locally to the third table, and also ask the FDW to construct the
three-way join as whole. And then choose the cheapest alternative.

> We'll typically have several paths
> under consideration because of cheapest-startup versus cheapest-total
> and/or different resulting sort orders. If we do what you're
> suggesting, that's going to either break entirely or require a much more
> complicated API for PlanForeignJoin.

I don't understand why the FDW should care about the order the joins are
constructed in in the planner. From the FDW's point of view, there's no
difference between joining ((A B) C) and (A (B C)). Unless you also want
to consider doing a remote join between (A B) and C, where C is a
foreign table but A and B are local tables. That would in theory be
possible to execute in the remote server, by shipping the result of (A
B) to the remote server, but we'd also need a quite different executor
API to handle that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com