Re: Removing INNER JOINs

2017-12-12 Thread David Rowley
On 12 December 2017 at 02:38, Jim Finnerty wrote: > If necessary, the planner could also check that the FK constraint is not > DEFERRED, but if there are no volatile functions and the SELECT statement > can't see an inconsistent state created by any other transaction, I think > that just checking

Re: Removing INNER JOINs

2017-12-11 Thread Tom Lane
Jim Finnerty writes: > Great example, David. The planner can detect whether a SELECT statement > contains a volatile function, and can disable the proposed redundant > inner-join optimization in that case. > If necessary, the planner could also check that the FK constraint is not > DEFERRED, b

Re: Removing INNER JOINs

2017-12-10 Thread David Rowley
On 11 December 2017 at 17:33, Jim Finnerty wrote: > FK constraint enforcement may be deferred, but only to the end of the DML > transaction. Can someone provide an example where a SELECT statement that > is not in that DML transaction and that can view only committed data can see > a FK-side row

Sv: Re: Re: Removing INNER JOINs

2017-12-01 Thread Andreas Joseph Krogh
På fredag 01. desember 2017 kl. 03:53:29, skrev David Rowley < david.row...@2ndquadrant.com >: On 1 December 2017 at 15:34, Andreas Joseph Krogh mailto:andr...@visena.com>> wrote:  Can someone please explain, in layman-terms, what the problems with FKs are rela

Re: Re: Removing INNER JOINs

2017-11-30 Thread David Rowley
On 1 December 2017 at 15:34, Andreas Joseph Krogh wrote: > > Can someone please explain, in layman-terms, what the problems with FKs > are related to JOIN-removal? > Pretty much what I just wrote after "Unfortunately not," above, although you asked a few seconds before I sent. We're able to use

Sv: Re: Removing INNER JOINs

2017-11-30 Thread Andreas Joseph Krogh
På fredag 01. desember 2017 kl. 03:30:21, skrev Simon Riggs < si...@2ndquadrant.com >: On 1 December 2017 at 12:20, David Rowley wrote: > On 1 December 2017 at 02:52, Andreas Joseph Krogh wrote: >> >> I came across this from Oracle: https://oracle-base.com/artic

Re: Removing INNER JOINs

2017-11-30 Thread David Rowley
On 1 December 2017 at 15:30, Simon Riggs wrote: > On 1 December 2017 at 12:20, David Rowley > wrote: >> 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 wh

Re: Removing INNER JOINs

2017-11-30 Thread Simon Riggs
On 1 December 2017 at 12:20, David Rowley wrote: > On 1 December 2017 at 02:52, Andreas Joseph Krogh 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:-) > > I

Sv: Re: Removing INNER JOINs

2017-11-30 Thread Andreas Joseph Krogh
På fredag 01. desember 2017 kl. 02:20:19, skrev David Rowley < david.row...@2ndquadrant.com >: On 1 December 2017 at 02:52, Andreas Joseph Krogh wrote: > > I came across this from Oracle: https://oracle-base.com/articles/misc/join-elimination#basic-join-elim

Re: Removing INNER JOINs

2017-11-30 Thread David Rowley
On 1 December 2017 at 02:52, Andreas Joseph Krogh 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 discusse

Removing INNER JOINs

2017-11-30 Thread Andreas Joseph Krogh
rhoVLqOqOxVXe%3DNjxw%40mail.gmail.com 3. Removing INNER JOINs http://www.postgresql-archive.org/Removing-INNER-JOINs-td5828650i40.html 4.  https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/#top3   -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: