By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,776 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,776 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Mark Harrison <mh@pixar.com> 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

Nov 12 '05 #2

P: n/a
On Thu, 2003-11-13 at 10:01, Tom Lane wrote:
Mark Harrison <mh@pixar.com> 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)

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.