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

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.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.