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

Adam Zegelin <adam(at)relational(dot)io> writes:
> On 19/03/2013, at 12:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> In this particular case, it won't generate a cross-product join of l1
>> and l2 because there's a heuristic that says that's unlikely to be a
>> good idea.

> Is this heuristic a tuneable parameter, or something that would require a logic change inside the planner itself?

It's not tuneable, not so much because it would be hard to turn off as
because disabling it would lead to an exponential explosion in planning
time in an uncomfortably large fraction of cases. (If you don't mind
running a locally-hacked version of PG, whacking around the logic in
joinrels.c should get you what you want.)

Rather than shutting off that heuristic as such, what I'd be inclined to
think about is exploiting the "join order restriction" logic so that
an FDW with the kind of issue you describe could mark its relation as
subject to a pseudo join-order restriction. That could cause the
planner to explore join pathways it otherwise wouldn't, but only in
cases where it was really necessary to do so. I'm handwaving a bit here
but I think something like that could be made to work without creating
an across-the-board planning penalty.

Anyway, changes like that will be material for 9.4 or 9.5. I think in a
year or so we'll have a much clearer idea of what kinds of planner knobs
FDWs require than we do today.

regards, tom lane