Re: Join push-down for foreign tables

Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com> writes:
> I'd like to develop pushing down JOIN between foreign tables which are
> on one foreign server, to enhance performance of joining foreign tables
> by reducing data transfer.

This sketch sounds pretty reasonable, with one minor point that's not
going to work:

> Costs of ForeignJoinPath are estimated by FDW via new routine
> PlanForeignJoin, and SQL based FDW would need to generate remote SQL
> here. If a FDW can't push down that join, then it can set disable_cost
> (1.0e10) to tell planner to not choose that path.

disable_cost is not a positive guarantee that a path won't be chosen.
Particularly not for foreign table accesses, where the estimated costs
could be pretty darn large in themselves. You need to pick an API
wherein refusal is unmistakable. Probably, returning NULL instead of a
Path structure is the appropriate way to signal "can't do this join".

> In this design, cost of ForeignJoinPath is compared to other join nodes
> such as NestPath and MergePath. If ForeignJoinPath is the cheapest one
> among the join candidates, planner will generates ForeignJoin plan node
> and put it into plan tree as a leaf node. In other words, joined
> foreign tables are merged into upper ForeignJoin node.

Hmmm ... are you trying to describe what happens when three or more
foreign tables are all to be joined at the remote end? I agree that's
an important use-case, and that we probably want just one Plan node to
result from it, but I'm less sure about what the Path representation
ought to be. It might be better to retain the Path tree showing what
we'd concluded about what the join order ought to be, with the idea that
the transmitted query could be constructed to reflect that, saving the
remote-end planner from having to repeat that work.

regards, tom lane