how can I change a btree index into a hash index?

[Mark Harrison]

I create a table like so:

create table types (
typeid integer unique not null,
typename varchar(255) unique not null
);

and I get the expected messages:

NOTICE: CREATE TABLE / UNIQUE will create implicit index 'types_typeid_key' for table 'types'
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'types_typename_key' for table 'types

Since these are all unique things, and will only be tested for
equality, I am guessing that making a hash index will be better
than making a btree index.

1. Exactly how do I do this? I'm getting some hints that I should
drop a constraint rather than drop an index, but I'm still
not sure exactly what to type.

2. Is this in general a good idea? I will benchmark both ways
but I'm interested in hearing any discussions regarding
hashed indexing.

Many TIA!
Mark
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Mark Harrison <[e-mail]> writes:

Since these are all unique things, and will only be tested for
equality, I am guessing that making a hash index will be better
than making a btree index.

You are mistaken. If there were any real value in that, we'd offer
an easier way to do it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Stephen Robert Norris]

On Thu, 2003-11-13 at 10:01, Tom Lane wrote:

Mark Harrison <[e-mail]> writes:

Since these are all unique things, and will only be tested for
equality, I am guessing that making a hash index will be better
than making a btree index.

You are mistaken. If there were any real value in that, we'd offer
an easier way to do it.

regards, tom lane

Hash indices also have locking restrictions that make them less useful
in most applications.

Stephen
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)