Re: [PERFORM] Horribly slow hash join

2004-05-04 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > Modding by a *non* power of 2 (esp. a prime) mixes the bits quite well, > and is likely faster than any multiple-instruction way to do the same. > > The quoted article seems to be by someone who has spent a lot of time > counting assembly cycles and none at

Re: [PERFORM] Horribly slow hash join

2004-04-20 Thread Jim C. Nasby
Dammit, I somehow deleted a bunch of replies to this. Did a TODO ever come out of this? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Wh

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Greg Stark
Dave Cramer <[EMAIL PROTECTED]> writes: > Here's an interesting link that suggests that hyperthreading would be > much worse. Uh, this is the wrong thread. -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Dave Cramer
Here's an interesting link that suggests that hyperthreading would be much worse. http://groups.google.com/groups?q=hyperthreading+dual+xeon+idle&start=10&hl=en&lr=&ie=UTF-8&c2coff=1&selm=aukkonen-FE5275.21093624062003%40shawnews.gv.shawcable.net&rnum=16 FWIW, I have anecdotal evidence that sugge

Re: [PERFORM] Horribly slow hash join

2004-04-19 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > If the hash tables were made a power of two then it would be possible to mix > > the bits of the 32 bit value and just mask off the unneeded bits. I've found > > one page via google that mentions mixing bits in a has

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > If the hash tables were made a power of two then it would be possible to mix > the bits of the 32 bit value and just mask off the unneeded bits. I've found > one page via google that mentions mixing bits in a hash function, but I would > look for a more seri

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Greg Stark
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > On Sun, 18 Apr 2004, Bruno Wolff III wrote: > > > Another option would be to put the numbers into two int4s. For int4 or > > smaller types one of these would be zero. int8s would be split between > > the two. The hash function would then be defined

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Dennis Bjorklund
On Sun, 18 Apr 2004, Bruno Wolff III wrote: > Another option would be to put the numbers into two int4s. For int4 or > smaller types one of these would be zero. int8s would be split between > the two. The hash function would then be defined on the two int4s. Sure, this is an internal calculation

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Bruno Wolff III
On Sun, Apr 18, 2004 at 18:27:09 +0200, Dennis Bjorklund <[EMAIL PROTECTED]> wrote: > On Sun, 18 Apr 2004, Tom Lane wrote: > > > > What do you mean? int8 is supported on all platformas > > > > No it isn't. > > So on platforms where it isn't you would use int4 as the biggest int then. > I don'

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Dennis Bjorklund
On Sun, 18 Apr 2004, Tom Lane wrote: > > What do you mean? int8 is supported on all platformas > > No it isn't. So on platforms where it isn't you would use int4 as the biggest int then. I don't really see that as a problem. As long as you calculate the hash on the biggest int on that platform

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Dennis Bjorklund
On Sun, 18 Apr 2004, Tom Lane wrote: > That creates portability issues though. We do not depend on there being > a 64-bit-int type for anything except int8 itself, and I don't want to > start doing so. What do you mean? int8 is supported on all platformas and if the hasfunction would convert al

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Tom Lane
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > What do you mean? int8 is supported on all platformas No it isn't. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Tom Lane
Dennis Bjorklund <[EMAIL PROTECTED]> writes: > On Sat, 17 Apr 2004, Tom Lane wrote: >> *some* set of inputs. (Also, I have been harboring some notions of >> supporting cross-type hash joins for integer types, which will not work >> unless small int8 values hash the same as int4 etc.) > The simple

Re: [PERFORM] Horribly slow hash join

2004-04-18 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > Eh? Oh, negative numbers? So low^high^sign. [ thinks about it... ] Yeah, that would work. We can't backpatch it without breaking existing hash indexes on int8, but it'd be reasonable to change for 7.5 (since at the rate things are going, we won't have pg_

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> writes: > >> (Also, I have been harboring some notions of supporting cross-type hash > >> joins for integer types, which will not work unless small int8 values hash > >> the same as int4

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Dennis Bjorklund
On Sat, 17 Apr 2004, Tom Lane wrote: > *some* set of inputs. (Also, I have been harboring some notions of > supporting cross-type hash joins for integer types, which will not work > unless small int8 values hash the same as int4 etc.) The simple solution would be to always extend integers to 64

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> writes: >> (Also, I have been harboring some notions of supporting cross-type hash >> joins for integer types, which will not work unless small int8 values hash >> the same as int4 etc.) > The obvious way to modify the hash func

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > We could change the hash function, perhaps, but then we'd just have > different cases where there's a problem ... hashing will always fail on > *some* set of inputs. Sure, but completely ignoring part of the input seems like an unfortunate choice of hash fu

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Marcos Martínez(R)
gt; To: "Jim C. Nasby" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, April 17, 2004 6:08 PM Subject: Re: [PERFORM] Horribly slow hash join > [ resending because I fat-fingered the cc: to the list ] > > I see the problem: all the entries in your wo

Re: [PERFORM] Horribly slow hash join

2004-04-17 Thread Tom Lane
[ resending because I fat-fingered the cc: to the list ] I see the problem: all the entries in your work_units column have the low 32 bits equal to zero. regression=# select distinct work_units % (2^32)::bigint from Trank_work_overall; ?column? -- 0 (1 row) The hash function fo

Re: [PERFORM] Horribly slow hash join

2004-04-16 Thread Tom Lane
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Combined with the hash aggregate problem I saw (see my other email to > the list), do you think there could be some issue with the performance > of the hash function on FreeBSD 5.2 on AMD64? Yeah, I was wondering about that too. Hard to imagine what th

Re: [PERFORM] Horribly slow hash join

2004-04-16 Thread Jim C. Nasby
Yes, stats are up to date, and the values should be fairly unique. Combined with the hash aggregate problem I saw (see my other email to the list), do you think there could be some issue with the performance of the hash function on FreeBSD 5.2 on AMD64? I'll post the table you requested someplace

Re: [PERFORM] Horribly slow hash join

2004-04-16 Thread Tom Lane
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > Note the time for the hash join step: Have you ANALYZEd these tables lately? It looks to me like it's hashing on some column that has only a small number of distinct values, so that the hash doesn't actually help to avoid comparisons. The planner shou

[PERFORM] Horribly slow hash join

2004-04-16 Thread Jim C. Nasby
Note the time for the hash join step: -- Hash Join (cost=357.62..26677.99 rows=93668 width=62) (actual time=741.159..443381.011 rows=49091 loops=1) Ha