Re: should we have a fast-path planning for OLTP starjoins?

On 2/11/25 10:28, Richard Guo wrote:
> On Mon, Feb 10, 2025 at 5:35 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>> On 2/10/25 08:29, Richard Guo wrote:
>>> Hmm, I'm still a little concerned about whether the resulting joins
>>> are legal. Suppose we have a join pattern like the one below.
>>>
>>> F left join
>>> (D1 inner join T on true) on F.b = D1.b
>>> left join D2 on F.c = D2.c;
>>>
>>> For this query, the original joinlist is [F, D1, T, D2]. If we
>>> reorder it to [[F, T], D1, D2], the sub-joinlist [F, T] would fail to
>>> produce any joins, as the F/T join is not legal.
>>>
>>> This may not be the pattern we are targeting. But if we intend to
>>> support it, I think we need a way to ensure that the resulting joins
>>> are legal.
>
>> It's quite possible the PoC patch I posted fails to ensure this, but I
>> think the assumption is we'd not reorder joins for dimensions that any
>> any join order restrictions (except for the FK join).
>
> Then, we'll need a way to determine if a given relation has join-order
> restrictions, which doesn't seem like a trivial task. We do have the
> has_join_restriction() function, but it considers any relations
> involved in an outer join as having join restrictions, and that makes
> it unsuitable for our needs here.
>

I admit knowing next to nothing about join order planning :-( Could you
maybe explain why it would be non-trivial to determine if a relation has
join-order restrictions? Surely we already determine that, no? So what
would we need to do differently?

Or are you saying that because has_join_restriction() treats each
relation with an outer join as having a restriction, that makes it
unusable for the purpose of this optimization/patch? And we'd need to
invent something more elaborate?

I'm not sure that's quite true. The problem with joining the dimensions
(with inner joins) is *exactly* the lack of restrictions, which means
that explore possible orders of those dimensions (all N! of them). With
the restrictions (e.g. from LEFT JOIN), that's no longer true - in a
way, this is similar to what the patch does. And in fact, replacing the
inner joins with LEFT JOINs makes the queries much faster. I've seen
this used as a workaround to cut down on planning time ...

So I don't think treating outer joins as "having restriction" is a
problem. It doesn't regress any queries, although it might lead to a bit
strange situation that "less restricted" joins are faster to plan.

regards

--
Tomas Vondra