Thread

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

Bruce Momjian <bruce@momjian.us> — 2025-11-24T20:00:01Z

On Sun, Nov 23, 2025 at 03:53:17PM +0100, Tomas Vondra wrote:
> >> 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 my brain got lost in the patch --- I was happy I got as far as I
did in understanding it.  :-)

> 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.

I see, makes sense.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

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