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

On 11/8/25 21:36, Tom Lane wrote:
> [ Don't have time to read the v4 patch right now, but a couple
> of quick responses: ]
>
> Tomas Vondra <tomas(at)vondra(dot)me> writes:
>> On 9/23/25 21:46, Tom Lane wrote:
>>> I'd be slightly inclined to put the GUC test there, too:
>>>
>>> + if (enable_starjoin_join_search)
>>> + joinlist = starjoin_adjust_joins(root, joinlist);
>
>> I'm not sure I like this very mcuh. No other call in query_planner()
>> does it like that. Most don't have such GUC, but at least
>> remove_useless_self_joins does, and it still doesn't check it here.
>
> Fair enough, it was just a suggestion.
>
>> When thinking about this, I realized the has_join_restriction() is only
>> ever used in join_search_one_level(), i.e. when dealing with each small
>> join order problem. Doesn't this mean the deconstructed jointree must
>> already consider the restrictions in some way? I don't see any explicit
>> mentions of such join order restrictions in deconstruct_recurse. It must
>> not violate any ordering restrictions by splitting the joins in a
>> "wrong" way, right? If I set join_collapse_limit=1 it still needs to
>> satisfy all the rules.
>
> Performing outer joins in syntactic order is always OK by definition,
> and setting join_collapse_limit to 1 just forces that to happen.
> So I guess you could say that the original jointree "considers the
> restrictions", and it's only after we flatten an outer join's two
> sides into a joinlist (along with other rels) that we have to worry.
> Or is that not what you meant?
>

I'm not sure, but I wasn't talking just about outer joins.

AFAICS even queries with inner joins will get the jointree deconstructed
like this. Consider the query from select-1.sql:

select * from t
join dim1 on (dim1.id = id1)
join dim2 on (dim2.id = id2)
join dim3 on (dim3.id = id3)
join dim4 on (dim4.id = id4)
join dim5 on (dim5.id = id5)
join dim6 on (dim6.id = id6)
join dim7 on (dim7.id = id7);

If I set join_collapse_limit=1, then standard_join_search() only sees
problems of size 2, i.e. (list_length(initial_rels) == 2). And we only
look at has_join_restriction() *inside* these small problems, i.e. the
jointree must not be deconstructed in a way that would violate this.

Doesn't that mean deconstruct_jointree() has to somehow "consider" the
join restrictions (even if not explicitly). It that's the case, could we
maybe leverage that, eliminating the need to call has_join_restriction?

It's just a hunch, though. Maybe splitting the jointree like this is
always legal, because deconstruct_jointree() does not try to "reorder"
the elements.

regards

--
Tomas Vondra