Re: Hash Indexes. (Was: planner complaints)

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)