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

Adam Zegelin <adam@relational.io> writes:
> My path generation logic seems to work:

> baserel->cheapest_parameterized_paths = (
>    {FOREIGNPATH
>    :pathtype 120
>    :parent_relids (b 3)
>    :required_outer (b 1 2)
>    :rows 500
>    :startup_cost 0.00
>    :total_cost 0.00
>    :pathkeys <>
>    :fdw_private <>
>    }
>    {FOREIGNPATH
>    :pathtype 120
>    :parent_relids (b 3)
>    :required_outer (b)
>    :rows 1000
>    :startup_cost 5000000.00
>    :total_cost 5000000.00
>    :pathkeys <>
>    :fdw_private <>
>    }
> )

I think you missed my point: you should not be insisting on a maximal
set of required outer rels.

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.  But in related cases, there could be join order restrictions
that *require* us not to do the joins in that order; so even if you
could talk us out of applying that heuristic, this code is still subject
to undesirable failures.  You really need to provide three paths using
the three possible combinations of outer rels.

            regards, tom lane