Tomas Vondra <tomas@vondra.me> writes:
> On 2/4/25 20:43, Jeff Davis wrote:
>> If you base it on the join conditions rather than the size of the
>> table, then detection of the star join would be based purely on the
>> query structure (not stats), which would be nice for predictability.
> Right, there may be other (possibly better) ways to detect the star join
> shape. I was thinking about also requiring for foreign keys on the join
> clauses - in DWH systems FKeys are sometimes omitted, which would break
> the heuristics, but in OLTP it's common to still have them.
I think you need to insist on foreign keys. Otherwise you don't know
whether the joins will eliminate fact-table rows. If that's a
possibility then it's no longer sensible to ignore different join
orders.
I'm kind of imagining a planner rule like "if table X is joined to
using a match of a foreign-key column to its PK (so that the join
removes no rows from the other table) and there are not other
restriction conditions on table X, then force X to be joined last.
And if there are multiple such tables X, it doesn't matter what
order they are joined in as long as they're last."
The interesting thing about this is we pretty much have all the
infrastructure for detecting such FK-related join conditions
already. Possibly the join order forcing could be done with
existing infrastructure too (by manipulating the joinlist).
regards, tom lane
