På fredag 01. desember 2017 kl. 02:20:19, skrev David Rowley < david.row...@2ndquadrant.com <mailto:david.row...@2ndquadrant.com>>: On 1 December 2017 at 02:52, Andreas Joseph Krogh <andr...@visena.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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
