Received: from castle-smtp.amgen.com (firewall-user@ns2.amgen.com [138.133.17.7]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA75912 for ; Mon, 3 Apr 2000 15:36:34 -0400 (EDT) (envelope-from mdalphin@amgen.com) Received: by castle-smtp.amgen.com; id MAA24676; Mon, 3 Apr 2000 12:36:32 -0700 (PDT) Received: from mailbag.amgen.com(138.133.10.78) by castle-smtp.amgen.com via smap (V4.2) id xma024602; Mon, 3 Apr 00 12:36:14 -0700 Received: from maat.amgen.com (maat [138.133.145.25]) by mailbag.amgen.com (8.8.5/8.8.5) with ESMTP id MAA04504; Mon, 3 Apr 2000 12:35:55 -0700 (PDT) Received: from amgen.com (mahunui [138.133.147.23]) by maat.amgen.com (8.8.8+Sun/8.8.8) with ESMTP id MAA27296; Mon, 3 Apr 2000 12:35:35 -0700 (PDT) Message-ID: <38E8F2A3.CB62DF32@amgen.com> Date: Mon, 03 Apr 2000 12:36:03 -0700 From: Mark Dalphin Organization: Amgen, Inc. X-Mailer: Mozilla 4.6 [en] (X11; U; IRIX64 6.5 IP30) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgresql.org Subject: Re: Hash Indexes. (Was: planner complaints) References: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl> <3658.000403@webclub.ru> <20160.954772326@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200004/19 Tom Lane wrote: > > 2. I've replace btree indexes on relation > > AND atd.ifs_data_id = def.ifs_data_id; > > with: > > create index itd_hash_data_id on ifs_tree_default USING HASH (ifs_data_id); > > create index iatd_hash_data_id on ifs_account_tree_data USING HASH (ifs_data_id); > > Why would you do that? The hash index method doesn't have any advantage > over btree that I can see, and it's got a lot of disadvantages. Tom, I have heard this stated several times in this list and yet it contradicts what I was taught in my course on databases. It was explained that using a HASH index could be faster than a BTREE index for direct lookup of an item, however, the tradeoff was that you couldn't do "unequal" comparisons (ie COLUMN < SomeValue). The speed gain was because the HASH index could go directly to the page containing the data while the btree index might need to load several pages to get to the final data, especially for large BTREE indexes. Is this simply not true for PostgreSQL, or do you think it isn't true in general (for most implementations of HASH and BTREE)? Mark -- Mark Dalphin email: mdalphin@amgen.com Mail Stop: 29-2-A phone: +1-805-447-4951 (work) One Amgen Center Drive +1-805-375-0680 (home) Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work)