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.