Re: Re: Removing INNER JOINs

On 1 December 2017 at 15:34, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:
>
> Can someone please explain, in layman-terms, what the problems with FKs
> are related to JOIN-removal?
>

Pretty much what I just wrote after "Unfortunately not," above, although
you asked a few seconds before I sent.

We're able to use UNIQUE INDEXes as proofs to remove LEFT JOINs as (with
the exception of deferred unique indexes) these are updated right away,
rather than deferred until the end of the statement as is the case with NOT
DEFERRABLE and not DEFERRED foreign keys. The fact that the foreign keys do
not update the referenced rows right away means that there is a non-zero
window of time that the constraint is violated, therefore, if a query which
is run, or is running during that time, we could return the incorrect
results if we were to remove an INNER JOIN during the planning of that
query.

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