From: Mark Dalphin <mdalphin@amgen.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Hash Indexes. (Was: planner complaints)
Date: Mon, 03 Apr 2000 12:36:03 -0700
Message-ID: <38E8F2A3.CB62DF32@amgen.com> (raw)
References: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl>
<3658.000403@webclub.ru>
<20160.954772326@sss.pgh.pa.us>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: mdalphin@amgen.com, tgl@sss.pgh.pa.us
Subject: Re: Hash Indexes. (Was: planner complaints)
In-Reply-To: <38E8F2A3.CB62DF32@amgen.com>
References: <006c01bf9d2b$bc6bd000$020018ac@zke.com.pl> <3658.000403@webclub.ru> <20160.954772326@sss.pgh.pa.us> <38E8F2A3.CB62DF32@amgen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox