Re: Removing INNER JOINs

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.

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