Re: Remove inner joins based on foreign keys

On Wed, Apr 1, 2026 at 6:45 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Please see the v2 patch for the implementation details.
>
> I didn't find any mention of this approach in the 2014 thread.  I'd
> appreciate any thoughts or feedback on this direction.

Here is a new rebase over 20efbdffe.  It also tightens up some checks
in inner_join_is_removable().

I've reconsidered the trigger-gap issue and I think I now have a
cleaner understanding.  A snapshot captured inside the trigger-gap
window can outlive the window's closure -- for example via a STABLE
function that inherits an older snapshot from its caller, as Tom
pointed out back in 2015 [1].  A query executed against such a stale
snapshot would still observe the inconsistency even after the trigger
queue has drained.  So the predicate guarding the optimization has to
remain positive at least as long as any in-transaction snapshot could
still be referenced.

I went through several alternatives (a per-statement counter
incremented in ExecInitModifyTable, AfterTriggerPendingOnRel, etc.)
and convinced myself that all of them go silent before the relevant
snapshots are gone.  The lock-based predicate is still the best
correct approach I can think of: RowExclusiveLock is released only at
end of transaction, which trivially exceeds the lifetime of any
in-transaction snapshot.  By the time the lock is released, every
in-transaction snapshot has been released, so no stale gap-window
snapshot can still be referenced.  Maybe the false positives are just
the price we need to pay for that lifetime guarantee.

[1] https://postgr.es/m/32139.1427667410@sss.pgh.pa.us

- Richard