Re: WIP: Join push-down for foreign tables

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

> Does the planner expect the result from the foreign server to be
> correctly sorted, if it passes pathkeys to that function?

Well, the result path should be marked with pathkeys if it is known to
be sorted a certain way, or with NIL if not. There's no prejudgment as
to what a particular join method will produce. That does raise
interesting questions though as to how to interact with the remote-end
planner --- if we've reported that a path has certain pathkeys, that
probably means that the SQL sent to the remote had better say ORDER BY,
which would be kind of annoying if in the end we weren't depending on
the path to be sorted. I'm not sure what it would take to pass that
information back down, though. What we might have to do to make this
work conveniently is generate two versions of every foreign path: one
marked with pathkeys, and one not. And make sure the former has a
somewhat-higher cost. Then we'd know from which path gets picked
whether the plan is actually depending on sorted results.

regards, tom lane