On Sun, Mar 22, 2026 at 6:09 PM David Rowley <[email protected]> wrote: > On Sat, 21 Mar 2026 at 15:47, Richard Guo <[email protected]> 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. - Richard
