Re: Removing INNER JOINs

Jim Finnerty <jfinnert@amazon.com> writes:
> Great example, David.  The planner can detect whether a SELECT statement
> contains a volatile function, and can disable the proposed redundant
> inner-join optimization in that case.  

> 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.

I don't think you're thinking nearly hard enough about what would break
this.  The planner does not have much insight into the context a
statement is being used in (e.g. whether we're inside some kind of
PL function).  Nor does it get to make assumptions about whether the plan
will be used inside a transaction block or not.

            regards, tom lane