On 12 December 2017 at 02:38, Jim Finnerty <jfinnert@xxxxxxxxxx> wrote: > If necessary, the planner could also check that the FK constraint is not > DEFERRED, but if there are no volatile functions and the SELECT statement > can't see an inconsistent state created by any other transaction, I think > that just checking for volatile functions and not being inside a DML > transaction would be sufficient. > > A further opportunity would be to apply this to any SELECT statement in a > DML transaction, provided that there was no prior DML statement or statement > containing a volatile function in the same transaction. > > We already have a redundant outer join optimization, and I've implemented > the redundant inner join optimization in two other products before, so > adding the additional logic to support the inner join case(s) sounds > straightforward to me. Can anyone think of any other problem scenarios? You should read over [1]. This was my attempt at doing this over 3 years ago. The thread might save you from going down some of the dead ends that I ended up going down. [1] https://www.postgresql.org/message-id/flat/CAApHDvpCBEfuc5tD=vniepAv0pU5m=q=fOQZcOdMHeei7OQPgQ@xxxxxxxxxxxxxx#CAApHDvpCBEfuc5tD=vniepAv0pU5m=q=fOQZcOdMHeei7OQPgQ@xxxxxxxxxxxxxx -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
