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

On Wed, Feb 5, 2025 at 5:42 AM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> Hmmm, yeah. But that's only for the INNER JOIN case. But I've seen many
> of these star join queries with LEFT JOIN too, and then the FKs are not
> needed. All you need is a PK / unique index on the other side.
>
> Perhaps requiring (INNER JOIN + FK) or (LEFT JOIN + PK) would be enough
> to make this work for most cases, and then the rest would simply use the
> regular join order algorithm.
>
> I was thinking that if we allow the dimensions to eliminate rows in the
> fact table, we'd simply join them starting from the most selective ones.
> But that doesn't work if the joins might have different per-row costs
> (e.g. because some dimensions are much larger etc). Doing something
> smarter would likely end up fairly close to the regular join order
> algorithm ...

Yeah, we need to ensure that the joins to the fact table don't affect
its rows; otherwise, the join order matters for the final query plan,
and we'd better run the regular join search algorithm in this case.

For inner joins, using the foreign key seems ideal for this. For left
joins, we might be able to leverage rel_is_distinct_for() to handle
that.

Thanks
Richard