Re: Removing INNER JOINs

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?

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services