how can I change a btree index into a hash index?
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
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
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