Skip site navigation (1) Skip section navigation (2)

Behavior of hash index on a text field

From: "David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Behavior of hash index on a text field
Date: 2007-03-31 17:19:46
Message-ID: eea51fdb0703311019q4dc987b1k477508f13e89753a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hello -

My environment: Postgres 8.2, FreeBSD.

I have a db of about 250G.  In it is a table of Internet domain information
containing, among other things, the domain name.  In that table I have a
serial int index as the primary key to use as a foreign key in a number of
other tables.  The domain name is a text field.  There are about 60M rows in
the table, all with unique names.

Many queries begin with the specification of the domain name and then fan
out through joins to other tables.  Obviously I need to be able to locate a
domain name quickly so I can get the index of the row to use in the joins.
I believe (but don't know) that a hash index would be better for this than a
btree.  I don't know enough to have an opinion wrt GiST or GIN indexes.  Any
advice on this problem would be greatly appreciated.

TIA,
david

Responses

pgsql-novice by date

Next:From: Ron JohnsonDate: 2007-03-31 17:28:18
Subject: Re: Anyone know where I can get an 8.2.3 binary for ubuntu?
Previous:From: Alexander StauboDate: 2007-03-31 16:51:43
Subject: Re: Anyone know where I can get an 8.2.3 binary for ubuntu?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group