Sv: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 03:30:21, skrev Simon Riggs <
simon(at)2ndquadrant(dot)com <mailto:simon(at)2ndquadrant(dot)com>>:
On 1 December 2017 at 12:20, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> On 1 December 2017 at 02:52, Andreas Joseph Krogh <andreas(at)visena(dot)com>
wrote:
>>
>> I came across this from Oracle:
https://oracle-base.com/articles/misc/join-elimination#basic-join-elimination
>>
>> Needless to say, this would be very cool to have in PG:-)
>
> It would be nice, I agree.
>
>> It seems this has been discussed before [1], [2], [3], and the consesus at
the time was that the proposted implementation introduced way too much
planning-overhead to be worth it. Given that other RDBMS-vendors provides this,
and it's on the "Cool feactures other have that we don't"-list [4], is anyone
interessted in working on improving this?
>
> The large hurdle which a good workaround was never really found for
> was the fact that foreign key triggers only update the referenced rows
> at the end of the statement, or end of query when the foreign key
> constraint is deferred. I don't recall much concern about planner
> overhead. It's likely not going to be too big a concern since we're
> already checking for foreign keys nowadays during selectivity
> estimation.
>
> I do still have all the code I wrote all those years ago, and possibly
> it will still apply to master as I rebased it just several months ago.
> I've just not yet come up with any bright ideas on how to solve the
> foreign key trigger timing problem.

So it would work if the Foreign Keys are marked NOT DEFERRABLE?
 
Can someone please explain, in layman-terms, what the problems with FKs are
related to JOIN-removal?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>