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

On Wed, Feb 5, 2025 at 5:55 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Right now, if we have four tables to join, we have a joinlist
> (A B C D). (Really they're integer relids, but let's use names here.)
> If we decide to force C to be joined last, it should be sufficient to
> convert this to ((A B D) C). If C and D both look like candidates for
> this treatment, we can make it be (((A B) C) D) or (((A B) D) C).
> This is pretty much the same thing that happens if you set
> join_collapse_limit to 1 and use JOIN syntax to force a join order.
> In fact, IIRC we start out with nested joinlists and there is some
> code that normally flattens them until it decides it'd be creating
> too large a sub-problem. I'm suggesting selectively reversing the
> flattening.

This should not be too difficult to implement. Outer joins seem to
add some complexity, though. We need to ensure that the resulting
joins in each sub-list are legal given the query's join order
constraints. For example, if we make the joinlist be (((A B) C) D),
we need to ensure that the A/B join and the (A/B)/C join are legal.

Thanks
Richard