On 1 December 2017 at 15:34, Andreas Joseph Krogh <andr...@visena.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
