Hi po 23. 3. 2026 v 7:13 odesílatel Richard Guo <[email protected]> napsal:
> 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. > Maybe you can push this analysis to some README in the code. Regards Pavel > - Richard > > >
