X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1004ED86BA for ; Tue, 30 Aug 2005 20:39:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01256-10 for ; Tue, 30 Aug 2005 23:39:12 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 6F433D8686 for ; Tue, 30 Aug 2005 20:39:10 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id C9561152BB; Tue, 30 Aug 2005 18:39:13 -0500 (CDT) Date: Tue, 30 Aug 2005 18:39:13 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Scott Marlowe , RAJU kumar , pgsql-admin@postgresql.org Subject: Re: Hash index Message-ID: <20050830233913.GE77007@pervasive.com> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> <1125428023.28179.94.camel@state.g2switchworks.com> <24115.1125430346@sss.pgh.pa.us> <20050830201738.GD77007@pervasive.com> <24391.1125433203@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <24391.1125433203@sss.pgh.pa.us> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.01 required=5 tests=[AWL=0.010] X-Spam-Level: X-Archive-Number: 200508/401 X-Sequence-Number: 18720 On Tue, Aug 30, 2005 at 04:20:03PM -0400, Tom Lane wrote: > "Jim C. Nasby" writes: > > So does that mean a hash index could (theoretically) improve the > > performance of a hash join or hash aggregation? > > I don't think so --- the whole point of a hash is to do your matching > in-memory, rather than jumping all over the disk for it ... True, but if you have an index on the value you're hashing you should be able to find out very quickly if it exists or not, no? So if you were doing a hash join between two tables and one of them had a hash index on the appropriate field, couldn't you check the index first to see if there was a match? Granted, you could probably do the same thing with a B-tree index, but this might be faster in some scenarios. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461