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 561CBD1C4C3 for ; Sat, 17 Apr 2004 20:04:41 -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 59865-01 for ; Sat, 17 Apr 2004 20:04:40 -0300 (ADT) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id BE560D1BCB8 for ; Sat, 17 Apr 2004 20:04:37 -0300 (ADT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 7139B17C431; Sat, 17 Apr 2004 19:04:40 -0400 (EDT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1BEyrY-0004mf-00; Sat, 17 Apr 2004 19:04:40 -0400 To: Tom Lane Cc: "Jim C. Nasby" , pgsql-performance@postgresql.org Subject: Re: Horribly slow hash join 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> In-Reply-To: <10603.1082218095@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 17 Apr 2004 19:04:39 -0400 Message-ID: <878ygu8auw.fsf@stark.xeocode.com> Lines: 20 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/238 X-Sequence-Number: 6538 Tom Lane 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 function. > (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 function is to xor the high 32 bits with the low 32 bits. That maintains the property you need and at least ensures that all the bits are taken into account. -- greg