Re: should we have a fast-path planning for OLTP starjoins? - Mailing list pgsql-hackers

Jim Nasby <jnasby@upgrade.com> writes:
> On Tue, Feb 4, 2025 at 3:42 PM Tomas Vondra <tomas@vondra.me> wrote:
>> Perhaps requiring (INNER JOIN + FK) or (LEFT JOIN + PK) would be enough
>> to make this work for most cases, and then the rest would simply use the
>> regular join order algorithm.

> As long as the join is still happening there doesn't appear to be a
> correctness issue here, so I'm not sure mandating FKs makes sense.
> The reason this matters is that highly concurrent FK checks can get VERY
> expensive (due to the cost of creating multiXacts). While it'd be great to
> fix that issue the reality today is it's not uncommon for people to remove
> FKs because of the high performance penalty.

Meh.  If we don't apply this optimization when there's no FK, we have
not made those folks' life any worse.  If we apply it despite there
being no FK, we might choose a materially worse plan than before, and
that *will* make their lives worse.

            regards, tom lane