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

On 11/21/25 21:47, Bruce Momjian wrote:
> On Fri, Nov 21, 2025 at 03:14:15PM -0500, Tom Lane wrote:
>> I spent a little time staring at the v5 patches. Obviously there
>> are a bunch of minor details to be verified, which you've carefully
>> provided XXX comments about, and I didn't really go through those
>> yet. There are two big-picture questions that are bothering me:
>>
>> 1. I do not think I believe the premise that the dimension tables
>> typically won't have restriction clauses. ISTM that a typical
>> query might be like
>>
>> select sum(o.total_price) from
>> orders o
>> join customers c on c.id = o.c_id
>> join products p on p.id = o.p_id
>> where c.customer_name = 'Wile E Coyote'
>> and p.product_name = 'Rocket Skates';
>
> Yes, I am sure it is typical because I have seen cartoons use exactly
> those products. ;-)
>

;-)

>> The only reason to join a dimension table that lacks a restriction
>> clause is if you need some of its fields in the output, which you
>> might but I'm not sure that's such a common case. (Have you got
>> evidence to the contrary?) So I feel like we're not going to be
>> getting all that much win if we are not willing to treat such tables
>> as dimension tables. We could do something simplistic like order
>> those dimensions by the selectivity of their baserestrict clauses,
>> joining the most-restricted ones first and any restriction-free ones
>> last.
>
> Oh, I thought the patch already did this, e.g., the patch was going to
> make groups, e.g., foreign keys with restrictions, foreign keys without
> restrictions, and no foreign key (might add rows). The first group was
> going to be sorted by their selectivity, and the last group was going to
> be sorted by how much they add rows, if that is possible.
>

No, the patch never did that. The various XXX comments discuss that as a
future optimization. Aren't the comments clear enough?

I think it'd work about the way you described, except that joins without
foreign keys can both increase and decrease the cardinality, and those
that reduce cardinality would need to be moved to the first group.

Another question is what to do about snowflake joins, as a common
extension/generalization of starjoins. I think we'd need to identify the
groups of dimensions (for the branches), and treat those as a single
logical dimension.

regards

--
Tomas Vondra