Re: Remove inner joins based on foreign keys

Hi

po 23. 3. 2026 v 7:13 odesílatel Richard Guo <guofenglinux(at)gmail(dot)com>
napsal:

> On Sun, Mar 22, 2026 at 6:09 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> > On Sat, 21 Mar 2026 at 15:47, Richard Guo <guofenglinux(at)gmail(dot)com>
> wrote:
> > > Currently, the planner can remove useless left joins if the join
> > > condition cannot match more than one RHS row, and the RHS rel is not
> > > referenced above the join. I'd like to propose a similar optimization
> > > for inner joins.
>
> > I tried this many years ago and it was pretty much a dead end with how
> > the current foreign key implementation deferring the cascade of the
> > foreign key until the end of the query.
>
> Thanks for pointing this out! I failed to find the prior work, and I
> missed the fatal flaw introduced by the AFTER ROW trigger mechanism
> for foreign key constraints. I had been making a mental analogy to
> UNIQUE and NOT NULL constraints, but those are enforced immediately at
> the heap/B-tree level.
>
> Just for the sake of archives, the timeline of the trap during a
> cascading delete looks like this:
>
> T0: DELETE FROM users WHERE id = 1;
>
> T1: The executor finds users row 1 and sets its xmax, physically
> marking it as dead.
>
> T2: [The Gap] The executor pushes the RI trigger into a queue to deal
> with orders later. Right now, the orders row still exists, but its
> referenced row in users is dead.
>
> T3: The statement finishes, the trigger fires, and the orders row is
> finally deleted.
>
> The "T2 Gap" is small, but there are several ways to execute user code
> inside that window, such as RETURNING clauses, volatile functions, or
> user-defined AFTER ROW triggers.
>
> Since the planner operates on static logical schema guarantees and
> cannot predict dynamic execution-time trigger queues, it seems any
> plan-time optimization that relies on foreign keys for correctness is
> effectively a dead end. Maybe the only solution would be to handle
> the join removal in the executor (where the trigger state is known),
> but I noticed you explored that a decade ago and it seems far too
> invasive.
>
> Thanks again for the save. You saved me a lot of time and effort
> chasing a dead end.
>

Maybe you can push this analysis to some README in the code.

Regards

Pavel

> - Richard
>
>
>