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 >

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

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

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

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,

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

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

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

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

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. --

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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: > > > >

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

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

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

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 >

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 > > /* > *

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 >>

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

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 >

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

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,

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

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

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

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.

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

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

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

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

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: >>

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

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

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

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

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 >>

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 >>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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. ??

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

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:

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

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

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

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 > wrote: On 17 December 2015 at 00:17, Tomas Vondra > wrote:

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. >>

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

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

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,

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 > wrote: 0) I know the patch does not tweak costing - any plans in this direction? Would it be possible to simply use the

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

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

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

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

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

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

On Tue, Aug 25, 2015 at 2:25 PM, David Rowley david.row...@2ndquadrant.com wrote: On 24 August 2015 at 14:29, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley david.row...@2ndquadrant.com writes: I have to admit I don't much like it either, originally I had this as an extra property that was

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

On 24 August 2015 at 14:29, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley david.row...@2ndquadrant.com 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

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

Hi, I did some initial performance evaluation of this patch today, and I see a clear improvement on larger joins. The scenario I've chosen for the experiments is a simple fact-dimension join, i.e. a small table referenced by a large table. So effectively something like this: CREATE TABLE

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

On 24 August 2015 at 12:19, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley david.row...@2ndquadrant.com writes: On 24 August 2015 at 07:31, Tomas Vondra tomas.von...@2ndquadrant.com wrote: 2) in the explain output, there should probably be a space before the '(inner unique)' text, so

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

On 24 August 2015 at 07:31, Tomas Vondra tomas.von...@2ndquadrant.com wrote: dim 100k rows, fact 1M rows --- master patched --- --- .. med280.994 261.406 (-7%) dim 1M rows, fact 10M rows --

  1   2   >