Re: Removing INNER JOINs

On 12 December 2017 at 02:38, Jim Finnerty <jfinnert@amazon.com> 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@mail.gmail.com#CAApHDvpCBEfuc5tD=vniepAv0pU5m=q=fOQZcOdMHeei7OQPgQ@mail.gmail.com

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services