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

On 2/8/25 01:23, Tom Lane wrote:
> Tomas Vondra <tomas(at)vondra(dot)me> writes:
>> Instead, I was thinking about the "other" joins (if there are any), that
>> may add or remove rows. AFAIK we want to join the dimensions at the
>> place with the lowest cardinality - the discussion mostly assumed the
>> joins would only reduce the cardinality, in which case we'd just leave
>> the dimensions until the very end.
>
>> But ISTM that may not be necessarily true. Let's say there's a join that
>> "multiplies" each row. It'll probably be done at the end, and the
>> dimension joins should probably happen right before it ... not sure.
>
> I thought the idea here was to get rid of as much join order searching
> as we could. Insisting that we get the best possible plan anyway
> seems counterproductive, not to mention very messy to implement.
> So I'd just push all these joins to the end and be done with it.
>

Right, cutting down on the join order searching is the point. But most
of the savings comes (I think) from not considering different ordering
of the dimensions, because those are all essentially the same.

Consider a join with 16 relations, 10 of which are dimensions. There are
10! possible orders of the dimensions, but all of them behave pretty
much exactly the same. In a way, this behaves almost like a join with 7
relations, one of which represents all the 10 dimensions.

I think this "join group" abstraction (a relation representing a bunch
of relations in a particular order) would make this reasonably clean to
implement. I haven't tried yet, though.

Yes, this means we'd explore more orderings, compared to just pushing
all the dimensions to the end. In the example above, that'd be 7!/6!, so
up to ~7x orderings.

I don't know if this is worth the extra complexity, of course.

thanks

--
Tomas Vondra