From raju_19db@rediffmail.com Fri Aug 26 10:59:30 2005 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 1FA1DD6EBD for ; Fri, 26 Aug 2005 07:59:30 -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 38837-07 for ; Fri, 26 Aug 2005 10:59:26 +0000 (GMT) Received: from rediffmail.com (unknown [203.199.83.28]) by svr1.postgresql.org (Postfix) with SMTP id A1273D72C1 for ; Fri, 26 Aug 2005 07:59:23 -0300 (ADT) Received: (qmail 23188 invoked by uid 510); 26 Aug 2005 10:59:50 -0000 Date: 26 Aug 2005 10:59:50 -0000 Message-ID: <20050826105950.23187.qmail@webmail18.rediffmail.com> Received: from unknown (202.149.203.111) by rediffmail.com via HTTP; 26 aug 2005 10:59:50 -0000 MIME-Version: 1.0 From: "RAJU kumar" Reply-To: "RAJU kumar" To: pgsql-admin@postgresql.org Subject: Hash index Content-type: multipart/alternative; boundary="Next_1125053990---0-203.199.83.28-23166" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.706 required=5 tests=[DNS_FROM_RFC_ABUSE=0.374, FROM_HAS_ULINE_NUMS=0.361, HTML_IMAGE_ONLY_08=1.97, HTML_MESSAGE=0.001, MSGID_FROM_MTA_HEADER=0] X-Spam-Level: ** X-Archive-Number: 200508/355 X-Sequence-Number: 18674 This is a multipart mime message --Next_1125053990---0-203.199.83.28-23166 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =A0=0A =A0=0Ai want to find out the difference between the btree index and= hash index and how exactly the hash index work.=0A --Next_1125053990---0-203.199.83.28-23166 Content-type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

=0A 
=0A 
=0Ai want to find out the difference between= the btree index and hash index and how exactly the hash index work.
=0A= =0A

=0A

