Re: Enforcing Parameterised Nested Loop Join Order for Foreign Table Joins

Adam Zegelin <adam(at)relational(dot)io> writes:
> Some service endpoints have no concept of unqualified queries. In the example above, a sequence scan of Bing is a not possible.

In that case, you shouldn't be generating such a path. But keep in mind
that this may lead to failure to produce any plan at all for some
queries. If the foreign data source is really so broken that it can't
do that, then you have little choice ... but you shouldn't be thinking
of that as anything but a broken design decision on their part.

> I generate parameterised paths inside the FDW handler function `GetForeignPaths`. I call `create_foreignscan_path` with a set of req_outer relids found by scanning PlannerInfos eq_classes, left_join_clauses and right_join_clauses.

It looks to me like this logic will produce only one parameterized path
that demands the maximal set of outer relations. You need to be a bit
more flexible than that.

> adam=# explain select * from ft1, ft2, ft3 where ft1.inp = 'hello' and ft2.inp = ft1.out and ft3.inp = ft2.out;

In this example, your only parameterized path for ft2 will require both
ft1 and ft3 as inputs, since it sees both of the quals mentioning ft2
as potential join quals. So there's no way to generate the plan you're
hoping for. You need to have produced a path that requires only ft1.
Really, given this input, you should be producing three parameterized
paths for ft2 (one using only ft1, one using only ft3, one using both)
and then let the planner logic sort out which one to use.

regards, tom lane