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

On 2/10/25 08:29, Richard Guo wrote:
> On Mon, Feb 10, 2025 at 9:32 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>> E.g. imagine we have a join of 8 relations, with F (fact), dimensions D1
>> and D2, and then some artibrary tables T1, T2, T3, T4, T5. And let's say
>> deconstruct_recurse() sees them in this particular order
>>
>> [F, T1, T2, D1, D2, T3, T4, T5]
>>
>> AFAICS doing something in deconstruct_recurse() would likely split the
>> joinlist into four parts
>>
>> [F, T1, T2] [D1] [D2] [T3, T4, T5]
>>
>> which does treat the D1,D2 as if join_collapse_limit=1, but it also
>> splits the remaining relations into two groups, when we'd probably want
>> something more like this:
>>
>> [F, T1, T2, T3, T4, T5] [D1] [D2]
>>
>> Which should be legal, because a requirement is that D1/D2 don't have
>> any other join restrictions (I guess this could be relaxed a bit to only
>> restrictions within that particular group).
>
> 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).

regards

--
Tomas Vondra