Re: [HACKERS] Performance improvement for joins where outer side is unique

On 8 April 2017 at 14:23, Tom Lane wrote: > David Rowley writes: > [ unique_joins_2017-04-07b.patch ] > > It turned out that this patch wasn't as close to committable as I'd > thought, but after a full day of whacking at it, I got to a place > where I thought it was OK. So, pushed. Many thanks

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: [ unique_joins_2017-04-07b.patch ] It turned out that this patch wasn't as close to committable as I'd thought, but after a full day of whacking at it, I got to a place where I thought it was OK. So, pushed. [ and that's a wrap for v10 feature freeze, I think ]

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2017 at 13:41, Tom Lane wrote: > David Rowley writes: >> On 7 April 2017 at 11:47, Tom Lane wrote: >>> What I'm on about is that you can't do the early advance to the >>> next outer tuple unless you're sure that all the quals that were >>> relevant to the uniqueness proof have been ch

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 7 April 2017 at 11:47, Tom Lane wrote: >> What I'm on about is that you can't do the early advance to the >> next outer tuple unless you're sure that all the quals that were >> relevant to the uniqueness proof have been checked for the current >> inner tuple. That affec

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2017 at 11:47, Tom Lane wrote: > David Rowley writes: >> On 7 April 2017 at 07:26, Tom Lane wrote: >>> I'm looking through this, and I'm failing to see where it deals with >>> the problem we discussed last time, namely that you can't apply the >>> optimization unless all clauses that

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 7 April 2017 at 07:26, Tom Lane wrote: >> I'm looking through this, and I'm failing to see where it deals with >> the problem we discussed last time, namely that you can't apply the >> optimization unless all clauses that were used in the uniqueness >> proof are included

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2017 at 07:26, Tom Lane wrote: > I'm looking through this, and I'm failing to see where it deals with > the problem we discussed last time, namely that you can't apply the > optimization unless all clauses that were used in the uniqueness > proof are included in the join's merge/hash co

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 2 April 2017 at 21:21, David Rowley wrote: >> I've attached an updated patch which updates the regression test output of >> a recent commit to include the "Unique Inner" in the expected results. > The patch must've fallen off. Attempt number 2 at attaching. I'm looking

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > Tom, I'm wondering if you think you'll get time to look at this before the > feature freeze? Yeah, I intend to. Thanks for updating the patch. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make change

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 2 April 2017 at 21:21, David Rowley wrote: > I've attached an updated patch which updates the regression test output of > a recent commit to include the "Unique Inner" in the expected results. > The patch must've fallen off. Attempt number 2 at attaching. -- David Rowley

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Sun, Apr 2, 2017 at 5:21 AM, David Rowley wrote: > I've attached an updated patch which updates the regression test output of a > recent commit to include the "Unique Inner" in the expected results. Was this email supposed to have a patch attached? > Tom, I'm wondering if you think you'll get

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 27 March 2017 at 15:51, David Rowley wrote: > On 27 March 2017 at 09:28, David Rowley > wrote: > > > Patch is attached which fixes up the conflict between the expression > > evaluation performance patch. > > Seems I forgot to commit locally before creating the patch... Here's > the actual pat

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 27 March 2017 at 09:28, David Rowley wrote: > Patch is attached which fixes up the conflict between the expression > evaluation performance patch. Seems I forgot to commit locally before creating the patch... Here's the actual patch I meant to attach earlier. -- David Rowley

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 14 March 2017 at 16:37, David Rowley wrote: > On 14 March 2017 at 11:35, David Rowley > wrote: >> >> On 14 March 2017 at 07:50, Tom Lane wrote: >>> >>> [ getting back to this patch finally... ] >>> >>> David Rowley writes: >>> > I've attached a patch which implements this, though only for >>

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 14 March 2017 at 11:35, David Rowley wrote: > On 14 March 2017 at 07:50, Tom Lane wrote: > >> [ getting back to this patch finally... ] >> >> David Rowley writes: >> > I've attached a patch which implements this, though only for >> > MergeJoin, else I'd imagine we'd also need to ensure all p

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 14 March 2017 at 07:50, Tom Lane wrote: > [ getting back to this patch finally... ] > > David Rowley writes: > > I've attached a patch which implements this, though only for > > MergeJoin, else I'd imagine we'd also need to ensure all proofs used > > for testing the uniqueness were also hash-

Re: [HACKERS] Performance improvement for joins where outer side is unique

[ getting back to this patch finally... ] David Rowley writes: > I've attached a patch which implements this, though only for > MergeJoin, else I'd imagine we'd also need to ensure all proofs used > for testing the uniqueness were also hash-able too. I added some XXX > comments in analyzejoin.c a

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Tue, Jan 31, 2017 at 9:13 AM, David Rowley wrote: > On 31 January 2017 at 13:10, David Rowley > wrote: >> I've attached a patch which implements this. > > Please disregards previous patch. (I forgot git commit before git diff > to make the patch) > > I've attached the correct patch. Moved to

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 31 January 2017 at 10:43, Tom Lane wrote: > David Rowley writes: >> I don't think that's possible. The whole point that the current join >> removal code retries to remove joins which it already tried to remove, >> after a successful removal is exactly because it is possible for a >> join to be

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 31 January 2017 at 13:10, David Rowley wrote: > I've attached a patch which implements this. Please disregards previous patch. (I forgot git commit before git diff to make the patch) I've attached the correct patch. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 28 January 2017 at 05:44, Tom Lane wrote: > I wrote: >> David Rowley writes: >>> hmm. I'm having trouble understanding why this is a problem for Unique >>> joins, but not for join removal? > >> Ah, you know what, that's just mistaken. I was thinking that we >> short-circuited the join on the

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 31 January 2017 at 04:56, Tom Lane wrote: >> I'm not following. If the join removal code had reached the stage of >> making a uniqueness check, and that check had succeeded, the join would be >> gone and there would be no repeat check later. If it didn't reach the >> s

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 31 January 2017 at 04:56, Tom Lane wrote: > David Rowley writes: >> I can make this change, but before I do I just want to point that I >> don't think what you've said here is entirely accurate. > >> Let's assume unique joins are very common place, and join removals are >> not so common. If a

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > I can make this change, but before I do I just want to point that I > don't think what you've said here is entirely accurate. > Let's assume unique joins are very common place, and join removals are > not so common. If a query has 5 left joins, and only one of which can > b

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 28 January 2017 at 05:04, Tom Lane wrote: > David Rowley writes: >> I agree that special handling of one join type is not so pretty. >> However, LEFT JOINs still remain a bit special as they're the only >> ones we currently perform join removal on, and the patch modifies that >> code to make u

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Fri, Jan 27, 2017 at 2:00 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jan 27, 2017 at 1:39 PM, Tom Lane wrote: >>> Um ... what's that got to do with the point at hand? > >> So I assumed from that that the issue was that you'd have to wait for >> the first time the irrelevant-joinqual

Re: [HACKERS] Performance improvement for joins where outer side is unique

Robert Haas writes: > On Fri, Jan 27, 2017 at 1:39 PM, Tom Lane wrote: >> Um ... what's that got to do with the point at hand? > So I assumed from that that the issue was that you'd have to wait for > the first time the irrelevant-joinqual got satisfied before the > optimization kicked in. No,

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Fri, Jan 27, 2017 at 1:39 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Jan 27, 2017 at 11:44 AM, Tom Lane wrote: >>> I'm afraid though that we may have to do something about the >>> irrelevant-joinquals issue in order for this to be of much real-world >>> use for inner joins. > >> May

Re: [HACKERS] Performance improvement for joins where outer side is unique

Robert Haas writes: > On Fri, Jan 27, 2017 at 11:44 AM, Tom Lane wrote: >> I'm afraid though that we may have to do something about the >> irrelevant-joinquals issue in order for this to be of much real-world >> use for inner joins. > Maybe, but it's certainly not the case that all inner joins a

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Fri, Jan 27, 2017 at 11:44 AM, Tom Lane wrote: > I'm afraid though that we may have to do something about the > irrelevant-joinquals issue in order for this to be of much real-world > use for inner joins. Maybe, but it's certainly not the case that all inner joins are highly selective. There

Re: [HACKERS] Performance improvement for joins where outer side is unique

I wrote: > David Rowley writes: >> hmm. I'm having trouble understanding why this is a problem for Unique >> joins, but not for join removal? > Ah, you know what, that's just mistaken. I was thinking that we > short-circuited the join on the strength of the hash (or merge) quals > only, but actu

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 27 January 2017 at 12:39, Tom Lane wrote: >> 2. In these same cases (unique/semi/anti joins), it is possible to avoid >> mark/restore overhead in a mergejoin, because we can tweak the executor >> logic to not require backing up the inner side. > I've made modifications

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 27 January 2017 at 12:39, Tom Lane wrote: > 2. In these same cases (unique/semi/anti joins), it is possible to avoid > mark/restore overhead in a mergejoin, because we can tweak the executor > logic to not require backing up the inner side. This goes further than > just tweaking the executor l

Re: [HACKERS] Performance improvement for joins where outer side is unique

I thought about the patch from the perspective of "grouped relations" (especially [1]). When looking for the appropriate context within the thread, I picked this message. David Rowley wrote: > On 12 March 2016 at 11:43, Tom Lane wrote: > > > > It seems like the major intellectual complexity her

Re: [HACKERS] Performance improvement for joins where outer side is unique

To re-familiarize myself with this patch, I've been re-reading the thread, which has gotten quite long. It seemed like it would be a good idea to stop and try to summarize what the patch ought to accomplish, because there's been some drift over the more than 2 years the patch has been in the works

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 27 January 2017 at 08:34, Tom Lane wrote: > David Rowley writes: >> I've attached a version without outer unique. > > I looked through this a bit, and the first thing I noticed was it doesn't > touch costsize.c at all. That seems pretty wrong; it's little help to > have a performance improvem

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > I've attached a version without outer unique. I looked through this a bit, and the first thing I noticed was it doesn't touch costsize.c at all. That seems pretty wrong; it's little help to have a performance improvement if the planner won't pick the right plan type. Ther

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 27 January 2017 at 00:37, David Rowley wrote: > The attached has my Merge Join changes, to show what I think can be > done to make use of unique outer. Let me know what you think, but I > get that idea that we're both leaning towards ripping the outer unique > stuff out, so I'll go do that now.

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 26 January 2017 at 04:56, Antonin Houska wrote: > I suspect that "inner" and "outer" relation / tuple are sometimes confused in > comments: > > > * analyzejoins.c:70 > > "searches for subsequent matching outer tuples." > > > * analyzejoins.c:972 > > /* > * innerrel_is_unique > *Check

Re: [HACKERS] Performance improvement for joins where outer side is unique

Thank for looking at this again. On 25 January 2017 at 06:27, Tom Lane wrote: > David Rowley writes: >> However, having said that, I'm not sure why we'd need outer_unique >> available so we'd know that we could skip mark/restore. I think >> inner_unique is enough for this purpose. Take the comme

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley wrote: > On 19 January 2017 at 11:06, David Rowley > wrote: > > Old patch no longer applies, so I've attached a rebased patch. This > > also re-adds a comment line which I mistakenly removed. > > (meanwhile Andres commits 69f4b9c) > > I should've waited a bit longer. > > Here's a

Re: [HACKERS] Performance improvement for joins where outer side is unique

[ getting back to this at long last ] David Rowley writes: > However, having said that, I'm not sure why we'd need outer_unique > available so we'd know that we could skip mark/restore. I think > inner_unique is enough for this purpose. Take the comment from > nodeMergejoin.c: > * outer: (0 ^1 1

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 19 January 2017 at 11:06, David Rowley wrote: > Old patch no longer applies, so I've attached a rebased patch. This > also re-adds a comment line which I mistakenly removed. (meanwhile Andres commits 69f4b9c) I should've waited a bit longer. Here's another that fixes the new conflicts. --

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 3 December 2016 at 10:26, Tom Lane wrote: > Robert Haas writes: >> On Dec 2, 2016, at 7:47 AM, Haribabu Kommi wrote: >>> Patch still applies fine to HEAD. >>> Moved to next CF with "ready for committer" status. > >> Tom, are you picking this up? > > Yeah, I apologize for not having gotten to

Re: [HACKERS] Performance improvement for joins where outer side is unique

Robert Haas writes: > On Dec 2, 2016, at 7:47 AM, Haribabu Kommi wrote: >> Patch still applies fine to HEAD. >> Moved to next CF with "ready for committer" status. > Tom, are you picking this up? Yeah, I apologize for not having gotten to it in this commitfest, but it's definitely something I w

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Dec 2, 2016, at 7:47 AM, Haribabu Kommi wrote: >> On Wed, Nov 2, 2016 at 1:21 PM, David Rowley >> wrote: >> On 31 October 2016 at 18:37, David Rowley >> wrote: >> > I've rebased the changes I made to address this back in April to current >> > master. >> >> Please note that I went ahead an

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Wed, Nov 2, 2016 at 1:21 PM, David Rowley wrote: > On 31 October 2016 at 18:37, David Rowley > wrote: > > I've rebased the changes I made to address this back in April to current > master. > > Please note that I went ahead and marked this as "Ready for > committer". It was previously marked a

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 31 October 2016 at 18:37, David Rowley wrote: > I've rebased the changes I made to address this back in April to current > master. Please note that I went ahead and marked this as "Ready for committer". It was previously marked as such in a previous commitfest. The changes made since last ver

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 8 April 2016 at 06:49, Tom Lane wrote: > David Rowley writes: > Just had a thought about this, which should have crystallized a long > time ago perhaps. Where I'd originally imagined you were going with > this idea is to do what the thread title actually says, and check for > joins in which t

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 8 April 2016 at 11:59, Tom Lane wrote: > I did some performance testing on the attached somewhat-cleaned-up patch, > and convinced myself that the planning time penalty is fairly minimal: > on the order of a couple percent in simple one-join queries, and less > than that in very large queries.

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 8 April 2016 at 02:46, Tom Lane wrote: > I'm also a bit suspicious of the fact that some of the plans in > aggregates.out changed from merge to hash joins; with basically > no stats at hand in those tests, that seems dubious. A quick look > at what the patch touched in costsize.c suggests that

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Thu, Apr 7, 2016 at 7:59 PM, Tom Lane wrote: > Anyway, I think it would be reasonable to give this patch a few more > days in view of David's being away through the weekend. But the RMT > has final say on that. The RMT has considered this request (sorry for the delay) and thought it had merit

Re: [HACKERS] Performance improvement for joins where outer side is unique

I wrote: > Alvaro Herrera writes: >> FWIW the feature freeze rules state that it is allowed for a committer >> to request an extension to the feature freeze date for individual >> patches: >> https://www.postgresql.org/message-id/CA%2BTgmoY56w5FOzeEo%2Bi48qehL%2BBsVTwy-Q1M0xjUhUCwgGW7-Q%40mail.gma

Re: [HACKERS] Performance improvement for joins where outer side is unique

Alvaro Herrera writes: > Tom Lane wrote: >> I don't know if you have time to look at this now --- my clock says it's >> already Friday morning in New Zealand. > FWIW the feature freeze rules state that it is allowed for a committer > to request an extension to the feature freeze date for individu

Re: [HACKERS] Performance improvement for joins where outer side is unique

Tom Lane wrote: > Anyway, while refactoring the make_join_rel/add_paths_to_joinrel division > of labor wouldn't be such a big deal in itself, I don't want to commit a > change to JoinType only to undo it later; that would be too much churn. > So I think we need to resolve this question before we c

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > [ unique_joins_2016-04-07.patch ] Just had a thought about this, which should have crystallized a long time ago perhaps. Where I'd originally imagined you were going with this idea is to do what the thread title actually says, and check for joins in which the *outer* side

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > I've attached an updated patch which introduces JOIN_INNER_UNIQUE and > JOIN_LEFT_UNIQUE. So unique inner joins no longer borrow JOIN_SEMI. OK. > In EXPLAIN, I named these new join types "Unique Inner" and "Unique > Left". Hm. I'm back to being unhappy about the amount o

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2016 at 08:01, David Rowley wrote: > On 7 April 2016 at 04:05, Tom Lane wrote: >> Starting to look at this again. I wonder, now that you have the generic >> caching mechanism for remembering whether join inner sides have been >> proven unique, is it still worth having the is_unique_jo

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2016 at 08:01, David Rowley wrote: > On 7 April 2016 at 04:05, Tom Lane wrote: >> Starting to look at this again. I wonder, now that you have the generic >> caching mechanism for remembering whether join inner sides have been >> proven unique, is it still worth having the is_unique_jo

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 7 April 2016 at 04:05, Tom Lane wrote: > David Rowley writes: >> In the last patch I failed to notice that there's an alternative >> expected results file for one of the regression tests. >> The attached patch includes the fix to update that file to match the >> new expected EXPLAIN output. >

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > In the last patch I failed to notice that there's an alternative > expected results file for one of the regression tests. > The attached patch includes the fix to update that file to match the > new expected EXPLAIN output. Starting to look at this again. I wonder, now tha

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 2 April 2016 at 23:26, David Rowley wrote: > I worked on this today to try and get it into shape. In the last patch I failed to notice that there's an alternative expected results file for one of the regression tests. The attached patch includes the fix to update that file to match the new ex

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 2 April 2016 at 05:52, Tom Lane wrote: > > David Rowley writes: > > On 12 March 2016 at 11:43, Tom Lane wrote: > >> It seems like the major intellectual complexity here is to figure out > >> how to detect inner-side-unique at reasonable cost. I see that for > >> LEFT joins you're caching tha

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 12 March 2016 at 11:43, Tom Lane wrote: >> It seems like the major intellectual complexity here is to figure out >> how to detect inner-side-unique at reasonable cost. I see that for >> LEFT joins you're caching that in the SpecialJoinInfos, which is probably >> fine.

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 12 March 2016 at 11:43, Tom Lane wrote: > I wrote: >> I wondered why, instead of inventing an extra semantics-modifying flag, >> we couldn't just change the jointype to *be* JOIN_SEMI when we've >> discovered that the inner side is unique. > > BTW, to clarify: I'm not imagining that we'd make t

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Saturday, March 12, 2016, David G. Johnston wrote: > On Saturday, March 12, 2016, Tom Lane > wrote: > >> "David G. Johnston" writes: >> > Don't the semantics of a SEMI JOIN also state that the output columns >> only >> > come from the outer relation? i.e., the inner relation doesn't >> contr

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Saturday, March 12, 2016, Tom Lane wrote: > "David G. Johnston" > writes: > > Don't the semantics of a SEMI JOIN also state that the output columns > only > > come from the outer relation? i.e., the inner relation doesn't contribute > > either rows or columns to the final result? Or is that s

Re: [HACKERS] Performance improvement for joins where outer side is unique

Robert Haas writes: > The new join pushdown code in postgres_fdw does not grok SEMI and ANTI > joins because there is no straightforward way of reducing those back > to SQL. They can originate in multiple ways and not all of those can > be represented easily. I think it would be nice to do somet

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Fri, Mar 11, 2016 at 4:32 PM, Tom Lane wrote: > So I started re-reading this thread in preparation for looking at the > patch, and this bit in your initial message jumped out at me: > >> In all of our join algorithms in the executor, if the join type is SEMI, >> we skip to the next outer row on

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 12 March 2016 at 11:43, Tom Lane wrote: >>> I wondered why, instead of inventing an extra semantics-modifying flag, >>> we couldn't just change the jointype to *be* JOIN_SEMI when we've >>> discovered that the inner side is unique. > The thing that might matter is that,

Re: [HACKERS] Performance improvement for joins where outer side is unique

"David G. Johnston" writes: > Don't the semantics of a SEMI JOIN also state that the output columns only > come from the outer relation? i.e., the inner relation doesn't contribute > either rows or columns to the final result? Or is that simply > an implementation artifact of the fact that the on

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Saturday, March 12, 2016, David Rowley wrote: > On 12 March 2016 at 11:43, Tom Lane > > wrote: > > I wrote: > >> I wondered why, instead of inventing an extra semantics-modifying flag, > >> we couldn't just change the jointype to *be* JOIN_SEMI when we've > >> discovered that the inner side is

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 12 March 2016 at 11:43, Tom Lane wrote: > I wrote: >> I wondered why, instead of inventing an extra semantics-modifying flag, >> we couldn't just change the jointype to *be* JOIN_SEMI when we've >> discovered that the inner side is unique. > > BTW, to clarify: I'm not imagining that we'd make t

Re: [HACKERS] Performance improvement for joins where outer side is unique

I wrote: > I wondered why, instead of inventing an extra semantics-modifying flag, > we couldn't just change the jointype to *be* JOIN_SEMI when we've > discovered that the inner side is unique. BTW, to clarify: I'm not imagining that we'd make this change in the query jointree, as for example pre

Re: [HACKERS] Performance improvement for joins where outer side is unique

So I started re-reading this thread in preparation for looking at the patch, and this bit in your initial message jumped out at me: > In all of our join algorithms in the executor, if the join type is SEMI, > we skip to the next outer row once we find a matching inner row. This is > because we don

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > [1] http://www.postgresql.org/message-id/8907.1440383...@sss.pgh.pa.us Oh, okay, I had looked at the many changes in the regression outputs and jumped to the conclusion that you were printing the info all the time. Looking closer I see it's only coming out in VERBOSE mode.

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 9 March 2016 at 13:19, Tom Lane wrote: > I do think that the verbosity this adds to the EXPLAIN output is not > desirable at all, at least not in text mode. Another line for every > darn join is a pretty high price. For me it seems like a good idea to give some sort of indication in EXPLAIN a

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > I also notice that some regression tests, which I think some of which > Tom updated in the upper planner changes have now changed back again > due to the slightly reduced costs on hash and nested loop joins where > the inner side is unique. ?? I don't see anything in this p

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 23 January 2016 at 05:36, Tomas Vondra wrote: > Hi, > > On 12/17/2015 02:17 PM, David Rowley wrote: >> >> On 17 December 2015 at 19:11, Simon Riggs > > wrote: >> >> On 17 December 2015 at 00:17, Tomas Vondra >> mailto:tomas.von...@2ndquadrant.com>> >> w

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 5 March 2016 at 10:43, Alvaro Herrera wrote: > I wonder why do we have two identical copies of clause_sides_match_join ... Yeah, I noticed the same a while back, and posted about it. Here was the response: http://www.postgresql.org/message-id/26820.1405522...@sss.pgh.pa.us -- David Rowley

Re: [HACKERS] Performance improvement for joins where outer side is unique

I wonder why do we have two identical copies of clause_sides_match_join ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes t

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 23/01/2016 12:42 am, "David Rowley" wrote: > > On 23 January 2016 at 05:36, Tomas Vondra wrote: > > Otherwise I think the patch is ready for committer - I think this is a > > valuable optimization and I see nothing wrong with the code. > > > > > > Any objections to marking it accordingly? I'v

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 23 January 2016 at 05:36, Tomas Vondra wrote: > OK. I've looked at the patch again today, and it seems broken bv 45be99f8 as > the partial paths were not passing the unique_inner to the create_*_path() > functions. The attached patch should fix that. > Thanks for looking at this again, and tha

Re: [HACKERS] Performance improvement for joins where outer side is unique

Hi, On 12/17/2015 02:17 PM, David Rowley wrote: On 17 December 2015 at 19:11, Simon Riggs mailto:si...@2ndquadrant.com>> wrote: On 17 December 2015 at 00:17, Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: I'd go with match_first_tuple_only. +1 unique_i

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Thu, Dec 17, 2015 at 10:17 PM, David Rowley wrote: > On 17 December 2015 at 19:11, Simon Riggs wrote: >> >> On 17 December 2015 at 00:17, Tomas Vondra >> wrote: >>> >>> I'd go with match_first_tuple_only. >> >> >> +1 >> >> unique_inner is a state that has been detected, match_first_tuple_only

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 17 December 2015 at 19:11, Simon Riggs wrote: > On 17 December 2015 at 00:17, Tomas Vondra > wrote: > >> I'd go with match_first_tuple_only. > > > +1 > > unique_inner is a state that has been detected, match_first_tuple_only is > the action we take as a result. > > Ok great. I've made it so i

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 17 December 2015 at 00:17, Tomas Vondra wrote: > 1) nodeHashjoin.c (and other join nodes) >> >> I've noticed we have this in the ExecHashJoin() method: >> >> /* >>* When the inner side is unique or we're performing a >>* semijoin, we'll consider returning the fir

Re: [HACKERS] Performance improvement for joins where outer side is unique

Hi, On 12/16/2015 11:40 PM, David Rowley wrote: On 17 December 2015 at 05:02, Tomas Vondra mailto:tomas.von...@2ndquadrant.com>> wrote: 0) I know the patch does not tweak costing - any plans in this direction? Would it be possible to simply use the costing used by semijoin?

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 17 December 2015 at 05:02, Tomas Vondra wrote: > 0) I know the patch does not tweak costing - any plans in this > direction? Would it be possible to simply use the costing used by >semijoin? > > Many thanks for looking at this. The patch does tweak the costings so that unique joins are

Re: [HACKERS] Performance improvement for joins where outer side is unique

Hi, On 12/16/2015 01:27 AM, David Rowley wrote: I've attached a rebased patch against current master as there were some conflicts from the recent changes to LATERAL join. Thanks. I've looked at the rebased patch and have a few minor comments. 0) I know the patch does not tweak costing - any

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 25 August 2015 at 17:25, David Rowley wrote: > On 24 August 2015 at 14:29, Tom Lane wrote: > >> David Rowley writes: >> > I have to admit I don't much like it either, originally I had this as an >> > extra property that was only seen in EXPLAIN VERBOSE. >> >> Seems like a reasonable design f

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Wed, Oct 14, 2015 at 1:03 AM, Pavel Stehule wrote: > it is great +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql

Re: [HACKERS] Performance improvement for joins where outer side is unique

2015-10-13 23:28 GMT+02:00 David Rowley : > On 4 September 2015 at 04:50, Robert Haas wrote: > >> >> Also: very nice performance results. >> >> > Thanks. > > On following a thread in [General] [1] it occurred to me that this patch > can give a massive improvement on Merge joins where the mark and

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 4 September 2015 at 04:50, Robert Haas wrote: > > Also: very nice performance results. > > Thanks. On following a thread in [General] [1] it occurred to me that this patch can give a massive improvement on Merge joins where the mark and restore causes an index scan to have to skip over many f

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Tue, Aug 25, 2015 at 1:25 AM, David Rowley wrote: > If that's the case then why do we not enable verbose for all of the non-text > outputs? > It seems strange to start making exceptions on a case-by-case basis. +1. FORMAT and VERBOSE are separate options, and each one should control what the

Re: [HACKERS] Performance improvement for joins where outer side is unique

On Tue, Aug 25, 2015 at 2:25 PM, David Rowley wrote: > On 24 August 2015 at 14:29, Tom Lane wrote: >> >> David Rowley writes: >> > I have to admit I don't much like it either, originally I had this as an >> > extra property that was only seen in EXPLAIN VERBOSE. >> >> Seems like a reasonable des

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 24 August 2015 at 14:29, Tom Lane wrote: > David Rowley writes: > > I have to admit I don't much like it either, originally I had this as an > > extra property that was only seen in EXPLAIN VERBOSE. > > Seems like a reasonable design from here. The attached patch has the format in this way.

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 24 August 2015 at 12:19, Tom Lane wrote: >> I am not exactly convinced that this behavior needs to be visible in >> EXPLAIN output at all, but if it does, it should be a separate field. > I have to admit I don't much like it either, originally I had this as an > extra p

Re: [HACKERS] Performance improvement for joins where outer side is unique

On 24 August 2015 at 12:19, Tom Lane wrote: > David Rowley writes: > > On 24 August 2015 at 07:31, Tomas Vondra > > wrote: > >> 2) in the explain output, there should probably be a space before the > >> '(inner unique)' text, so > >> > >> Hash Join (inner unique) ... > >> > >> instead of > >> >

Re: [HACKERS] Performance improvement for joins where outer side is unique

David Rowley writes: > On 24 August 2015 at 07:31, Tomas Vondra > wrote: >> 2) in the explain output, there should probably be a space before the >> '(inner unique)' text, so >> >> Hash Join (inner unique) ... >> >> instead of >> >> Hash Join(inner unique) >> >> but that's just nitpicking at

  1   2   >