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.
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?
/Jim F
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html