=0A=0A --Next_1125053990---0-203.199.83.28-23166-- From bruno@wolff.to Sat Aug 27 06:22:14 2005 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 61D1AD7E14 for ; Sat, 27 Aug 2005 03:22:14 -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 44819-03 for ; Sat, 27 Aug 2005 06:22:12 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id C3D3AD736E for ; Sat, 27 Aug 2005 03:22:11 -0300 (ADT) Received: (qmail 28499 invoked by uid 500); 27 Aug 2005 06:21:50 -0000 Date: Sat, 27 Aug 2005 01:21:50 -0500 From: Bruno Wolff III To: RAJU kumar Cc: pgsql-admin@postgresql.org Subject: Re: Hash index Message-ID: <20050827062150.GC27248@wolff.to> Mail-Followup-To: Bruno Wolff III , RAJU kumar , pgsql-admin@postgresql.org References: <20050826105950.23187.qmail@webmail18.rediffmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <20050826105950.23187.qmail@webmail18.rediffmail.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/356 X-Sequence-Number: 18675 On Fri, Aug 26, 2005 at 10:59:50 -0000, RAJU kumar wrote: >   >   > i want to find out the difference between the btree index and hash index and how exactly the hash index work. Why? You probably don't want to use hash indexes as btrees are virtually always better in the current implementation. From bruno@wolff.to Sat Aug 27 13:51:25 2005 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 94EE8D7A36 for ; Sat, 27 Aug 2005 10:51:25 -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 34218-09 for ; Sat, 27 Aug 2005 13:51:22 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id 9D33AD6E2C for ; Sat, 27 Aug 2005 10:51:20 -0300 (ADT) Received: (qmail 1951 invoked by uid 500); 27 Aug 2005 13:51:03 -0000 Date: Sat, 27 Aug 2005 08:51:03 -0500 From: Bruno Wolff III To: RAJU kumar Cc: pgsql-admin@postgresql.org Subject: Re: Hash index Message-ID: <20050827135103.GA1839@wolff.to> Mail-Followup-To: Bruno Wolff III , RAJU kumar , pgsql-admin@postgresql.org References: <20050827081857.22843.qmail@webmail46.rediffmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050827081857.22843.qmail@webmail46.rediffmail.com> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/359 X-Sequence-Number: 18678 Please keep replies posted to the list so that more people can add to and learn from the discussion. On Sat, Aug 27, 2005 at 08:18:57 -0000, RAJU kumar wrote: > Sir i am a consultant in Sql * international india, > And the problem with me is i have to handle all the question regarding oracle i now how btree works and also about bitmap but i want to know abt hash index and how and where we should use it. > Hope u will help me . > raju The short answer is that you don't want to use hash indexes in postgres. From jnasby@pervasive.com Sat Aug 27 14:35:31 2005 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 5F0D0D7F16 for ; Sat, 27 Aug 2005 11:35:31 -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 48383-04 for ; Sat, 27 Aug 2005 14:35:22 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id B0596D7F10 for ; Sat, 27 Aug 2005 11:35:18 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id C265715255; Sat, 27 Aug 2005 09:35:23 -0500 (CDT) Date: Sat, 27 Aug 2005 09:35:23 -0500 From: "Jim C. Nasby" To: Bruno Wolff III , RAJU kumar , pgsql-admin@postgresql.org Subject: Re: Hash index Message-ID: <20050827143523.GQ11282@pervasive.com> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> <20050827062150.GC27248@wolff.to> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050827062150.GC27248@wolff.to> 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.011 required=5 tests=[AWL=0.011] X-Spam-Level: X-Archive-Number: 200508/360 X-Sequence-Number: 18679 On Sat, Aug 27, 2005 at 01:21:50AM -0500, Bruno Wolff III wrote: > On Fri, Aug 26, 2005 at 10:59:50 -0000, > RAJU kumar wrote: > > ? > > ? > > i want to find out the difference between the btree index and hash index and how exactly the hash index work. > > Why? You probably don't want to use hash indexes as btrees are virtually > always better in the current implementation. That may be true but it's still good to be able to see what they actually do and how they work. In particular it would be good to know what relation they have to the hash operations (HashAgg, HashJoin, etc). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461 From smarlowe@g2switchworks.com Tue Aug 30 18:53:50 2005 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 ECEA5D866B for ; Tue, 30 Aug 2005 15:53:50 -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 34679-08 for ; Tue, 30 Aug 2005 18:53:45 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id 31E50D84E6 for ; Tue, 30 Aug 2005 15:53:43 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 30 Aug 2005 13:53:43 -0500 Subject: Re: Hash index From: Scott Marlowe To: RAJU kumar Cc: pgsql-admin@postgresql.org In-Reply-To: <20050826105950.23187.qmail@webmail18.rediffmail.com> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1125428023.28179.94.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 30 Aug 2005 13:53:43 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 required=5 tests=[AWL=0.004] X-Spam-Level: X-Archive-Number: 200508/393 X-Sequence-Number: 18712 On Fri, 2005-08-26 at 05:59, RAJU kumar wrote: > > > i want to find out the difference between the btree index and hash > index and how exactly the hash index work. How do hash indexes work in postgresql? Poorly. badumpbump. Thanks, I'll be here all week (bad western humor, sorry.) Seriously, there are issues that pop up here every so often that stump people until we find out that they're using hash indexes and every goes "Ohhhhh! That's why X isn't working for you." Currently, only btree indexes are handled properly in case of a server crash (other indexes may get corrupted silently) and they are generally as fast as or faster than hashes. The basic concept in postgresql is the same as elsewhere, but they've never been fleshed out properly, especially compared to btree, which have had lots of testing and fixing and performance tuning and such. Personally, I think that when one creates a non-btree index, one should get a warning saying that non-btree indexes are not necessarily transactionally safe in the event of a crash. From tgl@sss.pgh.pa.us Tue Aug 30 19:32:35 2005 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 C3558D84E6 for ; Tue, 30 Aug 2005 16:32:35 -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 44660-03 for ; Tue, 30 Aug 2005 19:32:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7DCD4D7DD2 for ; Tue, 30 Aug 2005 16:32:28 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7UJWQJT024116; Tue, 30 Aug 2005 15:32:26 -0400 (EDT) To: Scott Marlowe Cc: RAJU kumar , pgsql-admin@postgresql.org Subject: Re: Hash index In-reply-to: <1125428023.28179.94.camel@state.g2switchworks.com> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> <1125428023.28179.94.camel@state.g2switchworks.com> Comments: In-reply-to Scott Marlowe message dated "Tue, 30 Aug 2005 13:53:43 -0500" Date: Tue, 30 Aug 2005 15:32:26 -0400 Message-ID: <24115.1125430346@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/394 X-Sequence-Number: 18713 Scott Marlowe writes: > Personally, I think that when one creates a non-btree index, one should > get a warning saying that non-btree indexes are not necessarily > transactionally safe in the event of a crash. As of 8.1, GIST indexes are WAL-logged, so that would be inappropriate anyway. What I foresee happening (soon, 8.2 maybe) is that rtree will go away completely; there isn't anything it can do that GIST can't do as well or better, so there's no point in expending maintenance resources on it. We're really only leaving it there for 8.1 because there's so much new, as-yet-poorly-tested code involved in the GIST replacement opclasses. That leaves hash. I'm hoping someone will step up and do WAL logging for hash in the near future. Unlike rtree, I'm not expecting that we might get rid of hash indexes. Even if the performance problems never get fixed, we use hash index opclasses to manage datatype-specific hashing for hash joins, hash aggregation, etc, so if we removed hash indexes we'd need to find some other representation for all that. Anyway, that's my two cents on the development roadmap for indexes. regards, tom lane From jnasby@pervasive.com Tue Aug 30 20:17:44 2005 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 1F4F1D7DD2 for ; Tue, 30 Aug 2005 17:17:44 -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 55826-04 for ; Tue, 30 Aug 2005 20:17:38 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id A1C8BD7A32 for ; Tue, 30 Aug 2005 17:17:36 -0300 (ADT) Received: by flake.decibel.org (Postfix, from userid 1001) id 40477152A6; Tue, 30 Aug 2005 15:17:38 -0500 (CDT) Date: Tue, 30 Aug 2005 15:17:38 -0500 From: "Jim C. Nasby" To: Tom Lane Cc: Scott Marlowe , RAJU kumar , pgsql-admin@postgresql.org Subject: Re: Hash index Message-ID: <20050830201738.GD77007@pervasive.com> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> <1125428023.28179.94.camel@state.g2switchworks.com> <24115.1125430346@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <24115.1125430346@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/397 X-Sequence-Number: 18716 On Tue, Aug 30, 2005 at 03:32:26PM -0400, Tom Lane wrote: > That leaves hash. I'm hoping someone will step up and do WAL logging > for hash in the near future. Unlike rtree, I'm not expecting that we > might get rid of hash indexes. Even if the performance problems never > get fixed, we use hash index opclasses to manage datatype-specific > hashing for hash joins, hash aggregation, etc, so if we removed hash > indexes we'd need to find some other representation for all that. So does that mean a hash index could (theoretically) improve the performance of a hash join or hash aggregation? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com 512-569-9461 From tgl@sss.pgh.pa.us Tue Aug 30 20:20:27 2005 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 D8FBFD86BE for ; Tue, 30 Aug 2005 17:20:27 -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 57513-01 for ; Tue, 30 Aug 2005 20:20:18 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 610CDD864B for ; Tue, 30 Aug 2005 17:20:12 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j7UKK37g024392; Tue, 30 Aug 2005 16:20:03 -0400 (EDT) To: "Jim C. Nasby" Cc: Scott Marlowe , RAJU kumar , pgsql-admin@postgresql.org Subject: Re: Hash index In-reply-to: <20050830201738.GD77007@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> Comments: In-reply-to "Jim C. Nasby" message dated "Tue, 30 Aug 2005 15:17:38 -0500" Date: Tue, 30 Aug 2005 16:20:03 -0400 Message-ID: <24391.1125433203@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200508/398 X-Sequence-Number: 18717 "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 ... regards, tom lane From smarlowe@g2switchworks.com Tue Aug 30 20:54:56 2005 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 42440D8616 for ; Tue, 30 Aug 2005 17:54:56 -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 63967-04 for ; Tue, 30 Aug 2005 20:54:54 +0000 (GMT) Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by svr1.postgresql.org (Postfix) with ESMTP id C559BD8612 for ; Tue, 30 Aug 2005 17:54:51 -0300 (ADT) Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP with MS-WebStorage 6.5.6944 Received: from state.g2switchworks.com by mail.g2switchworks.com; 30 Aug 2005 15:54:52 -0500 Subject: Re: Hash index From: Scott Marlowe To: Tom Lane Cc: RAJU kumar , pgsql-admin@postgresql.org In-Reply-To: <24115.1125430346@sss.pgh.pa.us> References: <20050826105950.23187.qmail@webmail18.rediffmail.com> <1125428023.28179.94.camel@state.g2switchworks.com> <24115.1125430346@sss.pgh.pa.us> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1125435291.28179.117.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 30 Aug 2005 15:54:52 -0500 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 required=5 tests=[AWL=0.004] X-Spam-Level: X-Archive-Number: 200508/399 X-Sequence-Number: 18718 On Tue, 2005-08-30 at 14:32, Tom Lane wrote: > Scott Marlowe writes: > > Personally, I think that when one creates a non-btree index, one should > > get a warning saying that non-btree indexes are not necessarily > > transactionally safe in the event of a crash. > > As of 8.1, GIST indexes are WAL-logged, so that would be inappropriate > anyway. Only in this exact instance. It still might make sense to emit such a warning / notice when using a non Wal logged index of any kind though. Glad that GiST indexes got the WAL logging, btw, my tanks to whoever did the work. From jnasby@pervasive.com Tue Aug 30 23:39:17 2005 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