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

On Mon, Feb 10, 2025 at 9:32 AM Tomas Vondra <tomas@vondra.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.

Thanks
Richard