X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 20B6AD1BCB8 for ; Sat, 17 Apr 2004 17:35:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 27509-01 for ; Sat, 17 Apr 2004 17:35:27 -0300 (ADT) Received: from mx2.mundo-r.com (mx2.mundo-r.com [212.51.32.186]) by svr1.postgresql.org (Postfix) with ESMTP id F2F9ED1BB49 for ; Sat, 17 Apr 2004 17:35:24 -0300 (ADT) Received: from lobeira (cm248132.red.mundo-r.com [213.60.248.132]) by obelix.mundo-r.ggc (iPlanet Messaging Server 5.1 HotFix 1.6 (built Oct 18 2002)) with ESMTP id <0HWC00CG016MG1@obelix.mundo-r.ggc> for pgsql-performance@postgreSQL.org; Sat, 17 Apr 2004 22:35:26 +0200 (MEST) Date: Sat, 17 Apr 2004 22:35:09 +0200 From: =?iso-8859-1?Q?Marcos_Mart=EDnez=28R=29?= Subject: Re: Horribly slow hash join To: Tom Lane , "Jim C. Nasby" Cc: pgsql-performance@postgreSQL.org Message-id: <001001c424bb$7fb83740$0564a8c0@lobeira> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Mailer: Microsoft Outlook Express 6.00.2800.1409 Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7BIT X-Priority: 3 X-MSMail-priority: Normal References: <20040416154502.GF87362@nasby.net> <450.1082133251@sss.pgh.pa.us> <20040416164644.GH87362@nasby.net> <808.1082135098@sss.pgh.pa.us> <20040416172523.GJ87362@nasby.net> <1263.1082138383@sss.pgh.pa.us> <20040416184541.GK87362@nasby.net> <10603.1082218095@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/237 X-Sequence-Number: 6537 I didn't follow the conversation from the begining, bu I imagine that you could improve performance using the value (work_units % (2^32) ) instead of work_units. You could even make an index on this value. Like that, the HASH function will work well. This is not a good solution, but ... For example. create index ind1 on table1 ( work_units % (2^32) ); create index ind1 on table2 ( work_units % (2^32) ); Select * from table1 join table2 on (table1.work_units % (2^32) ) = (table2.work_units % (2^32) ) ----- Original Message ----- From: "Tom Lane" To: "Jim C. Nasby" Cc: 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 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 for int8 only takes the low word into account, so all > of the entries end up on the same hash chain, resulting in worst-case > behavior. This applies to both your hash join and hash aggregate cases. > > 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. (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.) > > I guess the real issue is why are you encoding work_units like that? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org