Sv: Re: Removing INNER JOINs

På fredag 01. desember 2017 kl. 02:20:19, skrev David Rowley <
david(dot)rowley(at)2ndquadrant(dot)com <mailto:david(dot)rowley(at)2ndquadrant(dot)com>>:
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.
 
Thanks for answer.
 
I guess I'm back to hoping someone will spend time thinking about those
challenges. In our app we have more and more dynamic queries which join lots of
tables but not necessarily SELECTs from them. Maintaining the list of
join-tables when constructing the dynamic queries is a pain...
 
Especially when running "paging-queries", like "Give me top 100 of 899 345
totals". We fire two queries, one with the SELECT-list and one with count(*),
but with the same FROM-clause. I see LEFT OUTER JOINs are being removed when
issuing the count(*), which helps, but remove more JOINs would (probably) help
even more:-)
 
-- 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>